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

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?