BUG #4810: Complex Contains, Bad Performace.
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.
"Paul Mathews" <plm@netspace.net.au> writes:
Despite the existence of the index, postgresql is determined to full table
scan when given.
SELECT
postcode
WHERE
boundary @> point 'x,y';
polygon @> point isn't an indexable operator. The indexable operators
for a gist index on polygon are
<<(polygon,polygon)
&<(polygon,polygon)
&&(polygon,polygon)
&>(polygon,polygon)
(polygon,polygon)
~=(polygon,polygon)
@>(polygon,polygon)
<@(polygon,polygon)
&<|(polygon,polygon)
<<|(polygon,polygon)
|>>(polygon,polygon)
|&>(polygon,polygon)
~(polygon,polygon)
@(polygon,polygon)
So it looks like you need to convert the point to a one-point polygon.
regards, tom lane
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid:2470.1242396221@sss.pgh.pa.us" type="cite">
<pre wrap="">"Paul Mathews" <a class="moz-txt-link-rfc2396E" href="mailto:plm@netspace.net.au"><plm@netspace.net.au></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">Despite the existence of the index, postgresql is determined to full table
scan when given.
SELECT
postcode
WHERE
boundary @> point 'x,y';
</pre>
</blockquote>
<pre wrap=""><!---->
polygon @> point isn't an indexable operator. The indexable operators
for a gist index on polygon are
<<(polygon,polygon)
&<(polygon,polygon)
&&(polygon,polygon)
&>(polygon,polygon)
>>(polygon,polygon)
~=(polygon,polygon)
@>(polygon,polygon)
<@(polygon,polygon)
&<|(polygon,polygon)
<<|(polygon,polygon)
|>>(polygon,polygon)
|&>(polygon,polygon)
~(polygon,polygon)
@(polygon,polygon)
So it looks like you need to convert the point to a one-point polygon.
regards, tom lane
</pre>
</blockquote>
WHERE<br>
g.boundary @> polygon(box(w.geocode,w.geocode));<br>
<br>
Is there are more convenient, less ugly, way to convert a point to a
polygon?<br>
<br>
</body>
</html>