Thread

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

Tender Wang <tndrwang@gmail.com> — 2025-07-31T05:30:45Z

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;
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