>
> I don't object to the idea, but I don't like exposing the name
> "starelid". That just screams implementation artifact, and it
> goes against the idea that pg_stats is trying to hide the physical
> representation of pg_statistic. I wish we could use "tableoid",
> but that's taken already as a system column. Maybe "tableid" or
> "tablerelid"?
>
I went with tableid, as it pairs with tablename, and at least they're
inaccurate in the same way.
>
> Also, the proposed column ordering seems excessively random:
>
> n.nspname AS schemaname,
> c.relname AS tablename,
> s.starelid AS starelid,
> a.attnum AS attnum,
> a.attname AS attname,
> stainherit AS inherited,
>
> I could see either of these as plausible:
>
> n.nspname AS schemaname,
> c.relname AS tablename,
> s.starelid AS tableid,
> a.attname AS attname,
> a.attnum AS attnum,
> stainherit AS inherited,
>
> n.nspname AS schemaname,
> c.relname AS tablename,
> a.attname AS attname,
> s.starelid AS tableid,
> a.attnum AS attnum,
> stainherit AS inherited,
>
> but I don't see the rationale behind your version.
>
The rationale went like this:
In order to get the grain of this table you need:
( (schemaname AND tablename) OR tableid )
AND
(attnum OR attname)
AND
inherited
As I stated earlier, I don't think there's a way to "win" with the column
ordering here, any ordering we choose will look awkward to at least one use
case. Your first suggestion seems fine to me, so I'm going with that one in
this round.
From aec84aefb0dc524a7781ec9678f7a61da09f161a Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Tue, 24 Feb 2026 16:12:55 -0500
Subject: [PATCH v2 1/2] Add tableid and attnum columns to pg_stats.
The primary purpose of this patch is to expose the starelid column of
pg_statistic in the pg_stats view. Having this available will allow us
to simplify some code in pg_dump which currently has to store arrays of
nspname+relname in order to fetch a resonable amount of statistics in
one query. Furthermore, the query used in pg_dump requires a redundant
qual in the WHERE clause to ensure that it uses a specific index, thus
avoiding a hash join and the expensive sequential scan of pg_statistic
that that entails.
Additionally, there have been times when it would have been nice to have
attnum as well as attname available.
---
src/backend/catalog/system_views.sql | 8 +-
src/test/regress/expected/rules.out | 2 +
src/test/regress/expected/stats_import.out | 288 ++++++++++++++-------
src/test/regress/sql/stats_import.sql | 144 +++++++++--
doc/src/sgml/system-views.sgml | 20 ++
5 files changed, 339 insertions(+), 123 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1ea8f1faa9e..014998dc380 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -184,9 +184,11 @@ CREATE VIEW pg_sequences AS
CREATE VIEW pg_stats WITH (security_barrier) AS
SELECT
- nspname AS schemaname,
- relname AS tablename,
- attname AS attname,
+ n.nspname AS schemaname,
+ c.relname AS tablename,
+ a.attrelid AS tableid,
+ a.attname AS attname,
+ a.attnum AS attnum,
stainherit AS inherited,
stanullfrac AS null_frac,
stawidth AS avg_width,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 78a37d9fc8f..c34ef96ec95 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2513,7 +2513,9 @@ pg_statio_user_tables| SELECT relid,
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
pg_stats| SELECT n.nspname AS schemaname,
c.relname AS tablename,
+ a.attrelid AS tableid,
a.attname,
+ a.attnum,
s.stainherit AS inherited,
s.stanullfrac AS null_frac,
s.stawidth AS avg_width,
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index d6cc701500e..3090cc6aac8 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -488,15 +488,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.2 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.2 | 5 | 0.6 | | | | | | | | | |
(1 row)
--
@@ -515,15 +519,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.4 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.4 | 5 | 0.6 | | | | | | | | | |
(1 row)
-- warn: unrecognized argument name, rest get set
@@ -540,15 +548,19 @@ WARNING: unrecognized argument name: "nope"
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.2 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.2 | 5 | 0.6 | | | | | | | | | |
(1 row)
-- warn: mcv / mcf null mismatch part 1, rest get set
@@ -566,15 +578,19 @@ WARNING: argument "most_common_vals" must be specified when argument "most_comm
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.21 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.21 | 5 | 0.6 | | | | | | | | | |
(1 row)
-- warn: mcv / mcf null mismatch part 2, rest get set
@@ -592,15 +608,19 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.21 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.21 | 5 | 0.6 | | | | | | | | | |
(1 row)
-- warn: mcf type mismatch, mcv-pair fails, rest get set
@@ -620,15 +640,19 @@ WARNING: argument "most_common_freqs" must be specified when argument "most_com
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.22 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.22 | 5 | 0.6 | | | | | | | | | |
(1 row)
-- warn: mcv cast failure, mcv-pair fails, rest get set
@@ -647,15 +671,19 @@ WARNING: invalid input syntax for type integer: "four"
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.23 | 5 | 0.6 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.23 | 5 | 0.6 | | | | | | | | | |
(1 row)
-- ok: mcv+mcf
@@ -672,15 +700,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.23 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.23 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | |
(1 row)
-- warn: NULL in histogram array, rest get set
@@ -698,15 +730,19 @@ WARNING: "histogram_bounds" array must not contain null values
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | |
(1 row)
-- ok: histogram_bounds
@@ -722,15 +758,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.24 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
(1 row)
-- warn: elem_count_histogram null element, rest get set
@@ -748,15 +788,19 @@ WARNING: argument "elem_count_histogram" array must not contain null values
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'tags';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | tags | f | 0.25 | 0 | 0 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | tags | 5 | f | 0.25 | 0 | 0 | | | | | | | | | |
(1 row)
-- ok: elem_count_histogram
@@ -773,15 +817,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'tags';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
- stats_import | test | tags | f | 0.26 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
+ stats_import | test | tags | 5 | f | 0.26 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
(1 row)
-- warn: range stats on a scalar type, rest ok
@@ -801,15 +849,19 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.27 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.27 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
(1 row)
-- warn: range_empty_frac range_length_hist null mismatch, rest ok
@@ -827,15 +879,19 @@ WARNING: argument "range_empty_frac" must be specified when argument "range_len
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'arange';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | arange | f | 0.28 | 0 | 0 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | arange | 4 | f | 0.28 | 0 | 0 | | | | | | | | | |
(1 row)
-- warn: range_empty_frac range_length_hist null mismatch part 2, rest ok
@@ -853,15 +909,19 @@ WARNING: argument "range_length_histogram" must be specified when argument "ran
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'arange';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | arange | f | 0.29 | 0 | 0 | | | | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | arange | 4 | f | 0.29 | 0 | 0 | | | | | | | | | |
(1 row)
-- ok: range_empty_frac + range_length_hist
@@ -878,15 +938,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'arange';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | arange | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | arange | 4 | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 |
(1 row)
-- warn: range bounds histogram on scalar, rest ok
@@ -905,15 +969,19 @@ DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUN
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.31 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.31 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
(1 row)
-- ok: range_bounds_histogram
@@ -929,15 +997,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'arange';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+--------------------------------------
- stats_import | test | arange | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"}
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+--------------------------------------
+ stats_import | test | arange | 4 | f | 0.29 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"}
(1 row)
-- warn: cannot set most_common_elems for range type, rest ok
@@ -957,15 +1029,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'arange';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+--------------------------------------
- stats_import | test | arange | f | 0.32 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"}
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+--------------------------------------
+ stats_import | test | arange | 4 | f | 0.32 | 0 | 0 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"}
(1 row)
-- warn: scalars can't have mcelem, rest ok
@@ -985,15 +1061,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.33 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.33 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
(1 row)
-- warn: mcelem / mcelem mismatch, rest ok
@@ -1011,15 +1091,19 @@ WARNING: argument "most_common_elem_freqs" must be specified when argument "mos
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'tags';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
- stats_import | test | tags | f | 0.34 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
+ stats_import | test | tags | 5 | f | 0.34 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
(1 row)
-- warn: mcelem / mcelem null mismatch part 2, rest ok
@@ -1037,15 +1121,19 @@ WARNING: argument "most_common_elems" must be specified when argument "most_com
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'tags';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
- stats_import | test | tags | f | 0.35 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
+ stats_import | test | tags | 5 | f | 0.35 | 0 | 0 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
(1 row)
-- ok: mcelem
@@ -1062,15 +1150,19 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'tags';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
- stats_import | test | tags | f | 0.35 | 0 | 0 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
+ stats_import | test | tags | 5 | f | 0.35 | 0 | 0 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
(1 row)
-- warn: scalars can't have elem_count_histogram, rest ok
@@ -1089,15 +1181,19 @@ DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
f
(1 row)
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
AND inherited = false
AND attname = 'id';
- schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
---------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
- stats_import | test | id | f | 0.36 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
+ schemaname | tablename | attname | attnum | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram
+--------------+-----------+---------+--------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+ stats_import | test | id | 1 | f | 0.36 | 5 | 0.6 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | |
(1 row)
-- test for multiranges
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 8db7cd93b88..d59d45c11b0 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -368,7 +368,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'avg_width', 5::integer,
'n_distinct', 0.6::real);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -387,7 +391,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'inherited', false::boolean,
'null_frac', 0.4::real);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -403,7 +411,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'null_frac', 0.2::real,
'nope', 0.5::real);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -420,7 +432,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_freqs', '{0.1,0.2,0.3}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -437,7 +453,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_vals', '{1,2,3}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -455,7 +475,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_freqs', '{0.2,0.1}'::double precision[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -473,7 +497,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_freqs', '{0.3,0.25,0.05}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -490,7 +518,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_freqs', '{0.3,0.25,0.05}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -507,7 +539,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'histogram_bounds', '{1,NULL,3,4}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -523,7 +559,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'histogram_bounds', '{1,2,3,4}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -540,7 +580,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'elem_count_histogram', '{1,1,NULL,1,1,1,1,1}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -557,7 +601,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -575,7 +623,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_length_histogram', '{399,499,Infinity}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -592,7 +644,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_length_histogram', '{399,499,Infinity}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -609,7 +665,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_empty_frac', 0.5::real
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -626,7 +686,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_length_histogram', '{399,499,Infinity}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -643,7 +707,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -659,7 +727,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -677,7 +749,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -695,7 +771,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -712,7 +792,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_elems', '{one,two}'::text
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -729,7 +813,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_elem_freqs', '{0.3,0.2,0.2,0.3}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -746,7 +834,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
@@ -763,7 +855,11 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1}'::real[]
);
-SELECT *
+SELECT schemaname, tablename, attname, attnum, inherited, null_frac, avg_width,
+ n_distinct, most_common_vals, most_common_freqs, histogram_bounds,
+ correlation, most_common_elems, most_common_elem_freqs,
+ elem_count_histogram, range_length_histogram, range_empty_frac,
+ range_bounds_histogram
FROM pg_stats
WHERE schemaname = 'stats_import'
AND tablename = 'test'
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index e5fe423fc61..51afd3002bf 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -4414,6 +4414,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>starelid</structfield> <type>tableid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_attribute</structname></link>.<structfield>attrelid</structfield>)
+ </para>
+ <para>
+ ID of the table
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>attname</structfield> <type>name</type>
@@ -4424,6 +4434,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>attnum</structfield> <type>int2</type>
+ (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>)
+ </para>
+ <para>
+ Position of column described by this row
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>inherited</structfield> <type>bool</type>
base-commit: 2176520089e8c78a2eadb693670f65eaabf8ede2
--
2.53.0
From 64d195fb0d98bedd9607e39947f36db54215f025 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Wed, 25 Feb 2026 15:56:14 -0500
Subject: [PATCH v2 2/2] pg_dump: Use tableid in getAttributeStats
The existing query for fetching attribute stats is clumsy for several
reasons. One is that the volume of stats returned is unpredictable and
could be very large, so stats must be fetched in medium-sized batches.
The other is that the stats fetching query is on pg_stats, which
historically does not expose tableid, requiring us to pass in an array
of schemanames and an array of tablenames and unnest them in pairs. This
results in a hash join which gives very poor performance, but adding an
extra qual was able to trick the query into using an existing index.
That trick always seems brittle because it is, and while it works on all
past versions, there is no guarantee that it will continue to work on
future versions.
With that in mind, change the pg_dump query to instead use tableid on
versions in which tableid is available in pg_stats. This virtually
guarantees that pg_statistic will use index lookups, eliminates the
"trick" qual mentioned above, and is just simpler.
---
src/bin/pg_dump/pg_dump.c | 102 +++++++++++++++++++++++++++++++-------
src/bin/pg_dump/pg_dump.h | 1 +
2 files changed, 84 insertions(+), 19 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index dd8adef0a3e..a34ea1f3325 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7137,6 +7137,7 @@ getRelationStatistics(Archive *fout, DumpableObject *rel, int32 relpages,
dobj->components |= DUMP_COMPONENT_STATISTICS;
dobj->name = pg_strdup(rel->name);
dobj->namespace = rel->namespace;
+ info->starelid = rel->catId.oid;
info->relpages = relpages;
info->reltuples = pstrdup(reltuples);
info->relallvisible = relallvisible;
@@ -11031,8 +11032,9 @@ static PGresult *
fetchAttributeStats(Archive *fout)
{
ArchiveHandle *AH = (ArchiveHandle *) fout;
- PQExpBuffer nspnames = createPQExpBuffer();
- PQExpBuffer relnames = createPQExpBuffer();
+ PQExpBuffer nspnames = NULL;
+ PQExpBuffer relnames = NULL;
+ PQExpBuffer starelids = NULL;
int count = 0;
PGresult *res = NULL;
static TocEntry *te;
@@ -11066,8 +11068,18 @@ fetchAttributeStats(Archive *fout)
restarted = true;
}
- appendPQExpBufferChar(nspnames, '{');
- appendPQExpBufferChar(relnames, '{');
+ if (fout->remoteVersion >= 190000)
+ {
+ starelids = createPQExpBuffer();
+ appendPQExpBufferChar(starelids, '{');
+ }
+ else
+ {
+ nspnames = createPQExpBuffer();
+ relnames = createPQExpBuffer();
+ appendPQExpBufferChar(nspnames, '{');
+ appendPQExpBufferChar(relnames, '{');
+ }
/*
* Scan the TOC for the next set of relevant stats entries. We assume
@@ -11080,14 +11092,35 @@ fetchAttributeStats(Archive *fout)
if ((te->reqs & REQ_STATS) != 0 &&
strcmp(te->desc, "STATISTICS DATA") == 0)
{
- appendPGArray(nspnames, te->namespace);
- appendPGArray(relnames, te->tag);
+ if (fout->remoteVersion >= 190000)
+ {
+ RelStatsInfo *rsinfo = (RelStatsInfo *) te->defnDumperArg;
+
+ if (rsinfo == NULL)
+ pg_fatal("statistics table oid information missing for %s.%s",
+ te->namespace, te->tag);
+
+ if (count > 0)
+ appendPQExpBufferChar(starelids, ',');
+ appendPQExpBuffer(starelids, "%u", rsinfo->starelid);
+ }
+ else
+ {
+ appendPGArray(nspnames, te->namespace);
+ appendPGArray(relnames, te->tag);
+ }
+
count++;
}
}
- appendPQExpBufferChar(nspnames, '}');
- appendPQExpBufferChar(relnames, '}');
+ if (fout->remoteVersion >= 190000)
+ appendPQExpBufferChar(starelids, '}');
+ else
+ {
+ appendPQExpBufferChar(nspnames, '}');
+ appendPQExpBufferChar(relnames, '}');
+ }
/* Execute the query for the next batch of relations. */
if (count > 0)
@@ -11095,16 +11128,30 @@ fetchAttributeStats(Archive *fout)
PQExpBuffer query = createPQExpBuffer();
appendPQExpBufferStr(query, "EXECUTE getAttributeStats(");
- appendStringLiteralAH(query, nspnames->data, fout);
- appendPQExpBufferStr(query, "::pg_catalog.name[],");
- appendStringLiteralAH(query, relnames->data, fout);
- appendPQExpBufferStr(query, "::pg_catalog.name[])");
+ if (fout->remoteVersion >= 190000)
+ {
+ appendStringLiteralAH(query, starelids->data, fout);
+ appendPQExpBufferStr(query, "::pg_catalog.oid[])");
+ }
+ else
+ {
+ appendStringLiteralAH(query, nspnames->data, fout);
+ appendPQExpBufferStr(query, "::pg_catalog.name[],");
+ appendStringLiteralAH(query, relnames->data, fout);
+ appendPQExpBufferStr(query, "::pg_catalog.name[])");
+ }
+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
destroyPQExpBuffer(query);
}
- destroyPQExpBuffer(nspnames);
- destroyPQExpBuffer(relnames);
+ if (fout->remoteVersion >= 190000)
+ destroyPQExpBuffer(starelids);
+ else
+ {
+ destroyPQExpBuffer(nspnames);
+ destroyPQExpBuffer(relnames);
+ }
return res;
}
@@ -11163,8 +11210,18 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te)
query = createPQExpBuffer();
if (!fout->is_prepared[PREPQUERY_GETATTRIBUTESTATS])
{
+ /*
+ * Before v19, the starelid was not available in pg_stats. Prior to
+ * that we must identify tables with schemaname+relname.
+ */
+ if (fout->remoteVersion >= 190000)
+ appendPQExpBufferStr(query,
+ "PREPARE getAttributeStats(pg_catalog.oid[]) AS\n");
+ else
+ appendPQExpBufferStr(query,
+ "PREPARE getAttributeStats(pg_catalog.name[], pg_catalog.name[]) AS\n");
+
appendPQExpBufferStr(query,
- "PREPARE getAttributeStats(pg_catalog.name[], pg_catalog.name[]) AS\n"
"SELECT s.schemaname, s.tablename, s.attname, s.inherited, "
"s.null_frac, s.avg_width, s.n_distinct, "
"s.most_common_vals, s.most_common_freqs, "
@@ -11183,22 +11240,30 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te)
"NULL AS range_empty_frac,"
"NULL AS range_bounds_histogram ");
+ appendPQExpBufferStr(query, "FROM pg_catalog.pg_stats s ");
+
/*
* The results must be in the order of the relations supplied in the
* parameters to ensure we remain in sync as we walk through the TOC.
+ *
* The redundant filter clause on s.tablename = ANY(...) seems
* sufficient to convince the planner to use
* pg_class_relname_nsp_index, which avoids a full scan of pg_stats.
- * This may not work for all versions.
+ * This seems to work for all version prior to v19, after which we
+ * will use the starelid, which is simpler.
*
* Our query for retrieving statistics for multiple relations uses
* WITH ORDINALITY and multi-argument UNNEST(), both of which were
* introduced in v9.4. For older versions, we resort to gathering
* statistics for a single relation at a time.
*/
- if (fout->remoteVersion >= 90400)
+ if (fout->remoteVersion >= 190000)
+ appendPQExpBufferStr(query,
+ "JOIN unnest($1) WITH ORDINALITY AS u (tableid, ord) "
+ "ON s.tableid = u.tableid "
+ "ORDER BY u.ord, s.attname, s.inherited");
+ else if (fout->remoteVersion >= 90400)
appendPQExpBufferStr(query,
- "FROM pg_catalog.pg_stats s "
"JOIN unnest($1, $2) WITH ORDINALITY AS u (schemaname, tablename, ord) "
"ON s.schemaname = u.schemaname "
"AND s.tablename = u.tablename "
@@ -11206,7 +11271,6 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te)
"ORDER BY u.ord, s.attname, s.inherited");
else
appendPQExpBufferStr(query,
- "FROM pg_catalog.pg_stats s "
"WHERE s.schemaname = $1[1] "
"AND s.tablename = $2[1] "
"ORDER BY s.attname, s.inherited");
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 6deceef23f3..1d6b215d6a5 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -448,6 +448,7 @@ typedef struct _indexAttachInfo
typedef struct _relStatsInfo
{
DumpableObject dobj;
+ Oid starelid;
int32 relpages;
char *reltuples;
int32 relallvisible;
--
2.53.0
