Re: Partitioning versus autovacuum

Hi Greg,

On Tue, Oct 1, 2019 at 4:03 AM Greg Stark <stark(at)mit(dot)edu> wrote:
>
> Actually -- I'm sorry to followup to myself (twice) -- but that's
> wrong. That Todo item predates the modern partitioning code. It came
> from when the partitioned statistics were added for inheritance trees.
> The resulting comment almost doesn't make sense any more since it
> talks about updates to the parent table and treats them as distinct
> from updates to the children.
>
> In any case it's actually not true any more as updates to the parent
> table aren't even tracked any more -- see below. My modest proposal is
> that we should count any updates that arrive through the parent table
> as mods for both the parent and child.

Yeah, we need to teach autovacuum to consider analyzing partitioned
tables. That is still a TODO for declarative partitioning.

We do need to weigh the trade-offs here. In the thread quoted in your
previous email, Tom expresses a concern [1] about ending up doing
excessive work, because partitions would be scanned twice -- first to
collect their own statistics and then to collect the parent's when the
parent table is analyzed. Maybe if we find a way to calculate
parent's stats from the partitions' stats without scanning the
partitions, that would be great.

Another thing to consider is that users now (as of v11) have the
option of using partitionwise plans. Consider joining two huge
partitioned tables. If they are identically partitioned, Postgres
planner considers joining pairs of matching partitions and appending
the outputs of these smaller joins. In this case, even if the
non-partitionwise join couldn't use hash join, individual smaller
joins could, because partition stats would be up to date. The
requirements that the tables being joined be identically partitioned
(or be partitioned at all) might be a bit too restrictive though.

> A more ambitious proposal would have updates to the children also
> count against the parent somehow but I'm not sure exactly how. And I'm
> not sure we shouldn't be updating the parent statistics whenever we
> run analyze on a child anyways but again I'm not sure how.

As I mentioned above, we could try to figure out a way to "merge" the
individual partitions' statistics when they're refreshed into the
parent's stats.

Thanks,
Amit

[1] https://www.postgresql.org/message-id/489.1276114285%40sss.pgh.pa.us