Re: relkind='p' has no pg_stat_user_tables
On Thu, May 03, 2018 at 01:24:59PM -0500, Justin Pryzby wrote: > Yes, I was surprised about the difference between ANALYZE relkind_p > and relkind_r. > > But I see that's a documented behavior I'd missed until now: > > https://www.postgresql.org/docs/current/static/sql-analyze.html > |If the specified table is a partitioned table, both the inheritance > statistics > |of the partitioned table as a whole and statistics of the individual > partitions > |are updated. When I read this thread, it seems to me that more user-friendly system functions able to extract a sub-tree of child relations (by inheritance and/or partition) is something which would help. Now users are limited to things like large WITH RECURSIVE queries when willing to extract a full tree. While that's easily done with a custom function, there is room for an in-core function as well. I recall that Amit Langote has sent a patch which introduces a wrapper function on top of find_all_inheritors, perhaps that would get into v12. -- Michael signature.asc Description: PGP signature
Re: relkind='p' has no pg_stat_user_tables
On Thu, May 03, 2018 at 11:15:19AM -0700, Adrian Klaver wrote: > On 05/03/2018 10:38 AM, Justin Pryzby wrote: > >On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote: > >>On 05/03/2018 09:20 AM, Alvaro Herrera wrote: > https://www.postgresql.org/docs/10/static/sql-createtable.html > > "A partitioned table is divided into sub-tables (called partitions), which > are created using separate CREATE TABLE commands. The partitioned table is > itself empty. A data row inserted into the table is routed to a partition > based on the value of columns or expressions in the partition key. ... " > >>> > >>>Yeah, but I think Justin has a valid question from the POV of the user: > >>>how can we figure out if we need to re-run analyze on a partitioned > >>>table, if the time of last analyze is not stored anywhere? > >> > >>I agree. The only thing I can think of is, that knowing : > >> > >>ANALYZE VERBOSE t; > >> > >>walks the inheritance tree, look at the pg_stat_user_tables for one of the > >>children for the last time analyzed. > > > >I think I can make this work for my purposes: > > > >SELECT MIN(GREATEST(last_analyze,last_autoanalyze)) > >FROM pg_stat_user_tables psut > >JOIN pg_inherits i > >ON i.inhrelid=psut.relid > >WHERE i.inhparent=... > > > >I was about to say that it's perhaps more correct for relkind='r' parents, > >too. > > > >But actually, it looks like for relkind='p', ANALYZE populates stats on child > >tables in addition to the parent. For relkind='r', the behavior (introduced > >in > >PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent > >(both "inherited" stats including children, and "ONLY" stats for the > >potentially-nonempty parent). > > > >I guess ability to update child tables' stats is a nice feature, but I'm > >surprised. I wonder if that was a deliberate/documented change ? > > I was with you until I got to the above. You seem to be comparing apples and > oranges unless I am missing something. Yes, I was surprised about the difference between ANALYZE relkind_p and relkind_r. But I see that's a documented behavior I'd missed until now: https://www.postgresql.org/docs/current/static/sql-analyze.html |If the specified table is a partitioned table, both the inheritance statistics |of the partitioned table as a whole and statistics of the individual partitions |are updated. Thanks, Justin
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 10:38 AM, Justin Pryzby wrote: On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote: On 05/03/2018 09:20 AM, Alvaro Herrera wrote: https://www.postgresql.org/docs/10/static/sql-createtable.html "A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. ... " Yeah, but I think Justin has a valid question from the POV of the user: how can we figure out if we need to re-run analyze on a partitioned table, if the time of last analyze is not stored anywhere? I agree. The only thing I can think of is, that knowing : ANALYZE VERBOSE t; walks the inheritance tree, look at the pg_stat_user_tables for one of the children for the last time analyzed. I think I can make this work for my purposes: SELECT MIN(GREATEST(last_analyze,last_autoanalyze)) FROM pg_stat_user_tables psut JOIN pg_inherits i ON i.inhrelid=psut.relid WHERE i.inhparent=... I was about to say that it's perhaps more correct for relkind='r' parents, too. But actually, it looks like for relkind='p', ANALYZE populates stats on child tables in addition to the parent. For relkind='r', the behavior (introduced in PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent (both "inherited" stats including children, and "ONLY" stats for the potentially-nonempty parent). I guess ability to update child tables' stats is a nice feature, but I'm surprised. I wonder if that was a deliberate/documented change ? I was with you until I got to the above. You seem to be comparing apples and oranges unless I am missing something. The behavior for 'r' tables has not changed: https://www.postgresql.org/docs/9.0/static/sql-analyze.html https://www.postgresql.org/docs/10/static/sql-analyze.html The 'p' type table does not appear until version 10: https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html https://www.postgresql.org/docs/10/static/catalog-pg-class.html so there is no past behavior to compare to. Justin -- Adrian Klaver adrian.kla...@aklaver.com
Re: relkind='p' has no pg_stat_user_tables
On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote: > On 05/03/2018 09:20 AM, Alvaro Herrera wrote: > >>https://www.postgresql.org/docs/10/static/sql-createtable.html > >> > >>"A partitioned table is divided into sub-tables (called partitions), which > >>are created using separate CREATE TABLE commands. The partitioned table is > >>itself empty. A data row inserted into the table is routed to a partition > >>based on the value of columns or expressions in the partition key. ... " > > > >Yeah, but I think Justin has a valid question from the POV of the user: > >how can we figure out if we need to re-run analyze on a partitioned > >table, if the time of last analyze is not stored anywhere? > > I agree. The only thing I can think of is, that knowing : > > ANALYZE VERBOSE t; > > walks the inheritance tree, look at the pg_stat_user_tables for one of the > children for the last time analyzed. I think I can make this work for my purposes: SELECT MIN(GREATEST(last_analyze,last_autoanalyze)) FROM pg_stat_user_tables psut JOIN pg_inherits i ON i.inhrelid=psut.relid WHERE i.inhparent=... I was about to say that it's perhaps more correct for relkind='r' parents, too. But actually, it looks like for relkind='p', ANALYZE populates stats on child tables in addition to the parent. For relkind='r', the behavior (introduced in PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent (both "inherited" stats including children, and "ONLY" stats for the potentially-nonempty parent). I guess ability to update child tables' stats is a nice feature, but I'm surprised. I wonder if that was a deliberate/documented change ? Justin
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 09:20 AM, Alvaro Herrera wrote: Adrian Klaver wrote: and from here: https://www.postgresql.org/docs/10/static/sql-createtable.html "A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. ... " Yeah, but I think Justin has a valid question from the POV of the user: how can we figure out if we need to re-run analyze on a partitioned table, if the time of last analyze is not stored anywhere? I agree. The only thing I can think of is, that knowing : ANALYZE VERBOSE t; walks the inheritance tree, look at the pg_stat_user_tables for one of the children for the last time analyzed. Using psql -E and \d+ on table t I got the following to find the children: test_(aklaver)> SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '1417272' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; oid |pg_get_expr -+ t1 | FOR VALUES FROM (1) TO (9) -- Adrian Klaver adrian.kla...@aklaver.com
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 08:45 AM, Justin Pryzby wrote: On Thu, May 03, 2018 at 07:44:24AM -0700, Adrian Klaver wrote: On 05/03/2018 07:14 AM, Justin Pryzby wrote: I (finally) realized that my script for ANALYZEing parents of table hierarchies every month or so was looping around the same parent tables every night due to no stats for date of last last analysis. Would help to see the script. I reproduced it more simply than the 300 line script: postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i); postgres=# CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (9); postgres=# INSERT INTO t1 VALUES(1),(2); postgres=# ANALYZE VERBOSE t; I would say the answer lies below from above command: test_(postgres)# ANALYZE VERBOSE t; INFO: analyzing "public.t" inheritance tree INFO: "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows INFO: analyzing "public.t1" INFO: "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows ANALYZE and from here: https://www.postgresql.org/docs/10/static/sql-createtable.html "A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. ... " test_(postgres)# select * from only t; i --- (0 rows) Table t is just a pointer to the child tables and only the bulk statistics as shown in pg_statistic are maintained. postgres=# SELECT * FROM pg_stat_user_tables WHERE relname='t'; (0 rows) postgres=# SELECT 1 FROM pg_statistic WHERE starelid='t'::regclass; ?column? | 1 Justin -- Adrian Klaver adrian.kla...@aklaver.com
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 07:14 AM, Justin Pryzby wrote: I (finally) realized that my script for ANALYZEing parents of table hierarchies every month or so was looping around the same parent tables every night due to no stats for date of last last analysis. Would help to see the script. I guess that's deliberate/known and maybe related to relkind='p' having no relfilenode. Not sure that is an issue as pg_stat_user uses relid not relfilenode: https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW For reasons why they are often not the same: https://www.postgresql.org/docs/10/static/storage-file-layout.html " Caution Note that while a table's filenode often matches its OID, this is not necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER and some forms of ALTER TABLE, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs including pg_class itself, pg_class.relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using the pg_relation_filenode() function. " Is there any good workaround other than making stampfiles or making my own "last analyzed" table? Thanks, Justin -- Adrian Klaver adrian.kla...@aklaver.com
