Skip site navigation (1) Skip section navigation (2)

BUG #4810: Complex Contains, Bad Performace.

From: "Paul Mathews" <plm(at)netspace(dot)net(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4810: Complex Contains, Bad Performace.
Date: 2009-05-15 12:27:08
Message-ID: 200905151227.n4FCR8sF061705@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4810
Logged by:          Paul Mathews
Email address:      plm(at)netspace(dot)net(dot)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.

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2009-05-15 12:29:24
Subject: Re: [HACKERS] Re: BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
Previous:From: Heikki LinnakangasDate: 2009-05-15 12:26:27
Subject: Re: [HACKERS] Re: BUG #4796: Recovery followed by backup creates unrecoverable WAL-file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group