[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] Postgres choses wrong index?
From:       "Mark Cave-Ayland" <mark.cave-ayland () webbased ! co ! uk>
Date:       2002-12-23 9:52:20
Message-ID: F12B180178CE344E82DD10E3B030A72805A16C () webbased8 ! webbasedtsp ! tsp ! webbased ! co ! uk
[Download RAW message or body]

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.
 
 


[prev in list] [next in list] [prev in thread] [next in thread]