Re: Autovacuum on partitioned table (autoanalyze) - Mailing list pgsql-hackers

Not sure if you saw my earlier message ?

I think it ought to be possible to configure this feature such that an
auto-analyze on any child partition would trigger analyze of the parent.  I
think that would be important for maintaining accurate stats of the partition
key column for many cases involving RANGE-partitioned tables, which are likely
to rely on histogram rather than MCVs.

On Wed, Mar 18, 2020 at 11:30:39AM -0500, Justin Pryzby wrote:
> Regarding this patch:
> 
> +     * the ANALYZE message as it resets the partition's changes_since_analze
> => analyze
> 
> +     * If the relation is a partitioned table, we must add up children's
> childrens'
> 
> The approach in general:
> 
> I see an issue for timeseries data, where only the most recent partition is
> being inserted into, and the histogram endpoint is being continuously extended
> (this is our use-case).  The most recent partition will be analyzed pretty
> often, and I think it'll be problematic if its parent doesn't get similar
> treatment.  Let's say there are 12 historic, monthly children with 1e6 tuples
> each, and the 13th child has 2e5 tuples (6 days into the month).  It's analyzed
> when it grows by 20% (1.2 days), but at that point the parent has only grown by
> 12x less (~2%) and won't be analyzed until 12x further into the future (14
> days).  Its histogram is 12x longer (geometrically), but the histogram changed
> by just as much (arithmetically).  That's an issue for a query over "the last
> few days"; if that's past the end of the histogram bound, the query planner
> will estimate about ~0 tuples, and tend to give cascades of nested loops.  I'm
> biased, but I'm guessing that's too common a use case to answer that the proper
> fix is to set the parent's analyze_scale_factor=0.0005.  I think that suggests
> that the parent might sometimes need to be analyzed every time any of its
> children are.  In other cases (like probably any hash partitioning), that'd be
> excessive, and maybe the default settings shouldn't do that, but I think that
> behavior ought to be possible, and I think this patch doesn't allow that.  
> 
> In the past, I think there's was talk that maybe someone would invent a clever
> way to dynamically combine all the partitions' statistics, so analyzing the
> parent wasn't needed.  I think that's easy enough for reltuples, MCV, and I
> think histogram, but ISTM that ndistinct is simultaneously important to get
> right and hard to do so.  It depends on whether it's the partition key, which
> now can be an arbitrary expression.  Extended stats further complicates it,
> even if we didn't aim to dynamically compute extended stats for a parent.
> 
> While writing this, it occured to me that we could use "CREATE STATISTICS" as a
> way to mark a partitioned table (or certain columns) as needing to be handled
> by analyze.  I understand "CREATE STATs" was intended to (eventually) allow
> implementing stats on expressions without using "create index" as a hack.  So
> if it's excessive to automatically analyze a parent table when any of its
> children are analyzed, maybe it's less excessive to only do that for parents
> with a stats object, and only on the given colums.  I realize this patch is
> alot less useful if it requires to do anything extra/nondefault, and it's
> desirable to work without creating a stats object at all.  Also, using CREATE
> STATs would reduces the CPU cost of re-analyzing the entire heirarchy, but
> doesn't help to reduce the I/O cost, which is significant.
> 
> -- 
> Justin

-- 
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581