agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: kehan5800@gmail.com
Subject: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
Date: Fri, 18 Sep 2026 23:36:04 +0000
Message-ID: <19700-ceb6636b38e26eca@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19700
Logged by: Ke Han
Email address: kehan5800@gmail.com
PostgreSQL version: 18.6
Operating system: ubuntu
Description:
An SP-GiST index on an inet column that holds both IPv4 and IPv6 values
can make IPv6 rows unreachable through the index. The rows are in the
heap and their entries are in the index, but the scan does not descend
to them. No error or warning is produced; the query simply returns
fewer rows than it should. UPDATE and DELETE are affected the same way.
Everything below is self-contained: each block can be pasted into psql
against a freshly created database on a stock server.
Steps to reproduce
------------------
CREATE TABLE m(v inet);
INSERT INTO m SELECT '10.0.0.1/32'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '0.0.0.0/0'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '::1'::inet
FROM generate_series(1,100);
CREATE INDEX ON m USING spgist (v);
SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM m WHERE v = '::1';
SELECT count(*) AS with_index FROM m WHERE v = '::1';
SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
SELECT count(*) AS without_index FROM m WHERE v = '::1';
The enable_* settings are only there to force the two plans so they can
be compared. 300 rows is too small for the planner to choose the index
on its own; a case where it does, with nothing set at all, is further
down.
Output I got
------------
QUERY PLAN
---------------------------------------------
Aggregate
-> Bitmap Heap Scan on m
Recheck Cond: (v = '::1'::inet)
-> Bitmap Index Scan on m_v_idx
Index Cond: (v = '::1'::inet)
with_index
------------
42
without_index
---------------
100
Output I expected
-----------------
Both counts should be 100. The table contains 100 rows with v = '::1',
the predicate is a plain equality on the indexed column, and an index
scan and a sequential scan must agree.
It also happens with nothing set at all
---------------------------------------
With a larger and more realistic population -- the same two IPv4 values,
then 200000 distinct IPv6 addresses -- the planner chooses the index by
itself and no settings are involved:
CREATE TABLE r(id int, v inet);
INSERT INTO r SELECT g, '10.0.0.1/32'::inet
FROM generate_series(1,100) g;
INSERT INTO r SELECT g, '0.0.0.0/0'::inet
FROM generate_series(1,100) g;
INSERT INTO r SELECT g, ('2001:db8::' || to_hex((g>>16)&65535) ||
':' || to_hex(g&65535))::inet
FROM generate_series(1,200000) g;
CREATE INDEX rix ON r USING spgist (v);
ANALYZE r;
RESET ALL;
EXPLAIN (COSTS OFF) SELECT count(*) FROM r WHERE v = '2001:db8::1';
SELECT count(*) FROM r WHERE v = '2001:db8::1';
SELECT EXISTS(SELECT 1 FROM r WHERE v = '2001:db8::1');
UPDATE r SET id = -1 WHERE v = '2001:db8::1';
gives
Aggregate
-> Index Only Scan using rix on r
Index Cond: (v = '2001:db8::1'::inet)
count
-------
0 -- expected 1
exists
--------
f -- expected t
UPDATE 0 -- expected UPDATE 1
and the same three with the index disabled give 1, t and UPDATE 1:
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
SELECT count(*) FROM r WHERE v = '2001:db8::1';
SELECT EXISTS(SELECT 1 FROM r WHERE v = '2001:db8::1');
Exactly 58 of the 200000 IPv6 rows are affected, and they are the first
58 inserted -- 2001:db8::1 through 2001:db8::3a. An address outside
that set, for example 2001:db8::100, is returned correctly through the
same index. The count of lost rows does not scale with the table: it is
58 with 100 IPv6 rows and 58 with 200000. To list them:
RESET ALL;
CREATE TEMP TABLE viaidx(v inet);
CREATE TEMP TABLE alltrue(v inet);
SET enable_seqscan = off;
INSERT INTO viaidx SELECT v FROM r WHERE v << '2001:db8::/32';
RESET ALL;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
INSERT INTO alltrue SELECT v FROM r WHERE v << '2001:db8::/32';
RESET ALL;
SELECT (SELECT count(*) FROM viaidx) AS via_index,
(SELECT count(*) FROM alltrue) AS truth;
SELECT min(v), max(v), count(*)
FROM (SELECT v FROM alltrue EXCEPT ALL SELECT v FROM viaidx) x;
via_index | truth
-----------+--------
199942 | 200000
min | max | count
-------------+--------------+-------
2001:db8::1 | 2001:db8::3a | 58
DELETE leaves rows that match its own WHERE clause
--------------------------------------------------
On the 300-row table from the first section, with the index in use:
SET enable_seqscan = off;
DELETE FROM m WHERE v = '::1';
RESET ALL;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
SELECT count(*) FROM m WHERE v = '::1';
gives
DELETE 42
count
-------
58
58 rows matching the DELETE's own predicate survive it.
Which operators are affected
----------------------------
The DELETE above emptied part of m, so rebuild it first:
DROP TABLE IF EXISTS m CASCADE;
CREATE TABLE m(v inet);
INSERT INTO m SELECT '10.0.0.1/32'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '0.0.0.0/0'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '::1'::inet
FROM generate_series(1,100);
CREATE INDEX ON m USING spgist (v);
then:
CREATE OR REPLACE FUNCTION bothways(pred text)
RETURNS TABLE(predicate text, seqscan bigint, spgist bigint)
LANGUAGE plpgsql AS $$
DECLARE s bigint; i bigint;
BEGIN
SET LOCAL enable_seqscan = on;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
EXECUTE 'SELECT count(*) FROM m WHERE ' || pred INTO s;
SET LOCAL enable_seqscan = off;
SET LOCAL enable_indexscan = on;
SET LOCAL enable_bitmapscan = on;
SET LOCAL enable_indexonlyscan = on;
EXECUTE 'SELECT count(*) FROM m WHERE ' || pred INTO i;
RETURN QUERY SELECT pred, s, i;
END $$;
SELECT b.predicate, b.seqscan, b.spgist
FROM unnest(ARRAY[
'v = ''::1''', 'v >>= ''::1''', 'v <<= ''::/0''',
'v << ''::/0''', 'v && ''::/0''',
'v = ''10.0.0.1''', 'v <<= ''0.0.0.0/0''']) p,
LATERAL bothways(p) b;
gives
predicate | seqscan | spgist
--------------------+---------+--------
v = '::1' | 100 | 42
v >>= '::1' | 100 | 42
v <<= '::/0' | 100 | 42
v << '::/0' | 100 | 42
v && '::/0' | 100 | 42
v = '10.0.0.1' | 100 | 100
v <<= '0.0.0.0/0' | 200 | 200
IPv4 predicates against the same index are correct. Only the IPv6 side
is lost.
It is a window rather than a floor, and it depends on the data
--------------------------------------------------------------
CREATE OR REPLACE FUNCTION probe(n int,
v4a text DEFAULT '10.0.0.1/32',
v4b text DEFAULT '0.0.0.0/0',
v6first bool DEFAULT false,
am text DEFAULT 'spgist')
RETURNS TABLE(copies int, seqscan bigint, indexed bigint,
idxbytes bigint)
LANGUAGE plpgsql AS $$
DECLARE s bigint; i bigint;
BEGIN
DROP TABLE IF EXISTS t CASCADE;
CREATE TABLE t(v inet);
IF v6first THEN
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', '::1', n);
END IF;
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', v4a, n);
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', v4b, n);
IF NOT v6first THEN
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', '::1', n);
END IF;
EXECUTE format('CREATE INDEX tix ON t USING %s (v)', am);
SET LOCAL enable_seqscan = on;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
SELECT count(*) INTO s FROM t WHERE v = '::1';
SET LOCAL enable_seqscan = off;
SET LOCAL enable_indexscan = on;
SET LOCAL enable_bitmapscan = on;
SET LOCAL enable_indexonlyscan = on;
SELECT count(*) INTO i FROM t WHERE v = '::1';
RETURN QUERY SELECT n, s, i, pg_relation_size('tix');
END $$;
SELECT p.copies, p.seqscan, p.indexed, p.idxbytes
FROM unnest(ARRAY[81,82,90,144,145,200]) c,
LATERAL probe(c) p;
gives
copies | seqscan | indexed | idxbytes
--------+---------+---------+----------
81 | 81 | 81 | 24576
82 | 82 | 1 | 57344
90 | 90 | 20 | 57344
144 | 144 | 142 | 57344
145 | 145 | 145 | 57344
200 | 200 | 200 | 65536
Wrong for 82 to 144 copies and correct on both sides of that range. The
lower edge coincides with the first leaf page split (3 pages to 7).
Two further conditions:
- Two IPv4 values with no common leading bit are needed. A 0.0.0.0/0
entry is not required.
SELECT v4a, v4b, seqscan, indexed FROM (VALUES
('10.0.0.1','200.0.0.1'), ('1.2.3.4','129.2.3.4'),
('10.0.0.1','10.0.0.2'), ('192.168.0.1','192.168.0.2')
) p(v4a,v4b), LATERAL probe(100, p.v4a, p.v4b);
v4a | v4b | seqscan | indexed
------------+--------------+---------+---------
10.0.0.1 | 200.0.0.1 | 100 | 42
1.2.3.4 | 129.2.3.4 | 100 | 42
10.0.0.1 | 10.0.0.2 | 100 | 100
192.168.0.1| 192.168.0.2 | 100 | 100
- Insertion order matters. If the IPv6 rows go in first the answer is
correct.
SELECT f AS v6_first, seqscan, indexed
FROM (VALUES (true),(false)) o(f),
LATERAL probe(100, '10.0.0.1/32', '0.0.0.0/0', o.f);
v6_first | seqscan | indexed
----------+---------+---------
t | 100 | 100
f | 100 | 42
I mention the window because it makes the symptom confusing in the
field: a table can be correct, become wrong as it grows, and become
correct again, with nothing about the workload changing. A larger test
table is not evidence of absence -- a 50000/50000/100 table is clean.
Other access methods on the same data
-------------------------------------
SELECT a AS access_method, seqscan, indexed
FROM (VALUES ('btree'),('hash'),('brin'),('spgist')) m(a),
LATERAL probe(100, '10.0.0.1/32', '0.0.0.0/0', false, m.a);
access_method | seqscan | indexed
---------------+---------+---------
btree | 100 | 100
hash | 100 | 100
brin | 100 | 100
spgist | 100 | 42
Only SP-GiST is wrong.
Configuration
-------------
Stock. No configuration file changes, no command line options, no
environment variables set. The only settings touched are the enable_*
toggles shown above, used to force the two plans for comparison; the
200000-row case runs after RESET ALL with nothing set.
Nothing was done differently from the standard installation
instructions. Built with:
./configure --prefix=... --enable-debug --enable-cassert
make && make install
No assertion is tripped -- the wrong answer is returned quietly on an
assert-enabled build.
Version
-------
SELECT version();
PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
That is git master at commit
7b879c485243e61a0d4cb169717a8e96e9e875c2 (2026-09-19).
Also reproduced on 18.6, built from source, on two independently built
servers.
Platform
--------
Ubuntu 22.04.2 LTS, x86-64, Linux 5.15.0-187-generic, glibc 2.35,
gcc 11.4.0.
Analysis
--------
The following is my reading of the source rather than an observation,
and the facts above do not depend on it.
inet_spg_picksplit() in src/backend/utils/adt/network_spgist.c decides
whether a group of entries spans both address families in the same loop
that computes their common prefix:
/* Examine remaining items to discover minimum common prefix
length */
for (i = 1; i < in->nTuples; i++)
{
tmp = DatumGetInetPP(in->datums[i]);
if (ip_family(tmp) != ip_family(prefix))
{
differentFamilies = true;
break;
}
if (ip_bits(tmp) < commonbits)
commonbits = ip_bits(tmp);
commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp),
commonbits);
if (commonbits == 0)
break;
}
The commonbits == 0 break is correct for the prefix computation -- there
is nothing further to learn about the prefix. But the same loop is what
answers the separate question "is there an entry of the other family in
this group?", and that question is not yet answered when the break
fires. Entries after that point are never examined. If one of them is
IPv6, differentFamilies stays false and the else branch builds a
four-node inner tuple with an IPv4 CIDR prefix, mapping the IPv6 entries
into it through inet_spg_node_number().
That breaks the invariant stated at the top of the same file:
* We split inet index entries first by address family (IPv4 or
* IPv6).
The scan side then does what that invariant licenses. In
inet_spg_consistent_bitmap(), the static helper that both
inet_spg_inner_consistent() (via the prefix, line 293) and
inet_spg_leaf_consistent() (line 338) call:
if (ip_family(argument) != ip_family(prefix))
{
switch (strategy)
{
...
default:
/* For all other cases, we can be sure there is
no match */
bitmap = 0;
so an IPv6 key against an IPv4-prefixed inner tuple prunes the whole
subtree. The consistent functions look correct to me; picksplit is what
breaks their premise.
This also accounts for the conditions above: two IPv4 values with no
common leading bit are what drive commonbits to zero, insertion order
decides whether the family check or the commonbits break fires first,
and a page split is what calls picksplit at all.
A fix would need to separate the two questions. Dropping the
commonbits == 0 break is the smaller change, at the cost of always
scanning one page's worth of entries. Keeping the early exit means
answering the family question in its own pass first. I have not
submitted a patch; which of those is right, and whether the family scan
belongs somewhere cheaper, is a judgement for people who know this code.
If this is fixed, existing SP-GiST indexes on mixed-family inet columns
will need REINDEX, since the misfiled entries are already on disk.
Affected versions
-----------------
The loop above is byte-for-byte identical to the commit that introduced
the opclass -- 77e2906821e2aec3c0807866a84c2934feeac8be, "Create an
SP-GiST opclass for inet/cidr", 2016-08-23, first released in
PostgreSQL 10. git log over network_spgist.c shows no functional change
since: copyright updates, pgindent, header cleanups and the
palloc_array() conversion.
So every version carrying inet_ops for SP-GiST should be affected --
PostgreSQL 10 through master. I have verified 18.6 and master directly
and have not built the other branches.
view thread (6+ messages) latest in thread
Message-ID: <19700-ceb6636b38e26eca@postgresql.org>
Permalink: ../19700-ceb6636b38e26eca@postgresql.org/
Also on: postgresql.org/message-id/19700-ceb6636b38e26eca@postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, kehan5800@gmail.com
Subject: Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
In-Reply-To: <19700-ceb6636b38e26eca@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox