Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops
| От | Tender Wang |
|---|---|
| Тема | Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops |
| Дата | |
| Msg-id | CAHewXN=ykSm0RbVHQ46zCnOriejgjEKOR5DuPaX2Sry0Lx9NMA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops (Richard Guo <guofenglinux@gmail.com>) |
| Список | pgsql-bugs |
Richard Guo <guofenglinux@gmail.com> 于2025年7月28日周一 10:23写道：
On Mon, Jul 28, 2025 at 5:16 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> CREATE EXTENSION btree_gist;
>
> CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;
>
> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
> i
> ----------------
> 192.168.1.0/25
>
> CREATE INDEX ON t USING gist(i);
>
> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
> i
> ---
> (0 rows)
It seems that with gist_inet_ops the index's opfamily does not support
the '<<' operator correctly.
With inet_ops, the query works correctly.
CC'ing Peter to have a look.
Before be1cc9aaf, because :
if (opfamily != NETWORK_BTREE_FAM_OID)
return NIL;
return NIL;
So the planner creates seqscan.
After be1cc9aaf, above if block was removed, so the planner creates an index scan, as below:
postgres=# explain SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using t_i_idx on t (cost=0.12..8.15 rows=1 width=32)
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(3 rows)
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using t_i_idx on t (cost=0.12..8.15 rows=1 width=32)
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(3 rows)
However, the gistgettuple() function returned NULL, so the above query has no output.
I created another table t2 and used btree index, its plan was same with t, as below:
postgres=# explain SELECT * FROM t2 WHERE i << '192.168.1.0/24'::cidr;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using t2_i_idx on t2 (cost=0.12..8.15 rows=1 width=32)
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(3 rows)
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using t2_i_idx on t2 (cost=0.12..8.15 rows=1 width=32)
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(3 rows)
I hacked match_network_sub (), changing is_eq to true, so the plan of t is as below:
postgres=# explain SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using t_i_idx on t (cost=0.12..8.15 rows=1 width=32)
Index Cond: ((i >= '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(3 rows)
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using t_i_idx on t (cost=0.12..8.15 rows=1 width=32)
Index Cond: ((i >= '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(3 rows)
The above plan will return a tuple.
It seems that gist_inet_ops the index's opfamily does not support
the '<<' operator correctly, as Richard said. Or the Index Cond for the gist indexis not correct.
--
Thanks,
Tender Wang
В списке pgsql-bugs по дате отправления: