Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #4810: Complex Contains, Bad Performace.

BUG #4810: Complex Contains, Bad Performace.

From
"Paul Mathews"
Date:
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.

Re: BUG #4810: Complex Contains, Bad Performace.

From
Tom Lane
Date:
"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

Re: BUG #4810: Complex Contains, Bad Performace.

From
Paul Matthews
Date:
Tom Lane wrote:

  "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



WHERE
   g.boundary @> polygon(box(w.geocode,w.geocode));

Is there are more convenient, less ugly, way to convert a point to a
polygon?