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

[Ron Mayer]

Should I expect index scans to be quite a bit slower with RC2 than 0.8, or 
am I doing something else wrong?   

In the queries below (mapserver querying for all roads from the census 
tiger data around tallahassee florida), using 0.8 it took just 
over 100 ms; while with rc2 it took about a second.



I checked the postgresql.conf files and they were configured the same.
In both cases it seems the buffer cache seems to hold all the
necessary data, as "vmstat" reports 0 for bi and si the whole time.
I even tried "cluster rtgr_lines__gist on lines", with no luck.

Other differences (that I think shouldn't matter) are:
 * The fast one has the entire US tiger data loaded.  
   The slow one, just a few counties in florida, california,
   and texas.
 * The fast one is using postgresql 7.4, the slow one 8.0

I'm happy to investigate further, but can't really think where
to look next.

   Thanks,
   Ron

==================================================
=== using 0.8
==================================================


fl=# explain analyze SELECT cfcc::text,name::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from rtgr_lines2 WHERE the_geom && setSRID('BOX3D(-84.47104 30.3775,-84.16479 30.5875)'::BOX3D, find_srid('','rtgr_lines2','the_geom') );
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using rtgr_lines__gist on rtgr_lines2  (cost=0.00..11977.50 rows=2972 width=318) (actual time=0.112..122.424 rows=16459 loops=1)
   Index Cond: (the_geom && 'SRID=-1;BOX3D(-84.47104 30.3775 0,-84.16479 30.5875 0)'::geometry)
   Filter: (the_geom && 'SRID=-1;BOX3D(-84.47104 30.3775 0,-84.16479 30.5875 0)'::geometry)
 Total runtime: 132.451 ms
(4 rows)


==================================================
=== using rc2
==================================================

fli=# explain analyze SELECT cfcc::text,name::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text 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.07 rows=3 width=67) (actual time=0.380..951.519 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: 965.246 ms
(3 rows)