Thread: Postgresql 11.3 doesn't use gist index on polygon column
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)
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)
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?
=?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
Oh, got it, thanks!
чт, 17 нояб. 2022 г. в 18:52, Tom Lane <tgl@sss.pgh.pa.us>:
Вадим Самохин <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