Обсуждение: Changing the function used in an index.
How does the planner know that the function used in an index has changed? If the function's body is changed keeping the argument and return types the planner will not use it anymore:
create table t (i integer);
insert into t (i)
select generate_series(1, 100000);
analyze t;
A simple function to return the opposite integer:
create or replace function f(i integer)
returns integer as $$
select i * -1;
$$ immutable language sql;
And the index on it:
create index t_i_index on t(f(i));
The index is used:
explain select * from t order by f(i);
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using t_i_index on t (cost=0.00..3300.26 rows=100000 width=4)
Now the function is changed to return the integer itself:
create or replace function f(i integer)
returns integer as $$
select i;
$$ immutable language sql;
And the index is not used anymore:
explain select * from t order by f(i);
QUERY PLAN
---------------------------------------------------------------
Sort (cost=11116.32..11366.32 rows=100000 width=4)
Sort Key: i
-> Seq Scan on t (cost=0.00..1443.00 rows=100000 width=4)
If the index is rebuilt
reindex index t_i_index;
It is used again:
explain select * from t order by f(i);
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using t_i_index on t (cost=0.00..4376.26 rows=100000 width=4)
Regards, Clodoaldo
create table t (i integer);
insert into t (i)
select generate_series(1, 100000);
analyze t;
A simple function to return the opposite integer:
create or replace function f(i integer)
returns integer as $$
select i * -1;
$$ immutable language sql;
And the index on it:
create index t_i_index on t(f(i));
The index is used:
explain select * from t order by f(i);
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using t_i_index on t (cost=0.00..3300.26 rows=100000 width=4)
Now the function is changed to return the integer itself:
create or replace function f(i integer)
returns integer as $$
select i;
$$ immutable language sql;
And the index is not used anymore:
explain select * from t order by f(i);
QUERY PLAN
---------------------------------------------------------------
Sort (cost=11116.32..11366.32 rows=100000 width=4)
Sort Key: i
-> Seq Scan on t (cost=0.00..1443.00 rows=100000 width=4)
If the index is rebuilt
reindex index t_i_index;
It is used again:
explain select * from t order by f(i);
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using t_i_index on t (cost=0.00..4376.26 rows=100000 width=4)
Regards, Clodoaldo
Clodoaldo Neto wrote > How does the planner know that the function used in an index has changed? > If the function's body is changed keeping the argument and return types > the > planner will not use it anymore: Not a hacker but the function catalog entry is MVCC just like any other record and so even if you alter the function without changing the signature the system still knows that something has changed. Since the system cannot inspect the function change to know whether the original and new function provide the same output given the same input it can no longer trust the index. For me the "how" doesn't matter and it is working as expected. Do you have some larger intent than just understanding how that you have not made clear? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Changing-the-function-used-in-an-index-tp5763581p5763582.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Clodoaldo Neto <clodoaldo.pinto.neto@gmail.com> writes:
> How does the planner know that the function used in an index has changed?
> If the function's body is changed keeping the argument and return types the
> planner will not use it anymore:
Ordinarily, changing a function definition like that *would* break
things. The only thing that's saving you from yourself in this example
is that the function is inline-able SQL, and so what the planner is
seeing is not "f(i)" vs "f(i)", but "i * -1" vs just "i". Even so,
the index is broken/corrupt, or will be as soon as you make more
insertions into it with the changed function.
regards, tom lane