pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: No result when selecting attstattarget from pg_attribute
3+ messages / 3 participants
[nested] [flat]
* AW: No result when selecting attstattarget from pg_attribute
@ 2019-11-28 15:14 William Sescu (Suva) <william.sescu@suva.ch>
2019-11-28 15:23 ` Re: AW: No result when selecting attstattarget from pg_attribute Laurenz Albe <laurenz.albe@cybertec.at>
2019-11-28 15:29 ` Re: AW: No result when selecting attstattarget from pg_attribute Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: William Sescu (Suva) @ 2019-11-28 15:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 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.
Attachments:
smime.p7s (application/pkcs7-signature, 5.3K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: AW: No result when selecting attstattarget from pg_attribute
2019-11-28 15:14 AW: No result when selecting attstattarget from pg_attribute William Sescu (Suva) <william.sescu@suva.ch>
@ 2019-11-28 15:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2019-11-28 15:23 UTC (permalink / raw)
To: William Sescu (Suva) <william.sescu@suva.ch>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: AW: No result when selecting attstattarget from pg_attribute
2019-11-28 15:14 AW: No result when selecting attstattarget from pg_attribute William Sescu (Suva) <william.sescu@suva.ch>
@ 2019-11-28 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2019-11-28 15:29 UTC (permalink / raw)
To: William Sescu (Suva) <william.sescu@suva.ch>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-11-28 15:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-28 15:14 AW: No result when selecting attstattarget from pg_attribute William Sescu (Suva) <william.sescu@suva.ch>
2019-11-28 15:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-11-28 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox