[postgis-users] Postgres choses wrong index?

[Mark Cave-Ayland]

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.