Postgis sequential scan and query performance
P. S. Brannan
brannan at BRANNANSOFTWARE.COM
Wed Aug 30 13:00:12 PDT 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