Postgis sequential scan and query performance

P. S. Brannan brannan at BRANNANSOFTWARE.COM
Wed Aug 30 16:00:12 EDT 2006


I just imported a bunch of tiger data into postgres. I am using 
mapserver as the frontend. Everything is working fine except for the 
performance. The query performance is terrible and I can't seem to get 
postgresql to use the index i created. Here's what's going on:

Index Creation:
create index idx_completechain_wkb_geometry on completechain using 
gist(wkb_geometry gist_geometry_ops);
vacuum analyze;

Vacuum Analyze:
explain analyze SELECT 
cfcc::text,fename::text,asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),ogc_fid::text 
from completechain
WHERE wkb_geometry && setSRID('BOX3D(-90.6004589271402 
38.4732965951534,-89.9998214028184 
38.8267482378407)'::BOX3D,find_srid('','completechain','wkb_geometry') );
  QUERY 
PLAN                                                                                                                 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on completechain  (cost=0.00..275502.54 rows=90839 width=186) 
(actual time=10718.879..55686.238 rows=103159 loops=1)
   Filter: (wkb_geometry && 
'0103000020FF7F0000010000000500000023AA47EB6DA656C0FABE9AFB943C434023AA47EB6DA656C095C6E1E2D269434089BCE812FD7F56C095C6E1E2D269434089BCE812FD7F56C0FABE9AFB943C434023AA47EB6DA656C0FABE9AFB943C4340'::geometry)
 Total runtime: 55973.870 ms
(3 rows)

Can anyone tell me what I need to do to get postgres to use the index? 
I'm sure that I'm missing something obvious.

Thanks,

Patrick Brannan



More information about the mapserver-users mailing list