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
