Hello %
I was following the create statistics example from the doc
https://www.postgresql.org/docs/11/sql-createstatistics.html
and wondered why I don't see any results when selecting attstattarget from pg_attribute.
Version: PostgreSQL 11.5
(klb@[local]:55000)[knowledgebase]> CREATE TABLE t1 (
> a int,
> b int
> );
CREATE TABLE
(klb@[local]:55000)[knowledgebase]> INSERT INTO t1 SELECT i/100, i/500
> FROM generate_series(1,1000000) s(i);
INSERT 0 1000000
(klb@[local]:55000)[knowledgebase]> ANALYZE t1;
ANALYZE
-- No custom statistics here, which is OK
(klb@[local]:55000)[knowledgebase]> select attrelid::regclass as name, attname, attstattarget from pg_attribute where attname in ('a','b');
name | attname | attstattarget
------+---------+---------------
t1 | a | -1
t1 | b | -1
(2 rows)
(klb@[local]:55000)[knowledgebase]> SELECT attrelid::regclass, attname, attstattarget FROM pg_attribute WHERE attstattarget > 0 order by attstattarget desc;
attrelid | attname | attstattarget
----------+---------+---------------
(0 rows)
-- Now I create the custom statistics, however, attstattarget is still -1
(klb@[local]:55000)[knowledgebase]> CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
CREATE STATISTICS
(klb@[local]:55000)[knowledgebase]> select attrelid::regclass as name, attname, attstattarget from pg_attribute where attname in ('a','b');
name | attname | attstattarget
------+---------+---------------
t1 | a | -1
t1 | b | -1
(2 rows)
Am I looking at the wrong view? I am planning to do some upgrades to 12.x, and therefore, I created this little test case
to make sure that I don't forget to set custom statistics on the new version, if there are any.
Cheers
William