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

[Ron Mayer]

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


 
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)