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.klaver@xxxxxxxxxxx
