The following bug has been logged online:
Bug reference: 4810
Logged by: Paul Mathews
Email address: plm@netspace.net.au
PostgreSQL version: 8.3.7
Operating system: Linux SuSE 11.0
Description: Complex Contains, Bad Performace.
Details:
Consider a table :
Postcodes
postcode char[4]
boundary polygon
with an GIST index on boundary.
The table contains about 500 postcodes. Each boundary object is very
complicated however. Each one may contain up to 2000 (latitude, longitude)
points.
Despite the existence of the index, postgresql is determined to full table
scan when given.
SELECT
postcode
WHERE
boundary @> point 'x,y';
This is slow. 4m19 for 500 points.
Adding a bounding box to the table:
Postcodes
postcode char[4]
boundary polygon
boxbound box
Allows 500 points to be processed in less than 2 seconds.
SELECT
postcode
WHERE
boxbound @> box( point 'x,y', point 'x,y' ) and
boundary @> point 'x,y';
Issue: For complex polygon contains, users have to write their own bounding
box routines.
Issue: The existence of a GIST index on the boundary polygons is ignored,
despite the horrendous complexity of the polygons.