Обсуждение: [COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 on non-empty relationsin

Avoid having vacuum set reltuples to 0 on non-empty relations in the
presence of page pins, which leads to serious estimation errors in the
planner.  This particularly affects small heavily-accessed tables,
especially where locking (e.g. from FK constraints) forces frequent
vacuums for mxid cleanup.

Fix by keeping separate track of pages whose live tuples were actually
counted vs. pages that were only scanned for freezing purposes.  Thus,
reltuples can only be set to 0 if all pages of the relation were
actually counted.

Backpatch to all supported versions.

Per bug #14057 from Nicolas Baccelli, analyzed by me.

Discussion: https://postgr.es/m/20160331103739.8956.94469@wrigleys.postgresql.org

Branch
------
REL9_5_STABLE

Details
-------
http://git.postgresql.org/pg/commitdiff/ee78ad5bc0d2b905fdfcee997c76e98292f65fbb

Modified Files
--------------
src/backend/commands/vacuumlazy.c | 15 +++++++++++----
1 file changed, 11 insertions(+), 4 deletions(-)

On Thu, Mar 16, 2017 at 6:39 PM, Andrew Gierth
<rhodiumtoad@postgresql.org> wrote:
> Avoid having vacuum set reltuples to 0 on non-empty relations in the
> presence of page pins, which leads to serious estimation errors in the
> planner.  This particularly affects small heavily-accessed tables,
> especially where locking (e.g. from FK constraints) forces frequent
> vacuums for mxid cleanup.
>
> Fix by keeping separate track of pages whose live tuples were actually
> counted vs. pages that were only scanned for freezing purposes.  Thus,
> reltuples can only be set to 0 if all pages of the relation were
> actually counted.
>
> Backpatch to all supported versions.
>
> Per bug #14057 from Nicolas Baccelli, analyzed by me.
>
> Discussion: https://postgr.es/m/20160331103739.8956.94469@wrigleys.postgresql.org

In the department of nitpicks, we usually try to write commit messages
so that the first line is a summary line which stands alone, and then
there's a blank line, and then more follows.  a la
https://chris.beams.io/posts/git-commit/#separate

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

On 03/17/2017 09:30 AM, Robert Haas wrote:
>
> In the department of nitpicks, we usually try to write commit messages
> so that the first line is a summary line which stands alone, and then
> there's a blank line, and then more follows.  a la
> https://chris.beams.io/posts/git-commit/#separate
>


Yeah, In our case email subjects seem to be truncated at 64 chars, so
it's best to stay under that limit if possible - 50 chars is sometimes
pretty limiting.

cheers

andrew

--
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On 03/17/2017 09:30 AM, Robert Haas wrote:
>> In the department of nitpicks, we usually try to write commit messages
>> so that the first line is a summary line which stands alone, and then
>> there's a blank line, and then more follows.  a la
>> https://chris.beams.io/posts/git-commit/#separate

> Yeah, In our case email subjects seem to be truncated at 64 chars, so
> it's best to stay under that limit if possible - 50 chars is sometimes
> pretty limiting.

FWIW, I usually try to keep line length in commit messages to <= 75
characters, as that is the wrap boundary in "git log" output.  It's
nice if the summary line can be made shorter, but it's often damn
hard to cram an adequate summary into 75 chars, let alone fewer.

(Links to mailing-list threads must ignore the length limit, sadly.)

            regards, tom lane

On Fri, Mar 17, 2017 at 10:00:17AM -0400, Tom Lane wrote:
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> > On 03/17/2017 09:30 AM, Robert Haas wrote:
> >> In the department of nitpicks, we usually try to write commit messages
> >> so that the first line is a summary line which stands alone, and then
> >> there's a blank line, and then more follows.  a la
> >> https://chris.beams.io/posts/git-commit/#separate
>
> > Yeah, In our case email subjects seem to be truncated at 64 chars, so
> > it's best to stay under that limit if possible - 50 chars is sometimes
> > pretty limiting.
>
> FWIW, I usually try to keep line length in commit messages to <= 75
> characters, as that is the wrap boundary in "git log" output.  It's
> nice if the summary line can be made shorter, but it's often damn
> hard to cram an adequate summary into 75 chars, let alone fewer.
>
> (Links to mailing-list threads must ignore the length limit, sadly.)

Here are the length limits I have found, and I display this in my email
editor when I am creating a commit message:

-- email subject limit -----------------------------------------
-- gitweb summary limit --------------------------

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +