[postgis-users] Postgres choses wrong index? (Repost)

[Mark Cave-Ayland]

Apologies for the last message, I think Outlook managed to encode it
somehow! Here is the plain text version!

M.



Hi everyone,
 
We're having problems with Postgres/Postgis making the wrong decision
about which index to use on some of our queries, and was hoping that
someone could suggest a way of fixing it.
 
The basic setup is that we have a number of layers built into a table,
some densely populated and others with only a couple of hundred records
over the entire map are. Each entry within the table has an indexed
geometry and also an indexed layerid. In a particular case, we have a
table called biggeom containing approximately 3 million records split
across two different layerids. The bulk of these records have a layerid
of 1, whereas approximately 150 have a layerid of 2.
 
The problem we have is that when attempting to render layerid=2 when
zoomed out to maximum extents, the map takes minutes to appear on screen
and often results with the browser timing out. After investigating for a
couple of hours, I have found out why this is the case. It appears that
no matter what the current view scale is, the geometric index is always
chosen. Here is the output from the query planner:
 
explain select * from biggeom where layerid=2 and (geom &&
setSRID('BOX3D(-213384 -184341, 763828 686412)'::BOX3D, 27700);
 
-> Index scan on biggeom_geom_index (...etc.....)
 
So it appears that even though there are only 150 rows with layerid=2,
postgres decides to use the spatial index which will contain most (if
not all!) of the 3 million records!
 
In contrast, if I do:
 
explain select * from biggeom where layerid=2
 
-> Index scan on biggeom_layerid_index
 
...which returns all 150 records pretty much instantly. My question is
therefore how can I correctly get postgres to correctly determine the
primary index to use when both a layerid AND a bounding box are given?
Or is this an impossible task in which case I imagine I would need to
split the one library table into multiple tables?
 
 
Many thanks,
 
Mark.

---
Mark Cave-Ayland
Webbased Ltd.

Tel: (01752) 764445
Fax: (01752) 764446