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

[Yurgis Baykshtis]

> 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);


I actually tried that at the very beginning.
The query is really executed / explained as:

Index scan using <myspatial_index> and FILTER on map_id

So, it does not use <mynonspatial_index> at all.

Fortunaltely, I just found the solution.

GIST index (with btree_gist and rtree_gist contrib libs compiled)
supports both 1-D and 2-D types. 

So, I can build a compound index that I need. 
Although, this seems to be a solution not related to the PostGIS.






-----Original Message-----
From: David Blasby [mailto:dblasby at refractions.net] 
Sent: Tuesday, May 13, 2003 11:37 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Compound Index? (Integer + Spatial)

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

>  
>


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users