pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19821: pg_restore_attribute_stats() accepts an unsorted scalar histogram_bounds, producing badly wrong row
Date: Fri, 30 Oct 2026 07:01:30 +0000
Message-ID: <19821-0aa4084a3c6d9899@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19821
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux (TencentOS 4.4, x86_64)
Description:        

Hello,

pg_restore_attribute_stats() validates that range_bounds_histogram is
sorted in ascending order (stat_utils.c:842, commit df293aed46e "Reject
incorrect range_bounds_histograms in stats import functions").  The very
same function does not apply any ordering check to the scalar
histogram_bounds, even though the consumer, histogram_selectivity()
(src/backend/utils/adt/selfuncs.c), binary-searches the histogram and
therefore assumes ascending order.  So an unsorted (e.g. descending)
histogram_bounds is accepted -- the function returns true -- and every
row estimate made from it afterwards is arbitrary.

Reproducer (self-contained):

    CREATE TABLE h1 (a int) WITH (autovacuum_enabled = false);
    INSERT INTO h1 SELECT g FROM generate_series(1, 100) g;
    ANALYZE h1;

    -- baseline: sorted histogram built by ANALYZE
    EXPLAIN SELECT * FROM h1 WHERE a < 50;
    -- Seq Scan ... rows=48   (true value 49)

    -- import a DESCENDING histogram: accepted, returns true
    SELECT pg_restore_attribute_stats(
        'schemaname', 'public', 'relname', 'h1',
        'attname', 'a', 'inherited', false,
        'histogram_bounds', '{100,90,80,70,60,50,40,30,20,10,1}');
    --  t

    EXPLAIN SELECT * FROM h1 WHERE a < 50;   -- rows=1    (true value 49)
    EXPLAIN SELECT * FROM h1 WHERE a < 95;   -- rows=100  (true value 94)
    EXPLAIN SELECT * FROM h1 WHERE a > 95;   -- rows=1    (true value 5)

For comparison, in the very same function and session, the range
variant of the same mistake is properly rejected:

    SELECT pg_restore_attribute_stats(
        'schemaname', 'public', 'relname', 'h1',
        'attname', 'a', 'inherited', false,
        'range_bounds_histogram', '{"[90,93)","[1,4)","[50,53)"}');
    -- WARNING:  "range_bounds_histogram" must have its lower and upper
    --           bounds sorted in ascending order

Expected: histogram_bounds should get the same ordering check that
range_bounds_histogram already has (an unsorted input should produce a
WARNING and be skipped, not silently accepted).  The sibling slots
most_common_elems and elem_count_histogram also carry ordering/shape
contracts from ANALYZE that the import side does not verify, but the
scalar histogram is the one with a directly measurable effect on row
estimates shown above.

Actual: the function returns true and the planner's estimates become
arbitrary (49 -> 1, 94 -> 100, 5 -> 1).

Reproduced on:
  - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu)
  - PostgreSQL 18.6 (REL_18_STABLE, self-built)

A draft fix location would be the STATISTIC_KIND_HISTOGRAM branch of
pg_restore_attribute_stats() (src/backend/statistics/attribute_stats.c,
around lines 425-440): apply the same ascending-order check as
statatt_check_bounds_histogram() (src/backend/statistics/stat_utils.c).

Regards,
Chunling Qin








view thread (3+ messages)  latest in thread

Message-ID: <19821-0aa4084a3c6d9899@postgresql.org>
Permalink:  ../19821-0aa4084a3c6d9899@postgresql.org/
Also on:    postgresql.org/message-id/19821-0aa4084a3c6d9899@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19821: pg_restore_attribute_stats() accepts an unsorted scalar histogram_bounds, producing badly wrong row
  In-Reply-To: <19821-0aa4084a3c6d9899@postgresql.org>
  References: <19821-0aa4084a3c6d9899@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox