From: Justin Pryzby <pryzby@telsasoft.com>
To: yuzuko <yuzukohosoya@gmail.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-hackers@lists.postgresql.org, Greg Stark <stark@mit.edu>
Subject: Re: Autovacuum on partitioned table (autoanalyze)
Date: Sat, 25 Apr 2020 09:13:20 -0500
Message-ID: <20200425141320.GR28974@telsasoft.com> (raw)
In-Reply-To: <20200318163039.GL26184@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>
On Wed, Mar 18, 2020 at 11:30:39AM -0500, Justin Pryzby wrote:
> 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 happened across the thread I was referring to:
https://www.postgresql.org/message-id/7363.1426537103@sss.pgh.pa.us
I'm not opposed to doing things the currently-proposed way (trigger analyze of
partitioned tables based on updates, same as nonpartitioned tables), but we
should think if it's worth doing something totally different, like what Tom
proposed.
Robert had concerns that it would increase planning time. I imagine that
argument is even stronger now, since PG12 has *less* planning time for large
heirarchies (428b260f8) and advertizes support for "thousands" of partitions.
Tom said:
> we would automatically get statistics that account for
> partitions being eliminated by constraint exclusion, because only the
> non-eliminated partitions are present in the appendrel. And second,
That's a pretty strong benefit. I don't know if there's a good way to support
both(either) ways of doing things. Like maybe a reloption that allows
triggering autovacuum on partitioned tables, but if no statistics exist on a
partitioned table, then the planner would dynamically determine the selectivity
by decending into child statistics (Tom's way). I think the usual way this
would play out is that someone with a small partition heirarchies would
eventually complain about high planning time and then we'd suggest implementing
a manual ANALYZE job.
I'm not sure it's good to support two ways anyway, since 1) I think that gives
different (better) statistics Tom's way (due to excluding stats of excluded
partitions); 2) there's not a good way to put an ANALYZE job in place and then
get rid of parent stats (have to DELETE FROM pg_statistic WHERE
starelid='...'::regclass; 3) if someone implements an ANALYZE job, but they
disable it or it stops working then they have outdated stats forever;
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, yuzukohosoya@gmail.com, amitlangote09@gmail.com, alvherre@2ndquadrant.com, masahiko.sawada@2ndquadrant.com, laurenz.albe@cybertec.at, stark@mit.edu
Subject: Re: Autovacuum on partitioned table (autoanalyze)
In-Reply-To: <20200425141320.GR28974@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox