Subject: Re: Autovacuum on partitioned table (autoanalyze)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>, yuzuko <yuzukohosoya@gmail.com>
Cc: David Steele <david@pgmasters.net>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 Justin Pryzby <pryzby@telsasoft.com>, Daniel Gustafsson <daniel@yesql.se>,
 Amit Langote <amitlangote09@gmail.com>,
 Masahiko Sawada <masahiko.sawada@2ndquadrant.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Greg Stark <stark@mit.edu>
References: <20210408032235.GA6842@alvherre.pgsql>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <9ea106b4-22fc-fac5-2451-e876572284aa@enterprisedb.com>
Date: Thu, 8 Apr 2021 12:13:51 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.1
MIME-Version: 1.0
In-Reply-To: <20210408032235.GA6842@alvherre.pgsql>
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/9ea106b4-22fc-fac5-2451-e876572284aa%40enterprisedb.com>
Precedence: bulk


On 4/8/21 5:22 AM, Alvaro Herrera wrote:
> OK, I bit the bullet and re-did the logic in the way I had proposed
> earlier in the thread: do the propagation on the collector's side, by
> sending only the list of ancestors: the collector can read the tuple
> change count by itself, to add it to each ancestor.  This seems less
> wasteful.  Attached is v16 which does it that way and seems to work
> nicely under my testing.
> 
> However, I just noticed there is a huge problem, which is that the new
> code in relation_needs_vacanalyze() is doing find_all_inheritors(), and
> we don't necessarily have a snapshot that lets us do that.  While adding
> a snapshot acquisition at that spot is a very easy fix, I hesitate to
> fix it that way, because the whole idea there seems quite wasteful: we
> have to look up, open and lock every single partition, on every single
> autovacuum iteration through the database.  That seems bad.  I'm
> inclined to think that a better idea may be to store reltuples for the
> partitioned table in pg_class.reltuples, instead of having to add up the
> reltuples of each partition.  I haven't checked if this is likely to
> break anything.
> 

How would that value get updated, for the parent?

> (Also, a minor buglet: if we do ANALYZE (col1), then ANALYZE (col2) a
> partition, then we repeatedly propagate the counts to the parent table,
> so we would cause the parent to be analyzed more times than it should.
> Sounds like we should not send the ancestor list when a column list is
> given to manual analyze.  I haven't verified this, however.)
> 

Are you sure? I haven't tried, but shouldn't this be prevented by only
sending the delta between the current and last reported value?

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company