Horton

Re: Berserk Autovacuum (let's save next Mandrill)

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, Andres Freund <andres@anarazel.de>, Alexander Korotkov <a.korotkov@postgrespro.ru>, Justin Pryzby <pryzby@telsasoft.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Masahiko Sawada <masahiko.sawada@2ndquadrant.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Darafei Komяpa Praliaskouski <me@komzpa.net>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Michael Banck <mbanck@gmx.net>
Date: 2020-04-02T15:46:36Z
Lists: pgsql-hackers

Attachments

I wrote:
> I'd be inclined to undo what you did in favor of initializing the
> test tables to contain significantly different numbers of rows,
> because that would (a) achieve plan stability more directly,
> and (b) demonstrate that the planner is actually ordering the
> tables by cost correctly.  Maybe somewhere else we have a test
> that is verifying (b), but these test cases abysmally fail to
> check that point.

Concretely, I suggest the attached, which replaces the autovac disables
with adjusting partition boundaries so that the partitions contain
different numbers of rows.

I did not touch the partition boundaries for pagg_tab1 and pagg_tab2,
because that would have required also changing the associated test
queries (which are designed to access only particular partitions).
It seemed like too much work to verify that the answers were still
right, and it's not really necessary because those tables are so
small as to fit in single pages.  That means that autovac will either
see the whole table or none of it, and in either case it won't change
reltuples.

This does cause the order of partitions to change in a couple of the
pagg_tab_ml EXPLAIN results, but I think that's fine; the ordering
does now match the actual sizes of the partitions.

			regards, tom lane

Commits

  1. Further improve stability fix for partition_aggregate test.

  2. Improve stability fix for partition_aggregate test.

  3. Attempt to stabilize partitionwise_aggregate test

  4. Fix race condition in statext_store().

  5. Attempt to fix unstable regression tests, take 2

  6. Attempt to fix unstable regression tests

  7. Trigger autovacuum based on number of INSERTs

  8. Fix upper limit for vacuum_cleanup_index_scale_factor