[postgis-users] Postgresql not using Spatial Index

[dblasby at openplans.org]

I'm trying to get postgresql to use the spatial index, but I'm having an
awful time.

For example, in the included query postgresql wants to use a sequential
scan instead of an index scan.  The table has just under 100,000 rows,
and the estimate (and actual) number of returned rows is 4,400 (5%). 
It seems a bit excessive to do a sequential scan for 5% of the table!

I changed my random_page_cost from 4 to 2 to try to get it to prefer the
index, but its still no go!

This sure seems like a problem in the cost analysis.

dave
ps. the data is all the TIGER roads for NY.
ps. I've "vacuum analyse"-ed the table and the database.
ps. It takes almost 20seconds to do a seq scan on the table (the numbers
below are when the table is already in the disk cache)

-------------------
query plans (a) normal (b) with enable_seqscan =off
-------------------

new_tiger=# vacuum analyse finalroads;
VACUUM

new_tiger=# explain analyse SELECT "oid",
bytea(AsBinary(force_2d("wkb_geometry"), 'XDR')) FROM "finalroads"
WHERE "wkb_
geometry" && GeometryFromText('POLYGON ((-74.16533714779995
40.55016355826745, -74.16533714779995 40.6032587900393, -74.
05901394617682 40.6032587900393, -74.05901394617682 40.55016355826745,
-74.16533714779995 40.55016355826745))', 1);
                                                                        
                                        QUERY P
LAN
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on finalroads  (cost=0.00..4323.96 rows=4394 width=66) (actual
time=50.000..301.000 rows=4449 loops=1)
   Filter: (wkb_geometry &&
'010300002001000000010000000500000057A742E2948A52C0D71A6DC26B46444057A742E2948A52C0251F83953
74D444097356EE2C68352C0251F8395374D444097356EE2C68352C0D71A6DC26B46444057A742E2948A52C0D71A6DC26B464440'::geometry)
 Total runtime: 301.000 ms
(3 rows)


new_tiger=# set enable_seqscan =off;
SET
new_tiger=# explain analyse SELECT "oid",
bytea(AsBinary(force_2d("wkb_geometry"), 'XDR')) FROM "finalroads"
WHERE "wkb_
geometry" && GeometryFromText('POLYGON ((-74.16533714779995
40.55016355826745, -74.16533714779995 40.6032587900393, -74.
05901394617682 40.6032587900393, -74.05901394617682 40.55016355826745,
-74.16533714779995 40.55016355826745))', 1);
                                                                        
                                          QUERY
 PLAN
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
 Index Scan using finalroads_idx on finalroads  (cost=0.00..6508.77
rows=4394 width=66) (actual time=0.000..40.000 rows=
4449 loops=1)
   Index Cond: (wkb_geometry &&
'010300002001000000010000000500000057A742E2948A52C0D71A6DC26B46444057A742E2948A52C0251F8
395374D444097356EE2C68352C0251F8395374D444097356EE2C68352C0D71A6DC26B46444057A742E2948A52C0D71A6DC26B464440'::geometry)
 Total runtime: 60.000 ms
(3 rows)

new_tiger=# select count(*) from finalroads;
 count
-------
 91839
(1 row)

----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/