pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Вадим Самохин <samokhinvadim@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Postgresql 11.3 doesn't use gist index on polygon column
Date: Thu, 17 Nov 2022 10:52:56 -0500
Message-ID: <1833085.1668700376@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGVmuwoyF7WeWSwodRFfegJ=mDrd238XcJ4xnxUcdVg_EtihNA@mail.gmail.com>
References: <CAGVmuwoyF7WeWSwodRFfegJ=mDrd238XcJ4xnxUcdVg_EtihNA@mail.gmail.com>

=?UTF-8?B?0JLQsNC00LjQvCDQodCw0LzQvtGF0LjQvQ==?= <samokhinvadim@gmail.com> writes:
> 6. But postgresql still doesn't want to use my index:
> postgres=# explain analyze select * from zonez where '(2,2)'::point <@ p;

It can't, because "polygon @> point" isn't one of the operators supported
by that opclass.  You could use a polygon comparison:

=# \dAo gist poly_ops
               List of operators of operator families
  AM  | Operator family |       Operator       | Strategy | Purpose  
------+-----------------+----------------------+----------+----------
 gist | poly_ops        | <<(polygon,polygon)  |        1 | search
 gist | poly_ops        | &<(polygon,polygon)  |        2 | search
 gist | poly_ops        | &&(polygon,polygon)  |        3 | search
 gist | poly_ops        | &>(polygon,polygon)  |        4 | search
 gist | poly_ops        | >>(polygon,polygon)  |        5 | search
 gist | poly_ops        | ~=(polygon,polygon)  |        6 | search
 gist | poly_ops        | @>(polygon,polygon)  |        7 | search
 gist | poly_ops        | <@(polygon,polygon)  |        8 | search
 gist | poly_ops        | &<|(polygon,polygon) |        9 | search
 gist | poly_ops        | <<|(polygon,polygon) |       10 | search
 gist | poly_ops        | |>>(polygon,polygon) |       11 | search
 gist | poly_ops        | |&>(polygon,polygon) |       12 | search
 gist | poly_ops        | <->(polygon,point)   |       15 | ordering
(13 rows)

			regards, tom lane





view thread (3+ messages)  latest in thread

Message-ID: <1833085.1668700376@sss.pgh.pa.us>
Permalink:  ../1833085.1668700376@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1833085.1668700376@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, samokhinvadim@gmail.com
  Subject: Re: Postgresql 11.3 doesn't use gist index on polygon column
  In-Reply-To: <1833085.1668700376@sss.pgh.pa.us>
  References: <CAGVmuwoyF7WeWSwodRFfegJ=mDrd238XcJ4xnxUcdVg_EtihNA@mail.gmail.com> <1833085.1668700376@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox