AW: No result when selecting attstattarget from pg_attribute

From: "William Sescu (Suva)" <william(dot)sescu(at)suva(dot)ch>
To: 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: AW: No result when selecting attstattarget from pg_attribute
Date: 2019-11-28 15:14:13
Message-ID: 9545ee300c12457f8ef09c8708d45f31@suva.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sss(dot)pgh(dot)pa(dot)us>
Gesendet: Donnerstag, 28. November 2019 15:54
An: Sescu William (SW0) <william(dot)sescu(at)suva(dot)ch>
Cc: pgsql-admin(at)postgresql(dot)org
Betreff: Re: No result when selecting attstattarget from pg_attribute

"William Sescu (Suva)" <william(dot)sescu(at)suva(dot)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.

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2019-11-28 15:23:33 Re: AW: No result when selecting attstattarget from pg_attribute
Previous Message Tom Lane 2019-11-28 14:54:14 Re: No result when selecting attstattarget from pg_attribute