postgresql_predicate_using_immutable_functions

gistfile1.txt

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# drop table t cascade;
DROP TABLE
test=#
test=# create table t(id int);
CREATE TABLE
test=# insert into t select x from generate_series(1, 100000) x;
INSERT 0 100000
test=# create or replace function txt_to_int(text) returns int as $$
test$# begin
test$# return $1::int;
test$# end; $$ language plpgsql volatile;
CREATE FUNCTION
test=# create index on t using btree(id);
CREATE INDEX
test=# analyze t;
ANALYZE
test=# explain (analyze,verbose) select * from t where id = public.txt_to_int('01');
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on public.t  (cost=0.00..26693.00 rows=1 width=4) (actual time=0.091..33.259 rows=1 loops=1)
   Output: id
   Filter: (t.id = txt_to_int('01'::text))
   Rows Removed by Filter: 99999
 Query Identifier: -4922229916398990769
 Planning Time: 0.086 ms
 Execution Time: 33.292 ms
(7 rows)

test=# create or replace function txt_to_int(text) returns int as $$
test$# begin
test$# return $1::int;
test$# end; $$ language plpgsql immutable;
CREATE FUNCTION
test=# explain (analyze,verbose) select * from t where id = public.txt_to_int('01');
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_id_idx on public.t  (cost=0.29..4.31 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=1)
   Output: id
   Index Cond: (t.id = 1)
   Heap Fetches: 0
 Query Identifier: -4922229916398990769
 Planning Time: 0.100 ms
 Execution Time: 0.032 ms
(7 rows)

test=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop schema demo cascade;
ERROR:  schema "demo" does not exist
postgres=# create schema demo;
CREATE SCHEMA
postgres=# IMPORT FOREIGN SCHEMA public from server demo into demo;
IMPORT FOREIGN SCHEMA
postgres=# create or replace function txt_to_int(text) returns int as $$
begin
return $1::int;
end; $$ language plpgsql immutable;
CREATE FUNCTION
postgres=# explain (analyze,verbose) select * from demo.t where id = public.txt_to_int('01');
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Foreign Scan on demo.t  (cost=100.29..104.33 rows=1 width=4) (actual time=0.174..0.174 rows=1 loops=1)
   Output: id
   Remote SQL: SELECT id FROM public.t WHERE ((id = 1))
 Query Identifier: 3921959264602802240
 Planning Time: 0.784 ms
 Execution Time: 0.223 ms
(6 rows)

postgres=# analyze demo.t;
ANALYZE
postgres=# create or replace function txt_to_int(text) returns int as $$
begin
return $1::int;
end; $$ language plpgsql volatile;
CREATE FUNCTION
postgres=# explain (analyze,verbose) select * from demo.t where id = public.txt_to_int('01');
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Foreign Scan on demo.t  (cost=100.00..28793.00 rows=1 width=4) (actual time=0.361..48.367 rows=1 loops=1)
   Output: id
   Filter: (t.id = txt_to_int('01'::text))
   Rows Removed by Filter: 99999
   Remote SQL: SELECT id FROM public.t
 Query Identifier: 3921959264602802240
 Planning Time: 0.174 ms
 Execution Time: 48.392 ms
(8 rows)