public inbox for [email protected]
help / color / mirror / Atom feedFrom: Corey Huinker <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: [email protected]
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump
Date: Mon, 9 Mar 2026 18:42:46 -0400
Message-ID: <CADkLM=dhYeJtP+8h8k4ULCu-P1pNHmFyDFBm8B0WU6A6i+NgPQ@mail.gmail.com> (raw)
In-Reply-To: <aa81xZp421DSkHiW@nathan>
References: <CADkLM=d=udo8pxPeLQA6Z2YKLMgTvJM_Uh2mYtsCE38fJ8xodw@mail.gmail.com>
<CAA5RZ0uTxOgN-sfQMV5-3dwE9SqgS3_xf5zhj0Gp12TJtBuk5g@mail.gmail.com>
<CADkLM=cz=bEvRRj0mSffMrVW56M=1O-KqavCZv19on-MQrkZwg@mail.gmail.com>
<aateiqvWO53UjoNI@nathan>
<aathGcXAIpiUSP_s@nathan>
<CAA5RZ0vCuDDd6-H00aabjLri0yO_AMJQ4Lb4017pepqU5WNyFg@mail.gmail.com>
<aa7sZcuRrlkeVJGX@nathan>
<CADkLM=eUR5co+o3PU8M=Fo8rRynj4xf=PFtt2KisD96bdrPEOg@mail.gmail.com>
<CAA5RZ0s674FM8vcEqr+DUVjWOpYSPS-rG8uANdJKEnSyo+tObA@mail.gmail.com>
<CADkLM=cYLk7bi3QVMBGWsFEzNfpqyV1Es3tWCwfERS88MdULWQ@mail.gmail.com>
<aa81xZp421DSkHiW@nathan>
On Mon, Mar 9, 2026 at 5:04 PM Nathan Bossart <[email protected]>
wrote:
> On Mon, Mar 09, 2026 at 03:28:40PM -0400, Corey Huinker wrote:
> > Presently, I don't think we make any changes to pg_dump, unless Nathan
> > feels strongly that we should. If and when the need for oid-based
> fetching
> > of extended stats becomes necessary, we'll at least have a couple
> versions
> > where the catalog already had the oids handy.
>
> That's fine with me.
>
> --
> nathan
>
I was sidetracked for a bit because the tests (which are accurate) seemed
strange in that pg_stats_ext_exprs would have 2 rows with NULL
inherited...and it took me a bit to realize that that actually makes sense
because the pg_stats_ext_exprs should have one row per expression whether
or not there are stats to match it. If that sounds like it could mess up
vacuumdb, it actually can't, because we base those tests based off
pg_stats_ext not pg_stats_ext_exprs - which does vary according to whether
stats exist for the object or not. I added a comment to the regression test
to that effect.
The new column expr_attnum gives the negative number that a given
expression has in pg_dependencites and pg_ndistinct - as you might imagine,
the number is determined by its order within the list of expressions.
If you want 0003 split into two (one for pg_stats_ext and one for
pg_stats_ext_exprs) I can do that, but they felt like a package deal to me.
Attachments:
[text/x-patch] v4-0001-Add-tableid-and-attnum-columns-to-pg_stats.patch (76.7K, 3-v4-0001-Add-tableid-and-attnum-columns-to-pg_stats.patch)
download | inline diff:
From 5e393d997411b78ceea01e054765ffed214a303f Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Tue, 24 Feb 2026 16:12:55 -0500
Subject: [PATCH v4 1/3] 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 ecb7c996e86..70846f1555d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -189,9 +189,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 deb6e2ad6a9..46be5c7ac90 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2524,7 +2524,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 c7adb783da2..8d66d4d9329 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -642,15 +642,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)
--
@@ -669,15 +673,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
@@ -694,15 +702,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
@@ -720,15 +732,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
@@ -746,15 +762,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
@@ -774,15 +794,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
@@ -801,15 +825,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
@@ -826,15 +854,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
@@ -852,15 +884,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
@@ -876,15 +912,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
@@ -902,15 +942,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
@@ -927,15 +971,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
@@ -955,15 +1003,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
@@ -981,15 +1033,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
@@ -1007,15 +1063,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
@@ -1032,15 +1092,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
@@ -1059,15 +1123,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
@@ -1083,15 +1151,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
@@ -1111,15 +1183,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
@@ -1139,15 +1215,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
@@ -1165,15 +1245,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
@@ -1191,15 +1275,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
@@ -1216,15 +1304,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
@@ -1243,15 +1335,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 0518bbf6f42..8fbec83b5e0 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -520,7 +520,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'
@@ -539,7 +543,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'
@@ -555,7 +563,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'
@@ -572,7 +584,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'
@@ -589,7 +605,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'
@@ -607,7 +627,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'
@@ -625,7 +649,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'
@@ -642,7 +670,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'
@@ -659,7 +691,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'
@@ -675,7 +711,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'
@@ -692,7 +732,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'
@@ -709,7 +753,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'
@@ -727,7 +775,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'
@@ -744,7 +796,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'
@@ -761,7 +817,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'
@@ -778,7 +838,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'
@@ -795,7 +859,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'
@@ -811,7 +879,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'
@@ -829,7 +901,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'
@@ -847,7 +923,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'
@@ -864,7 +944,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'
@@ -881,7 +965,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'
@@ -898,7 +986,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'
@@ -915,7 +1007,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..8214f3cd062 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>tableid</structfield> <type>oid</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: 6307b096e2599edfe238816118b3f365a73fd12a
--
2.53.0
[text/x-patch] v4-0002-pg_dump-Use-tableid-in-getAttributeStats.patch (8.0K, 4-v4-0002-pg_dump-Use-tableid-in-getAttributeStats.patch)
download | inline diff:
From 47896c0539cf7e329e8d5d7da4b740c09aa6fab7 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Wed, 25 Feb 2026 15:56:14 -0500
Subject: [PATCH v4 2/3] 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 137161aa5e0..9cdacbc3467 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7224,6 +7224,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;
@@ -11118,8 +11119,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;
@@ -11153,8 +11155,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
@@ -11167,14 +11179,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)
@@ -11182,16 +11215,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;
}
@@ -11250,8 +11297,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, "
@@ -11270,22 +11327,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 "
@@ -11293,7 +11358,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 1c11a79083f..6fa248cc812 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
[text/x-patch] v4-0003-Add-tableid-statid-expr_attnum-columns-to-pg_stat.patch (27.3K, 5-v4-0003-Add-tableid-statid-expr_attnum-columns-to-pg_stat.patch)
download | inline diff:
From 5a0be7b5f683da6e8636fcc6cba830227b16c358 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Mon, 9 Mar 2026 16:53:43 -0400
Subject: [PATCH v4 3/3] Add tableid, statid, expr_attnum columns to
pg_stats_ext[_exprs].
This mimics the work done in the previous patch adding the columns
tableid and statid to both pg_stats_ext and pg_stats_ext_exprs. It also
adds expr_attnum to pg_stats_ext_exprs.
There is no present need for these additional columns, but if and when
pg_dump starts fetching extended statistics object stats in bulk, they
will be available.
---
src/backend/catalog/system_views.sql | 163 +++++++++++----------
src/test/regress/expected/rules.out | 119 ++++++++-------
src/test/regress/expected/stats_import.out | 22 ++-
src/test/regress/sql/stats_import.sql | 8 +
doc/src/sgml/system-views.sgml | 52 +++++++
5 files changed, 227 insertions(+), 137 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 70846f1555d..09274eac9c8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -280,8 +280,10 @@ REVOKE ALL ON pg_statistic FROM public;
CREATE VIEW pg_stats_ext WITH (security_barrier) AS
SELECT cn.nspname AS schemaname,
c.relname AS tablename,
+ s.stxrelid AS tableid,
sn.nspname AS statistics_schemaname,
s.stxname AS statistics_name,
+ s.oid AS statid,
pg_get_userbyid(s.stxowner) AS statistics_owner,
( SELECT array_agg(a.attname ORDER BY a.attnum)
FROM unnest(s.stxkeys) k
@@ -314,92 +316,97 @@ CREATE VIEW pg_stats_ext WITH (security_barrier) AS
CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
SELECT cn.nspname AS schemaname,
c.relname AS tablename,
+ s.stxrelid AS tableid,
sn.nspname AS statistics_schemaname,
s.stxname AS statistics_name,
+ s.oid AS statid,
pg_get_userbyid(s.stxowner) AS statistics_owner,
- stat.expr,
+ expr.expr,
+ 0 - expr.ordinality AS expr_attnum,
sd.stxdinherit AS inherited,
- (stat.a).stanullfrac AS null_frac,
- (stat.a).stawidth AS avg_width,
- (stat.a).stadistinct AS n_distinct,
- (CASE
- WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1
- WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2
- WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3
- WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4
- WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5
- END) AS most_common_vals,
- (CASE
- WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1
- WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2
- WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3
- WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4
- WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5
- END) AS most_common_freqs,
- (CASE
- WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1
- WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2
- WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3
- WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4
- WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5
- END) AS histogram_bounds,
- (CASE
- WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1]
- WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1]
- WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1]
- WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1]
- WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1]
- END) correlation,
- (CASE
- WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1
- WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2
- WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3
- WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4
- WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5
- END) AS most_common_elems,
- (CASE
- WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1
- WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2
- WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3
- WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4
- WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5
- END) AS most_common_elem_freqs,
- (CASE
- WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1
- WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2
- WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3
- WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4
- WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5
- END) AS elem_count_histogram,
- (CASE
- WHEN (stat.a).stakind1 = 6 THEN (stat.a).stavalues1
- WHEN (stat.a).stakind2 = 6 THEN (stat.a).stavalues2
- WHEN (stat.a).stakind3 = 6 THEN (stat.a).stavalues3
- WHEN (stat.a).stakind4 = 6 THEN (stat.a).stavalues4
- WHEN (stat.a).stakind5 = 6 THEN (stat.a).stavalues5
- END) AS range_length_histogram,
- (CASE
- WHEN (stat.a).stakind1 = 6 THEN (stat.a).stanumbers1[1]
- WHEN (stat.a).stakind2 = 6 THEN (stat.a).stanumbers2[1]
- WHEN (stat.a).stakind3 = 6 THEN (stat.a).stanumbers3[1]
- WHEN (stat.a).stakind4 = 6 THEN (stat.a).stanumbers4[1]
- WHEN (stat.a).stakind5 = 6 THEN (stat.a).stanumbers5[1]
- END) AS range_empty_frac,
- (CASE
- WHEN (stat.a).stakind1 = 7 THEN (stat.a).stavalues1
- WHEN (stat.a).stakind2 = 7 THEN (stat.a).stavalues2
- WHEN (stat.a).stakind3 = 7 THEN (stat.a).stavalues3
- WHEN (stat.a).stakind4 = 7 THEN (stat.a).stavalues4
- WHEN (stat.a).stakind5 = 7 THEN (stat.a).stavalues5
- END) AS range_bounds_histogram
+ a.stanullfrac AS null_frac,
+ a.stawidth AS avg_width,
+ a.stadistinct AS n_distinct,
+ CASE
+ WHEN a.stakind1 = 1 THEN a.stavalues1
+ WHEN a.stakind2 = 1 THEN a.stavalues2
+ WHEN a.stakind3 = 1 THEN a.stavalues3
+ WHEN a.stakind4 = 1 THEN a.stavalues4
+ WHEN a.stakind5 = 1 THEN a.stavalues5
+ END AS most_common_vals,
+ CASE
+ WHEN a.stakind1 = 1 THEN a.stanumbers1
+ WHEN a.stakind2 = 1 THEN a.stanumbers2
+ WHEN a.stakind3 = 1 THEN a.stanumbers3
+ WHEN a.stakind4 = 1 THEN a.stanumbers4
+ WHEN a.stakind5 = 1 THEN a.stanumbers5
+ END AS most_common_freqs,
+ CASE
+ WHEN a.stakind1 = 2 THEN a.stavalues1
+ WHEN a.stakind2 = 2 THEN a.stavalues2
+ WHEN a.stakind3 = 2 THEN a.stavalues3
+ WHEN a.stakind4 = 2 THEN a.stavalues4
+ WHEN a.stakind5 = 2 THEN a.stavalues5
+ END AS histogram_bounds,
+ CASE
+ WHEN a.stakind1 = 3 THEN a.stanumbers1[1]
+ WHEN a.stakind2 = 3 THEN a.stanumbers2[1]
+ WHEN a.stakind3 = 3 THEN a.stanumbers3[1]
+ WHEN a.stakind4 = 3 THEN a.stanumbers4[1]
+ WHEN a.stakind5 = 3 THEN a.stanumbers5[1]
+ END AS correlation,
+ CASE
+ WHEN a.stakind1 = 4 THEN a.stavalues1
+ WHEN a.stakind2 = 4 THEN a.stavalues2
+ WHEN a.stakind3 = 4 THEN a.stavalues3
+ WHEN a.stakind4 = 4 THEN a.stavalues4
+ WHEN a.stakind5 = 4 THEN a.stavalues5
+ END aS most_common_elems,
+ CASE
+ WHEN a.stakind1 = 4 THEN a.stanumbers1
+ WHEN a.stakind2 = 4 THEN a.stanumbers2
+ WHEN a.stakind3 = 4 THEN a.stanumbers3
+ WHEN a.stakind4 = 4 THEN a.stanumbers4
+ WHEN a.stakind5 = 4 THEN a.stanumbers5
+ END aS most_common_elem_freqs,
+ CASE
+ WHEN a.stakind1 = 5 THEN a.stanumbers1
+ WHEN a.stakind2 = 5 THEN a.stanumbers2
+ WHEN a.stakind3 = 5 THEN a.stanumbers3
+ WHEN a.stakind4 = 5 THEN a.stanumbers4
+ WHEN a.stakind5 = 5 THEN a.stanumbers5
+ END aS elem_count_histogram,
+ CASE
+ WHEN a.stakind1 = 6 THEN a.stavalues1
+ WHEN a.stakind2 = 6 THEN a.stavalues2
+ WHEN a.stakind3 = 6 THEN a.stavalues3
+ WHEN a.stakind4 = 6 THEN a.stavalues4
+ WHEN a.stakind5 = 6 THEN a.stavalues5
+ END aS range_length_histogram,
+ CASE
+ WHEN a.stakind1 = 6 THEN a.stanumbers1[1]
+ WHEN a.stakind2 = 6 THEN a.stanumbers2[1]
+ WHEN a.stakind3 = 6 THEN a.stanumbers3[1]
+ WHEN a.stakind4 = 6 THEN a.stanumbers4[1]
+ WHEN a.stakind5 = 6 THEN a.stanumbers5[1]
+ END aS range_empty_frac,
+ CASE
+ WHEN a.stakind1 = 7 THEN a.stavalues1
+ WHEN a.stakind2 = 7 THEN a.stavalues2
+ WHEN a.stakind3 = 7 THEN a.stavalues3
+ WHEN a.stakind4 = 7 THEN a.stavalues4
+ WHEN a.stakind5 = 7 THEN a.stavalues5
+ END AS range_bounds_histogram
FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
- JOIN LATERAL (
- SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
- unnest(sd.stxdexpr)::pg_statistic AS a
- ) stat ON (stat.expr IS NOT NULL)
+ JOIN LATERAL unnest(pg_get_statisticsobjdef_expressions(s.oid))
+ WITH ORDINALITY AS expr(expr, ordinality)
+ ON s.stxexprs IS NOT NULL
+ LEFT JOIN LATERAL unnest(sd.stxdexpr)
+ WITH ORDINALITY AS a
+ ON a.ordinality = expr.ordinality
WHERE pg_has_role(c.relowner, 'USAGE')
AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 46be5c7ac90..eab38a43609 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2618,8 +2618,10 @@ pg_stats| SELECT n.nspname AS schemaname,
WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
pg_stats_ext| SELECT cn.nspname AS schemaname,
c.relname AS tablename,
+ s.stxrelid AS tableid,
sn.nspname AS statistics_schemaname,
s.stxname AS statistics_name,
+ s.oid AS statid,
pg_get_userbyid(s.stxowner) AS statistics_owner,
( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
FROM (unnest(s.stxkeys) k(k)
@@ -2646,101 +2648,104 @@ pg_stats_ext| SELECT cn.nspname AS schemaname,
WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
c.relname AS tablename,
+ s.stxrelid AS tableid,
sn.nspname AS statistics_schemaname,
s.stxname AS statistics_name,
+ s.oid AS statid,
pg_get_userbyid(s.stxowner) AS statistics_owner,
- stat.expr,
+ expr.expr,
+ (0 - expr.ordinality) AS expr_attnum,
sd.stxdinherit AS inherited,
- (stat.a).stanullfrac AS null_frac,
- (stat.a).stawidth AS avg_width,
- (stat.a).stadistinct AS n_distinct,
+ a.stanullfrac AS null_frac,
+ a.stawidth AS avg_width,
+ a.stadistinct AS n_distinct,
CASE
- WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stavalues1
- WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stavalues2
- WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stavalues3
- WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stavalues4
- WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stavalues5
+ WHEN (a.stakind1 = 1) THEN a.stavalues1
+ WHEN (a.stakind2 = 1) THEN a.stavalues2
+ WHEN (a.stakind3 = 1) THEN a.stavalues3
+ WHEN (a.stakind4 = 1) THEN a.stavalues4
+ WHEN (a.stakind5 = 1) THEN a.stavalues5
ELSE NULL::anyarray
END AS most_common_vals,
CASE
- WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stanumbers1
- WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stanumbers2
- WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stanumbers3
- WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stanumbers4
- WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stanumbers5
+ WHEN (a.stakind1 = 1) THEN a.stanumbers1
+ WHEN (a.stakind2 = 1) THEN a.stanumbers2
+ WHEN (a.stakind3 = 1) THEN a.stanumbers3
+ WHEN (a.stakind4 = 1) THEN a.stanumbers4
+ WHEN (a.stakind5 = 1) THEN a.stanumbers5
ELSE NULL::real[]
END AS most_common_freqs,
CASE
- WHEN ((stat.a).stakind1 = 2) THEN (stat.a).stavalues1
- WHEN ((stat.a).stakind2 = 2) THEN (stat.a).stavalues2
- WHEN ((stat.a).stakind3 = 2) THEN (stat.a).stavalues3
- WHEN ((stat.a).stakind4 = 2) THEN (stat.a).stavalues4
- WHEN ((stat.a).stakind5 = 2) THEN (stat.a).stavalues5
+ WHEN (a.stakind1 = 2) THEN a.stavalues1
+ WHEN (a.stakind2 = 2) THEN a.stavalues2
+ WHEN (a.stakind3 = 2) THEN a.stavalues3
+ WHEN (a.stakind4 = 2) THEN a.stavalues4
+ WHEN (a.stakind5 = 2) THEN a.stavalues5
ELSE NULL::anyarray
END AS histogram_bounds,
CASE
- WHEN ((stat.a).stakind1 = 3) THEN (stat.a).stanumbers1[1]
- WHEN ((stat.a).stakind2 = 3) THEN (stat.a).stanumbers2[1]
- WHEN ((stat.a).stakind3 = 3) THEN (stat.a).stanumbers3[1]
- WHEN ((stat.a).stakind4 = 3) THEN (stat.a).stanumbers4[1]
- WHEN ((stat.a).stakind5 = 3) THEN (stat.a).stanumbers5[1]
+ WHEN (a.stakind1 = 3) THEN a.stanumbers1[1]
+ WHEN (a.stakind2 = 3) THEN a.stanumbers2[1]
+ WHEN (a.stakind3 = 3) THEN a.stanumbers3[1]
+ WHEN (a.stakind4 = 3) THEN a.stanumbers4[1]
+ WHEN (a.stakind5 = 3) THEN a.stanumbers5[1]
ELSE NULL::real
END AS correlation,
CASE
- WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stavalues1
- WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stavalues2
- WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stavalues3
- WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stavalues4
- WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stavalues5
+ WHEN (a.stakind1 = 4) THEN a.stavalues1
+ WHEN (a.stakind2 = 4) THEN a.stavalues2
+ WHEN (a.stakind3 = 4) THEN a.stavalues3
+ WHEN (a.stakind4 = 4) THEN a.stavalues4
+ WHEN (a.stakind5 = 4) THEN a.stavalues5
ELSE NULL::anyarray
END AS most_common_elems,
CASE
- WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stanumbers1
- WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stanumbers2
- WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stanumbers3
- WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stanumbers4
- WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stanumbers5
+ WHEN (a.stakind1 = 4) THEN a.stanumbers1
+ WHEN (a.stakind2 = 4) THEN a.stanumbers2
+ WHEN (a.stakind3 = 4) THEN a.stanumbers3
+ WHEN (a.stakind4 = 4) THEN a.stanumbers4
+ WHEN (a.stakind5 = 4) THEN a.stanumbers5
ELSE NULL::real[]
END AS most_common_elem_freqs,
CASE
- WHEN ((stat.a).stakind1 = 5) THEN (stat.a).stanumbers1
- WHEN ((stat.a).stakind2 = 5) THEN (stat.a).stanumbers2
- WHEN ((stat.a).stakind3 = 5) THEN (stat.a).stanumbers3
- WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4
- WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5
+ WHEN (a.stakind1 = 5) THEN a.stanumbers1
+ WHEN (a.stakind2 = 5) THEN a.stanumbers2
+ WHEN (a.stakind3 = 5) THEN a.stanumbers3
+ WHEN (a.stakind4 = 5) THEN a.stanumbers4
+ WHEN (a.stakind5 = 5) THEN a.stanumbers5
ELSE NULL::real[]
END AS elem_count_histogram,
CASE
- WHEN ((stat.a).stakind1 = 6) THEN (stat.a).stavalues1
- WHEN ((stat.a).stakind2 = 6) THEN (stat.a).stavalues2
- WHEN ((stat.a).stakind3 = 6) THEN (stat.a).stavalues3
- WHEN ((stat.a).stakind4 = 6) THEN (stat.a).stavalues4
- WHEN ((stat.a).stakind5 = 6) THEN (stat.a).stavalues5
+ WHEN (a.stakind1 = 6) THEN a.stavalues1
+ WHEN (a.stakind2 = 6) THEN a.stavalues2
+ WHEN (a.stakind3 = 6) THEN a.stavalues3
+ WHEN (a.stakind4 = 6) THEN a.stavalues4
+ WHEN (a.stakind5 = 6) THEN a.stavalues5
ELSE NULL::anyarray
END AS range_length_histogram,
CASE
- WHEN ((stat.a).stakind1 = 6) THEN (stat.a).stanumbers1[1]
- WHEN ((stat.a).stakind2 = 6) THEN (stat.a).stanumbers2[1]
- WHEN ((stat.a).stakind3 = 6) THEN (stat.a).stanumbers3[1]
- WHEN ((stat.a).stakind4 = 6) THEN (stat.a).stanumbers4[1]
- WHEN ((stat.a).stakind5 = 6) THEN (stat.a).stanumbers5[1]
+ WHEN (a.stakind1 = 6) THEN a.stanumbers1[1]
+ WHEN (a.stakind2 = 6) THEN a.stanumbers2[1]
+ WHEN (a.stakind3 = 6) THEN a.stanumbers3[1]
+ WHEN (a.stakind4 = 6) THEN a.stanumbers4[1]
+ WHEN (a.stakind5 = 6) THEN a.stanumbers5[1]
ELSE NULL::real
END AS range_empty_frac,
CASE
- WHEN ((stat.a).stakind1 = 7) THEN (stat.a).stavalues1
- WHEN ((stat.a).stakind2 = 7) THEN (stat.a).stavalues2
- WHEN ((stat.a).stakind3 = 7) THEN (stat.a).stavalues3
- WHEN ((stat.a).stakind4 = 7) THEN (stat.a).stavalues4
- WHEN ((stat.a).stakind5 = 7) THEN (stat.a).stavalues5
+ WHEN (a.stakind1 = 7) THEN a.stavalues1
+ WHEN (a.stakind2 = 7) THEN a.stavalues2
+ WHEN (a.stakind3 = 7) THEN a.stavalues3
+ WHEN (a.stakind4 = 7) THEN a.stavalues4
+ WHEN (a.stakind5 = 7) THEN a.stavalues5
ELSE NULL::anyarray
END AS range_bounds_histogram
- FROM (((((pg_statistic_ext s
+ FROM ((((((pg_statistic_ext s
JOIN pg_class c ON ((c.oid = s.stxrelid)))
LEFT JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
- JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
- unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL)))
+ JOIN LATERAL unnest(pg_get_statisticsobjdef_expressions(s.oid)) WITH ORDINALITY expr(expr, ordinality) ON ((s.stxexprs IS NOT NULL)))
+ LEFT JOIN LATERAL unnest(sd.stxdexpr) WITH ORDINALITY a(starelid, staattnum, stainherit, stanullfrac, stawidth, stadistinct, stakind1, stakind2, stakind3, stakind4, stakind5, staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, stavalues1, stavalues2, stavalues3, stavalues4, stavalues5, ordinality) ON ((a.ordinality = expr.ordinality)))
WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 8d66d4d9329..68e1b75f5e2 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -66,7 +66,7 @@ SELECT COUNT(*) FROM pg_attribute
attnum > 0;
count
-------
- 15
+ 17
(1 row)
-- Create a view that is used purely for the type based on pg_stats_ext.
@@ -107,7 +107,7 @@ SELECT COUNT(*) FROM pg_attribute
attnum > 0;
count
-------
- 20
+ 23
(1 row)
-- Create a view that is used purely for the type based on pg_stats_ext_exprs.
@@ -1684,6 +1684,8 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e
-------+-----------
(0 rows)
+-- A NULL value on inherited makes sense here because the rows are expanded
+-- from pg_statistic_ext but the values come from the empty pg_statistic_ext_data.
SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
WHERE e.statistics_schemaname = 'stats_import' AND
e.statistics_name = 'test_stat' GROUP BY e.inherited;
@@ -1701,6 +1703,14 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e
1 | t
(1 row)
+SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
+ WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'part_parent_stat' GROUP BY e.inherited;
+ count | inherited
+-------+-----------
+ 1 | t
+(1 row)
+
SELECT pg_catalog.pg_clear_extended_stats(
schemaname => 'stats_import',
relname => 'part_parent',
@@ -1719,6 +1729,14 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e
-------+-----------
(0 rows)
+SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
+ WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'part_parent_stat' GROUP BY e.inherited;
+ count | inherited
+-------+-----------
+ 1 |
+(1 row)
+
-- Check that MAINTAIN is required when clearing statistics.
CREATE ROLE regress_test_extstat_clear;
GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_clear;
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 8fbec83b5e0..081ff936fd8 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -1268,6 +1268,8 @@ COMMIT;
SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e
WHERE e.statistics_schemaname = 'stats_import' AND
e.statistics_name = 'test_stat' GROUP BY e.inherited;
+-- A NULL value on inherited makes sense here because the rows are expanded
+-- from pg_statistic_ext but the values come from the empty pg_statistic_ext_data.
SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
WHERE e.statistics_schemaname = 'stats_import' AND
e.statistics_name = 'test_stat' GROUP BY e.inherited;
@@ -1275,6 +1277,9 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e
WHERE e.statistics_schemaname = 'stats_import' AND
e.statistics_name = 'part_parent_stat' GROUP BY e.inherited;
+SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
+ WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'part_parent_stat' GROUP BY e.inherited;
SELECT pg_catalog.pg_clear_extended_stats(
schemaname => 'stats_import',
relname => 'part_parent',
@@ -1284,6 +1289,9 @@ SELECT pg_catalog.pg_clear_extended_stats(
SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e
WHERE e.statistics_schemaname = 'stats_import' AND
e.statistics_name = 'part_parent_stat' GROUP BY e.inherited;
+SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e
+ WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'part_parent_stat' GROUP BY e.inherited;
-- Check that MAINTAIN is required when clearing statistics.
CREATE ROLE regress_test_extstat_clear;
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 8214f3cd062..25a013abbf8 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -4686,6 +4686,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>tableid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>)
+ </para>
+ <para>
+ ID of the table
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>statistics_schemaname</structfield> <type>name</type>
@@ -4706,6 +4716,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>statid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ ID of the extended statistics object
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>statistics_owner</structfield> <type>name</type>
@@ -4897,6 +4917,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>tableid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>)
+ </para>
+ <para>
+ ID of the table
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>statistics_schemaname</structfield> <type>name</type>
@@ -4917,6 +4947,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>statid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ ID of the extended statistics object
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>statistics_owner</structfield> <type>name</type>
@@ -4936,6 +4976,18 @@ 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>expr_attnum</structfield> <type>int2</type>
+ </para>
+ <para>
+ Synthetic attnum used to reference this expression in
+ <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxdndistinct</structfield>
+ and
+ <link linkend="catalog-pg-class"><structname>pg_statistic_ext</structname></link>.<structfield>stxddependencies</structfield>
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>inherited</structfield> <type>bool</type>
--
2.53.0
view thread (36+ messages) latest in thread
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump
In-Reply-To: <CADkLM=dhYeJtP+8h8k4ULCu-P1pNHmFyDFBm8B0WU6A6i+NgPQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox