pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNo result when selecting attstattarget from pg_attribute
3+ messages / 3 participants
[nested] [flat]
* No result when selecting attstattarget from pg_attribute
@ 2019-11-28 09:55 William Sescu (Suva) <william.sescu@suva.ch>
2019-11-28 14:23 ` Re: No result when selecting attstattarget from pg_attribute Laurenz Albe <laurenz.albe@cybertec.at>
2019-11-28 14:54 ` Re: 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 09:55 UTC (permalink / raw)
To: pgsql-admin
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.
Attachments:
smime.p7s (application/pkcs7-signature, 5.3K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: No result when selecting attstattarget from pg_attribute
2019-11-28 09:55 No result when selecting attstattarget from pg_attribute William Sescu (Suva) <william.sescu@suva.ch>
@ 2019-11-28 14:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2019-11-28 14:23 UTC (permalink / raw)
To: William Sescu (Suva) <william.sescu@suva.ch>; pgsql-admin
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 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.
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: No result when selecting attstattarget from pg_attribute
2019-11-28 09:55 No result when selecting attstattarget from pg_attribute William Sescu (Suva) <william.sescu@suva.ch>
@ 2019-11-28 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2019-11-28 14:54 UTC (permalink / raw)
To: William Sescu (Suva) <william.sescu@suva.ch>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-11-28 14:54 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 09:55 No result when selecting attstattarget from pg_attribute William Sescu (Suva) <william.sescu@suva.ch>
2019-11-28 14:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-11-28 14:54 ` 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