Hello all, I saw that STABLE-defined functions
don't get replaced by their output, shoudn't they ?
Following shows that VOLATILE and STABLE functions outputs
won't get into the Filter, while IMMUTABLE will.
Documentation says that STABLE is the modifier to use for functions
which don't change output within a single query, isn't the shown one
a "single" query ?
Thanks in advance
--strk;
-- VOLATILE
update pg_proc set provolatile = 'v' where proname = 'find_srid';
UPDATE 1
explain analyze select * from input.geobit_5 where the_geom && setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d,
find_srid('input','geobit_5','the_geom'));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon geobit_5 (cost=0.00..1708.79 rows=1 width=379) (actual time=586.979..1099.565 rows=255 loops=1) Filter:
(the_geom&&
setsrid('0103000000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry,
find_srid('input'::charactervarying, 'geobit_5'::character varying, 'the_geom'::character varying)))Total runtime:
1099.989ms
(3 rows)
-- STABLE
update pg_proc set provolatile = 's' where proname = 'find_srid';
UPDATE 1
explain analyze select * from input.geobit_5 where the_geom && setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d,
find_srid('input','geobit_5','the_geom'));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index
Scanusing geobit_5_gist on geobit_5 (cost=0.00..6.02 rows=1 width=379) (actual time=2.084..42.157 rows=255 loops=1)
IndexCond: (the_geom &&
setsrid('0103000000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry,
find_srid('input'::charactervarying, 'geobit_5'::character varying, 'the_geom'::character varying)))Total runtime:
42.835ms
(3 rows)
-- IMMUTABLE
update pg_proc set provolatile = 'i' where proname = 'find_srid';
UPDATE 1
explain analyze select * from input.geobit_5 where the_geom && setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d,
find_srid('input','geobit_5','the_geom'));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index
Scanusing geobit_5_gist on geobit_5 (cost=0.00..1095.52 rows=298 width=379) (actual time=0.127..18.010 rows=255
loops=1) Index Cond: (the_geom &&
'0103000020787F0000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry)Total
runtime:18.276 ms
(3 rows)
