[postgis-users] Index scan performance difference between 0.8 and rc1; (fwd)

[strk at refractions.net]

On Mon, Jan 31, 2005 at 03:53:20PM -0800, Ron Mayer wrote:
> 
> On Mon, 31 Jan 2005, Ron Mayer wrote:
> > 
> > Should I expect index scans to be quite a bit slower with RC2 than 0.8, or 
> > am I doing something else wrong?   
> 
> 
> Hmm....  the performance changes a lot if I use find_srid() or "-1" when defining my BOX3D; even though my srid is -1.
> 
> Is this a good hint what my problem may be?
> 
>    Ron

Mmm.. this is interesting.
I've tested a sample query three times, with find_srid defined as
VOLATILE, STABLE, IMMUTABLE. Here are the results:

   VOLATILE: Total runtime: 1096.708 ms
     STABLE: Total runtime: 42.457 ms
  IMMUTABLE: Total runtime: 18.457 ms

Postgis < 1.0.0RC2 had find_srid defined with (iscachable).

Since 8.0.0 PostgreSQL has splitted the iscachable keywork in
STABLE/IMMUTABLE to define in-query cachability and in-session
cachability:

For details:
 http://www.postgresql.org/docs/current/static/sql-createfunction.html

In RC2 I've defined find_srid as STABLE, and defined STABLE to 
be non-cachable for PostGRESQL<800. 

If you want to try it out:

-- IMMUTABLE
update pg_proc set provolatile = 'i' where proname = 'find_srid';
-- STABLE
update pg_proc set provolatile = 's' where proname = 'find_srid';
-- VOLATILE
update pg_proc set provolatile = 'v' where proname = 'find_srid';


I'll make it IMMUTABLE for now, to also keep it cachable in PG<800.


--strk;



> 
> 
>  
> fli=# explain analyze SELECT * from rtgr.lines WHERE the_geom && setSRID('BOX3D(-84.47104 30.3775,-84.16479 30.5875)'::BOX3D, -1);
>                                                                                                              QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using rtgr_lines__gist on lines  (cost=0.00..21096.85 rows=5916 width=71) (actual time=0.118..94.982 rows=15706 loops=1)
>    Index Cond: (the_geom && '01030000000100000005000000E7C6F484251E55C0713D0AD7A3603E40E7C6F484251E55C06666666666963E404D2D5BEB8B0A55C06666666666963E404D2D5BEB8B0A55C0713D0AD7A3603E40E7C6F484251E55C0713D0AD7A3603E40'::geometry)
>  Total runtime: 104.429 ms
> (3 rows)
> 
> fli=# explain analyze select * from rtgr.lines WHERE the_geom && setSRID('BOX3D(-84.47104 30.3775,-84.16479 30.5875)'::BOX3D, find_srid('','rtgr.lines','the_geom') );
>                                                                                                                                                                   QUERY PLAN                                                                      
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using rtgr_lines__gist on lines  (cost=0.00..14.04 rows=3 width=71) (actual time=0.313..808.014 rows=15706 loops=1)
>    Index Cond: (the_geom && setsrid('01030000000100000005000000E7C6F484251E55C0713D0AD7A3603E40E7C6F484251E55C06666666666963E404D2D5BEB8B0A55C06666666666963E404D2D5BEB8B0A55C0713D0AD7A3603E40E7C6F484251E55C0713D0AD7A3603E40'::geometry, find_srid(''::character varying, 'rtgr.lines'::character varying, 'the_geom'::character varying)))
>  Total runtime: 821.575 ms
> (3 rows)
> 
> 
> 
> 
> 
> 
> fli=# select find_srid('','rtgr.lines','the_geom') ;
>  find_srid
> -----------
>         -1
> (1 row)
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users