From: Michael Paquier <michael@paquier.xyz>
To: 303677365@qq.com
To: pgsql-bugs@lists.postgresql.org
Cc: Corey Huinker <corey.huinker@gmail.com>
Subject: Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
Date: Mon, 9 Nov 2026 14:28:37 +0900
Message-ID: <avFaBdhb4ONGhIjH@paquier.xyz> (raw)
In-Reply-To: <19842-ca042326dd2430a4@postgresql.org>
References: <19842-ca042326dd2430a4@postgresql.org>
On Sat, Nov 07, 2026 at 04:22:06AM +0000, PG Bug reporting form wrote:
> When an extended-statistics object has an expression of a range/multirange
> type, and fewer than 2 non-empty range values are sampled by ANALYZE (a
> 2-row table, or a column where most ranges are empty),
> `compute_range_stats()` still produces a statistics slot whose only range
> field is `range_empty_frac`. This is legal and intentional:
> `pg_stats_ext_exprs` exposes it, and `pg_dump --statistics-only` therefore
> emits a `pg_restore_extended_stats()` call whose `exprs` JSON contains only
> `range_empty_frac` (no `range_length_histogram`, no
> `range_bounds_histogram`).
>
> The restore side rejects exactly this input with a WARNING (and returns
> false), so on restore/pg_upgrade the expression statistics are silently
> lost. The planner then has no `range_empty_frac` and wrongfully assumes "no
> empty ranges", producing wrong selectivity/row estimates.
You are not mentioning the attribute case, that exists down to v18:
CREATE TABLE promo (id int, valid int4range);
INSERT INTO promo
SELECT i, CASE WHEN i = 1 THEN int4range(1, 100) ELSE 'empty' END
FROM generate_series(1, 10000) i;
ANALYZE promo;
SELECT range_length_histogram, range_empty_frac, range_bounds_histogram
FROM pg_stats WHERE tablename = 'promo' AND attname = 'valid';
This generates the following dump data:
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
'version', '200000'::integer,
'schemaname', 'public',
'relname', 'promo',
'attname', 'valid',
'inherited', 'f'::boolean,
'null_frac', '0'::real,
'avg_width', '6'::integer,
'n_distinct', '-1'::real,
'range_length_histogram', '{}'::text,
'range_empty_frac', '0.9999'::real);
And once a restore is attempted:
WARNING: 22023: "range_length_histogram" must be a one-dimensional
array
LOCATION: statatt_build_stavalues, stat_utils.c:627
pg_restore_attribute_stats
----------------------------
f
(1 row)
Hence the extstats case has just been copy-pasting the same rule as
previously, because we have the same 1-dimensional array requirement.
In order to do something about that, we would need to allow a case in
the restore paths where empty arrays are OK on an option-basis. That
means touching array_in_safe() and statatt_build_stavalues(), or
change their interfaces. Invasive, but after seeing 261f89a976bf I
am afraid that we have to fix that especially for the MCEs.
range_bounds_histogram should be mostly esoteric in this case, no?
Still it seems good to make consistent choices across the board,
especially as the range bound histograms require the same fixes.
Corey, what's your take?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
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: michael@paquier.xyz, 303677365@qq.com, corey.huinker@gmail.com
Subject: Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
In-Reply-To: <avFaBdhb4ONGhIjH@paquier.xyz>
References: <19842-ca042326dd2430a4@postgresql.org> <avFaBdhb4ONGhIjH@paquier.xyz>
* 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