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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Paul Mathews" <plm(at)netspace(dot)net(dot)au>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4810: Complex Contains, Bad Performace.
Date: 2009-05-15 14:03:41
Message-ID: 2470.1242396221@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Paul Mathews" <plm(at)netspace(dot)net(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Simon Riggs 2009-05-15 14:10:42 Re: [HACKERS] Re: BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
Previous Message Fujii Masao 2009-05-15 13:56:15 Re: [HACKERS] Re: BUG #4796: Recovery followed by backup creates unrecoverable WAL-file