Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate. - Mailing list pgsql-hackers

On Fri, Mar 2, 2018 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> (1) do we really want to go over to treating ANALYZE's tuple density
> result as gospel, contradicting the entire thrust of the 2011 discussion?
>
>> This tables reltuples is 18 times the actual row count. It will never converge
>> because with 50000953 pages analyze can only adjust reltuples by 0.0006 each time.
>
> But by the same token, analyze only looked at 0.0006 of the pages.  It's
> nice that for you, that's enough to get a robust estimate of the density
> everywhere; but I have a nasty feeling that that won't hold good for
> everybody.  The whole motivation of the 2011 discussion, and the issue
> that is also seen in some other nearby discussions, is that the density
> can vary wildly.

I think that viewing ANALYZE's result as fairly authoritative is
probably a good idea.  If ANALYZE looked at only 0.0006 of the pages,
that's because we decided that 0.0006 of the pages were all it needed
to look at in order to come up with good estimates.  Having decided
that, we should turn around and decide that they are 99.94% bunk.

Now, it could be that there are data sets out there were the number of
tuples per page varies widely between different parts of the table,
and therefore sampling 0.0006 of the pages can return quite different
estimates depending on which ones we happen to pick.  However, that's
a lot like saying that 0.0006 of the pages isn't really enough, and
maybe the solution is to sample more.  Still, it doesn't seem
unreasonable to do some kind of smoothing, where we set the new
estimate = (newly computed estimate * X) + (previous estimate * (1 -
X)) where X might be 0.25 or whatever; perhaps X might even be
configurable.

One thing to keep in mind is that VACUUM will, in many workloads, tend
to scan the same parts of the table over and over again.  For example,
consider a database of chess players which is regularly updated with
new ratings information.  The records for active players will be
updated frequently, but the ratings for deceased players will rarely
change.  Living but inactive players may occasionally become active
again, or may be updated occasionally for one reason or another.  So,
VACUUM will keep scanning the pages that contain records for active
players but will rarely or never be asked to scan the pages for dead
players.  If it so happens that these different groups of players have
rows of varying width -- perhaps we store more detailed data about
newer players but don't have full records for older ones -- then the
overall tuple density estimate will come to resemble more and more the
density of the rows that are actively being updated, rather than the
overall density of the whole table.

Even if all the tuples are the same width, it might happen in some
workload that typically insert a record, update it N times, and then
it stays fixed after that.  Suppose we can fit 100 tuples into a page.
On pages were all of the records have reached their final state, there
will be 100 tuples.  But on pages where updates are still happening
there will -- after VACUUM -- be fewer than 100 tuples per page,
because some fraction of the tuples on the page were dead row
versions.  That's why we were vacuuming them.  Suppose typically half
the tuples on each page are getting removed by VACUUM.  Then, over
time, as the table grows, if only VACUUM is ever run, our estimate of
tuples per page will converge to 50, but in reality, as the table
grows, the real number is converging to 100.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company