I'm sorry, I sent the wrong EXPLAIN ANALYZE for the first query, this is the correct one:
Seq Scan on test (cost=0.00..1.06 rows=1 width=16) (actual time=0.018..0.022 rows=4 loops=1) Filter: (p <@ '(1,1),(0,0)'::box) Rows Removed by Filter: 1 Planning Time: 0.211 ms Execution Time: 0.051 ms (5 rows) On Wed, 30 Nov 2022 at 17:44, Igor ALBUQUERQUE SILVA < i.albuquerque-si...@kayrros.com> wrote: > Hello everyone, > > I'm having a problem regarding the point type/gist indexes. Here's a > minimal reproduction of it: > > create table test(p point); > insert into test(p) values (point(0, 0)); > insert into test(p) values (point(0, 1)); > insert into test(p) values (point(1, 0)); > insert into test(p) values (point(1, 1)); > insert into test(p) values (point(50, 0)); > analyze test; > explain analyze select * from test where p <@ box '(0,0),(1,1)'; > explain analyze select * from test where p <@ box '(50,0),(51,1)'; > > The two queries get the same cost/row estimation, of 1 row. This is the > EXPLAIN ANALYZE of the first query: > > Seq Scan on test (cost=0.00..1.07 rows=1 width=16) (actual > time=0.022..0.026 rows=4 loops=1) > Filter: ((p[0] >= '0'::double precision) AND (p[0] <= '1'::double > precision)) > Rows Removed by Filter: 1 > Planning Time: 0.115 ms > Execution Time: 0.055 ms > (5 rows) > > What I was expecting is the first query to estimate 4 rows and the second > to estimate 1, like what I get If I try the same thing using integers. > > create table test(x integer, y integer); > insert into test(x, y) values (0, 0); > insert into test(x, y) values (0, 1); > insert into test(x, y) values (1, 0); > insert into test(x, y) values (1, 1); > insert into test(x, y) values (50, 0); > analyze test; > explain analyze select * from test where x between 0 and 1 and y between 0 > and 1; > explain analyze select * from test where x between 50 and 51 and y between > 0 and 1; > > My question is: is this expected behaviour? I actually have a much larger > table with a gist index where I found this occurring, and this causes the > planner to make bad decisions: every query that I do will have the same > estimation, and whenever this estimation is very wrong, the planner does > not take the optimal decision. > > I'm using the official docker image, PostgreSQL 15.1 (Debian > 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) > 10.2.1 20210110, 64-bit, running everything in psql (PostgreSQL) 15.1 > (Ubuntu 15.1-1.pgdg22.04+1). > > Best regards, > Igor >
