From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: relkind='p' has no pg_stat_user_tables |
Date: | 2018-05-03 18:15:19 |
Message-ID: | ba5ad25e-0585-aff7-3423-ec76f1f8ffb5@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2018-05-03 18:24:59 | Re: relkind='p' has no pg_stat_user_tables |
Previous Message | Adrian Klaver | 2018-05-03 18:02:00 | Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN |