pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgresql 11.3 doesn't use gist index on polygon column
3+ messages / 2 participants
[nested] [flat]

* Postgresql 11.3 doesn't use gist index on polygon column
@ 2022-11-17 15:45  Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Вадим Самохин @ 2022-11-17 15:45 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi there,
1. I have a table that looks like this:
create table zonez (p polygon);
2. I have an index that looks like this:
create index zonez__p2 on zonez using gist(p poly_ops);
3. I inserted several records in that table, it looks like:
postgres=# select * from zonez ;
                     p
-------------------------------------------
 ((1,1),(1,5),(5,5),(5,1))
 ((1,2),(1,5),(5,5),(5,1))
 ((1,3),(1,5),(5,5),(5,1))
 ((1,4),(1,5),(5,5),(5,1))
 ((1,6),(1,5),(5,5),(5,1))
 ((1,7),(1,5),(5,5),(5,1))
 ((1,7),(1,5),(5,5),(500000,1000))
 ((1,7),(1,5),(5,5),(52.654987,37.123789))
(8 rows)
4. I've "turned off" seqscan: set enable_seqscan = off;
5. I've issued vacuum analyze
6. But postgresql still doesn't want to use my index:
postgres=# explain analyze select * from zonez where '(2,2)'::point <@ p;
                                                     QUERY PLAN

---------------------------------------------------------------------------------------------------------------------
 Seq Scan on zonez  (cost=10000000000.00..10000000001.10 rows=1 width=101)
(actual time=0.013..0.018 rows=2 loops=1)
   Filter: ('(2,2)'::point <@ p)
   Rows Removed by Filter: 6
 Planning Time: 0.069 ms
 Execution Time: 0.036 ms
(5 rows)

How come? Is it an index that should look different, or is it really more
expensive than 10000000001.10?

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Postgresql 11.3 doesn't use gist index on polygon column
@ 2022-11-17 15:52  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2022-11-17 15:52 UTC (permalink / raw)
  To: Вадим Самохин <samokhinvadim@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

=?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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Postgresql 11.3 doesn't use gist index on polygon column
@ 2022-11-17 17:42  Вадим Самохин <samokhinvadim@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Вадим Самохин @ 2022-11-17 17:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

Oh, got it, thanks!

чт, 17 нояб. 2022 г. в 18:52, Tom Lane <tgl@sss.pgh.pa.us>:

> =?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
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2022-11-17 17:42 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-17 15:45 Postgresql 11.3 doesn't use gist index on polygon column Вадим Самохин <samokhinvadim@gmail.com>
2022-11-17 15:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-17 17:42   ` Вадим Самохин <samokhinvadim@gmail.com>

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