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

On Sat, 19 Sept 2026 at 05:14, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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.
>
>
>
>

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


With this (obvious?) diff issue disappears:

reshke@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