[postgis-users] Compound Index? (Integer + Spatial)

[David Blasby]

Yurgis Baykshtis wrote:

>    SELECT FROM cities WHERE map_id = my_map_id AND location && '(x1,
>y1, x2, y2)'::box
>
This should work fine with a spatial index on location and normal index 
on map_id.

CREATE INDEX myspatial_index ON cities USING GIST (location 
GIST_GEOMETRY_OPS);
CREATE INDEX mynonspatial_index ON cities (map_id);

You probably want to use:

AND location && 'BOX3D(x1 y1,x2 y2)'::geometry 

instead of the 'box' type.

If you are finding its using the spatial index when it would be much more effecient to use the interger integer then you need to enable the histogram-based index-selectivity.  See the messages with subjects like 'new estimation functions in PostGIS' in october of 2002.

dave

>  
>