Re: VACUUM and ANALYZE disagreeing on what reltuples means

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
Cc: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: VACUUM and ANALYZE disagreeing on what reltuples means
Date: 2017-11-02 19:15:25
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> writes:
> The changes are fine and now it reports proper live tuples in both
> pg_class and stats. The other issue of continuous vacuum operation
> leading to decrease of number of live tuples is not related to this
> patch and that can be handled separately.

I did not like this patch too much, because it did nothing to fix
the underlying problem of confusion between "live tuples" and "total
tuples"; in fact, it made that worse, because now the comment on
LVRelStats.new_rel_tuples is a lie. And there's at least one usage
of that field value where I think we do want total tuples not live
tuples: where we pass it to index AM cleanup functions. Indexes
don't really care whether heap entries are live or not, only that
they're there. Plus the VACUUM VERBOSE log message that uses the
field is supposed to be reporting total tuples not live tuples.

I hacked up the patch trying to make that better, finding along the
way that contrib/pgstattuple shared in the confusion about what
it was trying to count. Results attached.

However, I'm not sure we're there yet, because there remains a fairly
nasty discrepancy even once we've gotten everyone onto the same page
about reltuples counting just live tuples: VACUUM and ANALYZE have
different definitions of what's "live". In particular they do not treat
INSERT_IN_PROGRESS and DELETE_IN_PROGRESS tuples the same. Should we
try to do something about that? If so, what? It looks like ANALYZE's
behavior is pretty tightly constrained, judging by the comments in

Another problem is that it looks like CREATE INDEX will set reltuples
to the total number of heap entries it chose to index, because that
is what IndexBuildHeapScan counts. Maybe we should adjust that?

regards, tom lane

Attachment Content-Type Size
vacuum-reltuples-fix-v3.patch text/x-diff 10.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Nico Williams 2017-11-02 19:16:13 Re: MERGE SQL Statement for PG11
Previous Message Alvaro Herrera 2017-11-02 19:11:29 Re: Re: PANIC: invalid index offnum: 186 when processing BRIN indexes in VACUUM