BUG #19000: gist index returns inconsistent result with gist_inet_ops

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.

Thanks
Richard

On Mon, Jul 28, 2025 at 9:23 AM Richard Guo <guofenglinux@gmail.com> wrote:

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.

Thanks
Richard

Hi,

I tried to reproduce this issue on PostgreSQL 18beta2 (commit 3151c264) on
Ubuntu 24.04, but I could not observe the incorrect behavior described.

Here’s what I did:

psql (19devel)
Type "help" for help.

postgres=# CREATE EXTENSION btree_gist;

CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;
CREATE EXTENSION
SELECT 1
postgres=# CREATE INDEX ON t USING gist(i);
CREATE INDEX
postgres=# SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
i
----------------
192.168.1.0/25
(1 row)

So the query still returns the expected row after the GiST index is created.
If there are any additional settings or steps required to reproduce,
please let me know.

Best regards,
Stepan Neretin

On Mon, Jul 28, 2025 at 3:39 PM Stepan Neretin <slpmcf@gmail.com> wrote:

I tried to reproduce this issue on PostgreSQL 18beta2 (commit 3151c264) on Ubuntu 24.04, but I could not observe the incorrect behavior described.

So the query still returns the expected row after the GiST index is created.
If there are any additional settings or steps required to reproduce, please let me know.

You'll need to ensure that the plan uses an index scan. Setting
enable_seqscan to off should be sufficient to do so.

Thanks
Richard

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

On 28.07.25 04:23, Richard Guo wrote:

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.

The generated index condition is the same for the gist and the btree
index, namely:

((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))

If I run the query with the lower bound directly, like

SELECT * FROM t WHERE i > '192.168.1.0/24'::inet;

then I also get no result rows for the gist index, but I do get one for
the btree index. (The upper bound works correctly in either case.)

This can be reproduced even in PG17.

My mind is a bit boggled about what the actual meaning of the > operator
should be in this case, but in any case it seems there might be a
pre-existing discrepancy in the btree_gist module.

Peter Eisentraut <peter@eisentraut.org> writes:

On 28.07.25 04:23, Richard Guo wrote:

It seems that with gist_inet_ops the index's opfamily does not support
the '<<' operator correctly.
With inet_ops, the query works correctly.

This can be reproduced even in PG17.
My mind is a bit boggled about what the actual meaning of the > operator
should be in this case, but in any case it seems there might be a
pre-existing discrepancy in the btree_gist module.

We've known about this for, um, decades: btree_gist's support for
inet/cidr is fundamentally broken [1]/messages/by-id/201010112055.o9BKtZf7011251@wwwmaster.postgresql.org[2]/messages/by-id/7891efc1-8378-2cf2-617b-4143848ec895@proxel.se. It's still there
only because nobody's been able to think of a way of removing it
without causing pain for anyone who has indexes like that.
But maybe we should just accept that it's going to cause pain
and remove it.

Actually ... I vaguely recall that we have, or once had, a
kluge in CREATE INDEX that would silently substitute another
opclass name for a user-specified opclass that wasn't there
anymore. That would help with dump/restore scenarios, although
it'd be pretty disastrous in pg_upgrade. But we could add a
preflight check to pg_upgrade that looked for gist_inet_ops
indexes and told users to replace them with network_ops indexes.
So maybe there is a way forward?

regards, tom lane

[1]: /messages/by-id/201010112055.o9BKtZf7011251@wwwmaster.postgresql.org
[2]: /messages/by-id/7891efc1-8378-2cf2-617b-4143848ec895@proxel.se

On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

We've known about this for, um, decades: btree_gist's support for
inet/cidr is fundamentally broken [1][2]. It's still there
only because nobody's been able to think of a way of removing it
without causing pain for anyone who has indexes like that.
But maybe we should just accept that it's going to cause pain
and remove it.

I think correctness should take priority over avoiding pain.

Thanks
Richard

Richard Guo <guofenglinux@gmail.com> writes:

On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

We've known about this for, um, decades: btree_gist's support for
inet/cidr is fundamentally broken [1][2]. It's still there
only because nobody's been able to think of a way of removing it
without causing pain for anyone who has indexes like that.
But maybe we should just accept that it's going to cause pain
and remove it.

I think correctness should take priority over avoiding pain.

Yeah. I spent a little time investigating this today, and hope
to have a patch to propose tomorrow.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> 于2025年8月1日周五 11:33写道：

Richard Guo <guofenglinux@gmail.com> writes:

On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

We've known about this for, um, decades: btree_gist's support for
inet/cidr is fundamentally broken [1][2]. It's still there
only because nobody's been able to think of a way of removing it
without causing pain for anyone who has indexes like that.
But maybe we should just accept that it's going to cause pain
and remove it.

I think correctness should take priority over avoiding pain.

Yeah. I spent a little time investigating this today, and hope
to have a patch to propose tomorrow.

What I debugged yesterday was, in gbt_inet_consistent(),

query = convert_network_to_scalar(dquery, INETOID, &failure);
(gdb) p query
$2 = 11822170368
then in gbt_num_consistent(), called tinfo->f_lt(query, key->upper,
flinfo), which is gbt_inetlt()
and
(gdb) p *(double *) b
$5 = 11822170368

a is equal to b, gbt_inet_consistent() returned false, no index tuple found.

--
Thanks,
Tender Wang

Tender Wang <tndrwang@gmail.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> 于2025年8月1日周五 11:33写道：

Richard Guo <guofenglinux@gmail.com> writes:

On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

We've known about this for, um, decades: btree_gist's support for
inet/cidr is fundamentally broken [1][2].

What I debugged yesterday was, in gbt_inet_consistent(),
query = convert_network_to_scalar(dquery, INETOID, &failure);

Right --- as I mentioned in one of the linked threads, the thing that
is fundamentally wrong here is relying on convert_network_to_scalar
in the first place. That has no charter to deliver exact results,
and it doesn't. In the present example I think the issue is that
it doesn't consider the netmask at all; but there are many other
cases where it's not an exact representation of inet's sort order.
That's not a big problem for its intended purpose for statistical
estimates, but it's completely fatal for index infrastructure.

regards, tom lane

I wrote:

Richard Guo <guofenglinux@gmail.com> writes:

I think correctness should take priority over avoiding pain.

Yeah. I spent a little time investigating this today, and hope
to have a patch to propose tomorrow.

Done at
/messages/by-id/2483812.1754072263@sss.pgh.pa.us

regards, tom lane