[postgis-users] Spatial Index questino

[strk at refractions.net]

On Tue, Jul 05, 2005 at 11:23:40AM +0100, Eli Dylan Lorimer wrote:
> Hi PostGIS folks,
> 
> I have a postGIS db that I am using to store  waypoints. I haven't  
> implemented any spatial indexes however I think by doing so I may  
> speed up my queries.
> They types of queries that I am mostly making involve where clauses  
> that specify a spatial bounding box (so where myPoint < maxPoint &&  
> myPoint>minPoint) and a date range (so where timestamp > minTimestamp  
> and timeStamp < maxTimestamp).
> 
> Firstly, would I benefit from a spatial index? And if so, can I make  
> a multi-variate index so that my Point column in the db is indexed as  
> well as my timestamp column?

All depends on how selective is your time selection and how is
your spatial bbox one. Create a spatial index and check it
out with explain analyze select ...

Note that the < and > operators will not use the GiST spatial
index, but just a btree index which would be slow. You're probably
looking for << and >> instead.

--strk;