Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible

On Sat, 19 Sept 2026 at 05:14, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19700
> Logged by: Ke Han
> Email address: kehan5800(at)gmail(dot)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.
>
>
>
>

Hi!
Reproduced this bug. Also I think your analysis is correct.

With this (obvious?) diff issue disappears:

reshke(at)reshke:~/postgres$ git diff src/backend/utils/adt/network_spgist.c
diff --git a/src/backend/utils/adt/network_spgist.c
b/src/backend/utils/adt/network_spgist.c
index 52e3c666d4f..1cd065f57c4 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -191,9 +191,8 @@ inet_spg_picksplit(PG_FUNCTION_ARGS)

if (ip_bits(tmp) < commonbits)
commonbits = ip_bits(tmp);
- commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp),
commonbits);
- if (commonbits == 0)
- break;
+ if (commonbits != 0)
+ commonbits = bitncommon(ip_addr(prefix),
ip_addr(tmp), commonbits);
}

/* Don't need labels; allocate output arrays */

--
Best regards,
Kirill Reshke