Date: Wed, 25 Aug 2021 14:29:51 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?iso-8859-1?Q?=C1lvaro?= Herrera <alvherre@alvh.no-ip.org>
Cc: Andres Freund <andres@anarazel.de>,
	Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>,
	yuzuko <yuzukohosoya@gmail.com>,
	Tomas Vondra <tomas.vondra@enterprisedb.com>,
	David Steele <david@pgmasters.net>,
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	Daniel Gustafsson <daniel@yesql.se>,
	Amit Langote <amitlangote09@gmail.com>,
	Masahiko Sawada <masahiko.sawada@2ndquadrant.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>,
	pgsql-hackers@lists.postgresql.org, Greg Stark <stark@mit.edu>,
	Michael Paquier <michael@paquier.xyz>, Peter Geoghegan <pg@bowt.ie>,
	Andrew Dunstan <andrew@dunslane.net>
Subject: Re: Autovacuum on partitioned table (autoanalyze)
Message-ID: <20210825192951.GD10479@telsasoft.com>
References: <202108161546.3zuouk76skr4@alvherre.pgsql>
 <202108162142.crk2myigb2ad@alvherre.pgsql>
 <20210816222810.GE10479@telsasoft.com>
 <20210817113018.GG10479@telsasoft.com>
 <20210820125513.GQ10479@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20210820125513.GQ10479@telsasoft.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210825192951.GD10479%40telsasoft.com>
Precedence: bulk

On Fri, Aug 20, 2021 at 07:55:13AM -0500, Justin Pryzby wrote:
> On Tue, Aug 17, 2021 at 06:30:18AM -0500, Justin Pryzby wrote:
> > On Mon, Aug 16, 2021 at 05:28:10PM -0500, Justin Pryzby wrote:
> > > On Mon, Aug 16, 2021 at 05:42:48PM -0400, Álvaro Herrera wrote:
> > > > On 2021-Aug-16, Álvaro Herrera wrote:
> > > > 
> > > > > Here's the reversal patch for the 14 branch.  (It applies cleanly to
> > > > > master, but the unused member of PgStat_StatTabEntry needs to be
> > > > > removed and catversion bumped).
> > > > 
> > > > I have pushed this to both branches.  (I did not remove the item from
> > > > the release notes in the 14 branch.)
> > > 
> > > |    I retained the addition of relkind 'p' to tables included by
> > > |    pg_stat_user_tables, because reverting that would require a catversion
> > > |    bump.
> > > 
> > > Right now, on v15dev, it shows 0, which is misleading.
> > > Shouldn't it be null ?
> > > 
> > > analyze_count       | 0
> > > 
> > > Note that having analyze_count and last_analyze would be an an independently
> > > useful change.  Since parent tables aren't analyzed automatically, I have a
> > > script to periodically process them if they weren't processed recently.  Right
> > > now, for partitioned tables, the best I could find is to check its partitions:
> > > | MIN(last_analyzed) FROM pg_stat_all_tables psat JOIN pg_inherits i ON psat.relid=i.inhrelid
> > > 
> > > In 20200418050815.GE26953@telsasoft.com I wrote:
> > > |This patch includes partitioned tables in pg_stat_*_tables, which is great; I
> > > |complained awhile ago that they were missing [0].  It might be useful if that
> > > |part was split out into a separate 0001 patch (?).
> > > | [0] https://www.postgresql.org/message-id/20180601221428.GU5164%40telsasoft.com
> > 
> > I suggest the attached (which partially reverts the revert), to allow showing
> > correct data for analyze_count and last_analyzed.
> 
> Álvaro, would you comment on this ?
> 
> To me this could be an open item, but someone else should make that
> determination.

I added an opened item until this is discussed.
|   pg_stats includes partitioned tables, but always shows analyze_count=0
|       Owner: Alvaro Herrera

Possible solutions, in decreasing order of my own preference:

 - partially revert the revert, as proposed, to have "analyze_count" and
   "last_analyzed" work properly for partitioned tables.  This doesn't suffer
   from any of the problems that led to the revert, does it ?

 - Update the .c code to return analyze_count=NULL for partitioned tables.

 - Update the catalog definition to exclude partitioned tables, again.
   Requires a catalog bumped.

 - Document that analyze_count=NULL for partitioned tables.  It seems to just
   document a misbehavior.

-- 
Justin