Thread: [COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 on non-empty relationsin
[COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 on non-empty relationsin
From
Andrew Gierth
Date:
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(-)
Re: [COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 onnon-empty relations in
From
Robert Haas
Date:
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
Re: [COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 onnon-empty relations in
From
Andrew Dunstan
Date:
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
Re: [COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 on non-empty relations in
From
Tom Lane
Date:
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
Re: [COMMITTERS] pgsql: Avoid having vacuum set reltuples to 0 onnon-empty relations in
From
Bruce Momjian
Date:
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 +