Date: Mon, 6 Apr 2020 22:32:56 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: yuzuko <yuzukohosoya@gmail.com>
Cc: Amit Langote <amitlangote09@gmail.com>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Masahiko Sawada <masahiko.sawada@2ndquadrant.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>,
	pgsql-hackers@lists.postgresql.org, Greg Stark <stark@mit.edu>
Subject: Re: Autovacuum on partitioned table (autoanalyze)
Message-ID: <20200407033256.GG2228@telsasoft.com>
References: 
 <CAKkQ509Gn8bOEZNmcudBYj=g97qG4PcNC2pbGxC4Ci_pPtQrEQ@mail.gmail.com>
 <20200228022545.GA19686@alvherre.pgsql>
 <CA+HiwqFrLkaYW8G8hXz6XeMYUi-NHzbQ=Dn8CwEJuH+ahwbRZA@mail.gmail.com>
 <CAKkQ50-bwFEDMBGb1JmDXffXsiU8xk-hN6kJK9CKjdBa7r=Hdw@mail.gmail.com>
 <CAKkQ508nu-YVDmoDef9riW5ApoOB0RG51S1ADuFG4_yvqRnSOQ@mail.gmail.com>
 <20200318163039.GL26184@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200318163039.GL26184@telsasoft.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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