Обсуждение: No result when selecting attstattarget from pg_attribute
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
Disclaimer:
Diese Nachricht und ihr eventuell angehängte Dateien sind nur für den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich geschützte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich erreicht hat, bitten wir Sie höflich, diese unter Ausschluss jeglicher Reproduktion zu löschen und die absendende Person zu benachrichtigen. Danke für Ihre Hilfe.
This message and any attached files are for the sole use of the recipient named above. It may contain confidential or legally protected data or information. If you have received this message in error, please delete it without making any copies whatsoever and notify the sender. Thank you for your assistance.
Вложения
On Thu, 2019-11-28 at 09:55 +0000, William Sescu (Suva) wrote:
> 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
attnamein ('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.
"attstattarget" is set to a value different from -1 if you define a special
statistics detail level for the column:
ALTER TABLE t1 ALTER a SET STATISTICS 1000;
It has nothing to do with extended statistics.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
"William Sescu (Suva)" <william.sescu@suva.ch> writes: > 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. attstattarget has nothing to do with extended statistics. It's for recording "ALTER TABLE ... SET STATISTICS n" commands, which just control the granularity of the regular (pg_statistic) statistics. Look into pg_statistic_ext to see the effects of CREATE STATISTICS. regards, tom lane
Thank you Tom for clarification. However, in regards of pg_upgrade, which does not transfer statistics, how do I know
how to create the "CREATE STATISTICS" on the new PostgreSQL version?
e.g. Developers created a whole bunch of "CREATE STATISTICS" on all kind of tables, which ends up in pg_statistic_ext
view.
select * from pg_statistic_ext;
stxrelid | stxname | stxnamespace | stxowner | stxkeys | stxkind | stxndistinct | stxdependencies
----------+---------+--------------+----------+---------+---------+--------------+----------------------
35600 | s1 | 16579 | 16569 | 1 2 | {f} | | {"1 => 2": 1.000000}
Is there any way how I can translate the view entries into into a SQL again?
CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us>
Gesendet: Donnerstag, 28. November 2019 15:54
An: Sescu William (SW0) <william.sescu@suva.ch>
Cc: pgsql-admin@postgresql.org
Betreff: Re: No result when selecting attstattarget from pg_attribute
"William Sescu (Suva)" <william.sescu@suva.ch> writes:
> 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.
attstattarget has nothing to do with extended statistics.
It's for recording "ALTER TABLE ... SET STATISTICS n"
commands, which just control the granularity of the regular (pg_statistic) statistics.
Look into pg_statistic_ext to see the effects of CREATE STATISTICS.
regards, tom lane
________________________________
Disclaimer:
Diese Nachricht und ihr eventuell angehängte Dateien sind nur für den Adressaten bestimmt. Sie kann vertrauliche oder
gesetzlichgeschützte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich erreicht hat, bitten wir
Siehöflich, diese unter Ausschluss jeglicher Reproduktion zu löschen und die absendende Person zu benachrichtigen.
Dankefür Ihre Hilfe.
This message and any attached files are for the sole use of the recipient named above. It may contain confidential or
legallyprotected data or information. If you have received this message in error, please delete it without making any
copieswhatsoever and notify the sender. Thank you for your assistance.
Вложения
On Thu, 2019-11-28 at 15:14 +0000, William Sescu (Suva) wrote:
> Thank you Tom for clarification. However, in regards of pg_upgrade, which does not transfer statistics, how do I
know
> how to create the "CREATE STATISTICS" on the new PostgreSQL version?
>
> e.g. Developers created a whole bunch of "CREATE STATISTICS" on all kind of tables, which ends up in pg_statistic_ext
view.
>
> select * from pg_statistic_ext;
> stxrelid | stxname | stxnamespace | stxowner | stxkeys | stxkind | stxndistinct | stxdependencies
> ----------+---------+--------------+----------+---------+---------+--------------+----------------------
> 35600 | s1 | 16579 | 16569 | 1 2 | {f} | | {"1 => 2": 1.000000}
>
> Is there any way how I can translate the view entries into into a SQL again?
>
> CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
You are worrying without need.
While the actual statistics won't be upgraded, the *definition* for
extended statistics will be. So your CREATE STATISTICS won't vanish
during an upgrade.
You'll have to ANALYZE after upgrading so that PostgreSQL collects the
extended statistics.
Extended statistics are stored in the "pg_statistic_ext" catalog.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
"William Sescu (Suva)" <william.sescu@suva.ch> writes:
> Thank you Tom for clarification. However, in regards of pg_upgrade, which does not transfer statistics, how do I know
> how to create the "CREATE STATISTICS" on the new PostgreSQL version?
You are apparently confusing the *definition* of a statistics object
with the *content*. pg_upgrade will certainly transfer the former.
Run ANALYZE to re-fill the latter.
regards, tom lane
Thanks. It looks like that I have mixed up there a few things. Now it's clear. -----Ursprüngliche Nachricht----- Von: Tom Lane <tgl@sss.pgh.pa.us> Gesendet: Donnerstag, 28. November 2019 16:29 An: Sescu William (SW0) <william.sescu@suva.ch> Cc: pgsql-admin@postgresql.org Betreff: Re: AW: No result when selecting attstattarget from pg_attribute "William Sescu (Suva)" <william.sescu@suva.ch> writes: > Thank you Tom for clarification. However, in regards of pg_upgrade, > which does not transfer statistics, how do I know how to create the "CREATE STATISTICS" on the new PostgreSQL version? You are apparently confusing the *definition* of a statistics object with the *content*. pg_upgrade will certainly transferthe former. Run ANALYZE to re-fill the latter. regards, tom lane ________________________________ Disclaimer: Diese Nachricht und ihr eventuell angehängte Dateien sind nur für den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlichgeschützte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich erreicht hat, bitten wir Siehöflich, diese unter Ausschluss jeglicher Reproduktion zu löschen und die absendende Person zu benachrichtigen. Dankefür Ihre Hilfe. This message and any attached files are for the sole use of the recipient named above. It may contain confidential or legallyprotected data or information. If you have received this message in error, please delete it without making any copieswhatsoever and notify the sender. Thank you for your assistance.