Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

Richard Guo <guofenglinux(at)gmail(dot)com> 于2025年7月28日周一 10:23写道：

> On Mon, Jul 28, 2025 at 5:16 AM PG Bug reporting form
> <noreply(at)postgresql(dot)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;
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)

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)

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)

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 index
is not correct.

--
Thanks,
Tender Wang