From: David Gould <daveg@sonic.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org, Alina Alexeeva <alexeeva@adobe.com>
Cc: Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
Date: Fri, 2 Mar 2018 16:18:40 -0800
Message-ID: <20180302161840.3530921f@engels> (raw)
In-Reply-To: <3240.1519948160@sss.pgh.pa.us>
References: <20180117164916.3fdcf2e9@engels>
<fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru>
<20180228192337.67cc3f99@engels>
<4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru>
<18522.1519916974@sss.pgh.pa.us>
<1386effe-0e6d-fd5a-6f0d-f294e4ef2dda@postgrespro.ru>
<3240.1519948160@sss.pgh.pa.us>
On Thu, 01 Mar 2018 18:49:20 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The sticking point in my mind right now is, if we do that, what to do with
> VACUUM's estimates. If you believe the argument in the PDF that we'll
> necessarily overshoot reltuples in the face of declining true density,
> then it seems like that argument applies to VACUUM as well. However,
> VACUUM has the issue that we should *not* believe that it looked at a
> random sample of pages. Maybe the fact that it looks exactly at the
> changed pages causes it to see something less than the overall density,
> cancelling out the problem, but that seems kinda optimistic.
For what it's worth, I think the current estimate formula for VACUUM is
pretty reasonable. Consider a table T with N rows and P pages clustered
on serial key k. Assume reltuples is initially correct.
Then after:
delete from T where k < 0.2 * (select max k from T);
vacuum T;
Vacuum will touch the first 20% of the pages due to visibility map, the sample
will have 0 live rows, scanned pages will be 0.2 * P.
Then according to the current code:
old_density = old_rel_tuples / old_rel_pages;
new_density = scanned_tuples / scanned_pages;
multiplier = (double) scanned_pages / (double) total_pages;
updated_density = old_density + (new_density - old_density) * multiplier;
return floor(updated_density * total_pages + 0.5);
the new density will be:
N/P + (0/0.2*P - N/P) * 0.2
= N/P - N/P * 0.2
= 0.8 * N/P
New reltuples estimate will be 0.8 * old_reltuples. Which is what we wanted.
If we evenly distribute the deletes across the table:
delete from T where rand() < 0.2;
Then vacuum will scan all the pages, the sample will have 0.8 * N live rows,
scanned pages will be 1.0 * P. The new density will be
N/P + (0.8 * N/1.0*P - N/P) * 1.0
= N/P + (0.8 N/P - N/P)
= N/P - 0.2 * N/P
= 0.8 * N/P
Which again gives new reltuples as 0.8 * old_reltuples and is again correct.
I believe that given a good initial estimate of reltuples and relpages and
assuming that the pages vacuum does not scan do not change density then the
vacuum calculation does the right thing.
However, for ANALYZE the case is different.
-dg
--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
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: daveg@sonic.net, tgl@sss.pgh.pa.us, a.kuzmenkov@postgrespro.ru, alexeeva@adobe.com, lakkurra@adobe.com
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
In-Reply-To: <20180302161840.3530921f@engels>
* 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