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
