[postgis-users] Overlap Queries - speed

strk at refractions.net strk at refractions.net
Wed Oct 5 05:25:28 PDT 2005

It seems the bbox cache thing is unclear to many here ..
I'll try to dissipate some of the clouds over it.

Geometry objects can optionally have a precomputed
bounding box cache.

The GiST index uses bbox values as index key.
These are extracted from Geometry values at time of indexing.
So, at time of indexing, an available precomputed bounding box
cache would be used; if the cache is not available, the bounding
box would be computed for the sole purpose of indexing.

Index scans won't therefore be influenced by the presence of a
bbox cache in the geometry values.

BUT: since the GiST operator is LOSSY (can return false positive)
an underlying function is always invoked on the set returned
by the index scan. In this phase the bounding box will again
be required, so an available cache would speed this up.

Finally, all 1.x versions of PostGIS automatically add a bbox
cache to all Geometry objects except POINTS. If you happen
to have a non-point Geometry w/out a bbox cache, you've likely
found a bug.

I hope this clarifies things. Let me know if you need more info.


On Tue, Oct 04, 2005 at 03:59:21PM -0400, rb wrote:
> Brent,
> If I understand correctly, the command AddBBox helps the index, not
> replaces it. Since I had mentioened in my original post that both tables
> had GIST indices, this is why you saw no one make that suggestion to me.
> As it was, the use of the spatial index was very effective once I
> eliminated the intersection command, reducing query time from over an
> hour to a little over 2 minutes.
> Also, with regards to the AddBBOx, this is implicit in all geom columns
> in later versions.
> r.b.
> On Tue, 2005-10-04 at 15:25, Brent Wood wrote:
> > I've just read this thread with interest, and it has made me curious. I may
> > have missed some posts, but have a question.
> > 
> > The docs I read (section 4.5) discussed how to add a spatial index on geometry
> > columns. I had thought this generated a bounding box for use with the &&
> > operator to improve spatial query performance.
> > 
> > The advice given in this thread was to use AddBBox(the_geom), not to create a
> > GIST index.
> > 
> > Is there any real underlying difference in these two approaches, and if so,
> > what is it?
> > 
> > 
> > Thanks,
> > 
> >   Brent Wood
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> -- 
> Non-point Source Data Analyst
> University of Maryland, College Park
> Chesapeake Bay Program Office
> 410 Severn Avenue, Suite 305B
> Annapolis, MD, 21403
> Phone: (410) 267-5779
> rburghol at chesapeakebay.net
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list