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 Scan on 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'::character varying, 'geobit_5'::character varying,
'the_geom'::character varying)))
Total runtime: 1099.989 ms
(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 Scan using 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)
Index Cond: (the_geom &&
setsrid('0103000000010000000500000000000000C05C254100000000308C514100000000C05C254100000000CC6C524100000000006A284100000000CC6C524100000000006A284100000000308C514100000000C05C254100000000308C5141'::geometry,
find_srid('input'::character varying, 'geobit_5'::character varying,
'the_geom'::character varying)))
Total runtime: 42.835 ms
(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 Scan using 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)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
