From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Enable BUFFERS with EXPLAIN ANALYZE by default
Date: Wed, 11 Dec 2024 09:35:50 +0000
Message-ID: <E1tLJ8I-002ApU-7t@gemulon.postgresql.org> (raw)
Enable BUFFERS with EXPLAIN ANALYZE by default
The topic of turning EXPLAIN's BUFFERS option on with the ANALYZE option
has come up a few times over the past few years. In many ways, doing this
seems like a good idea as it may be more obvious to users why a given
query is running more slowly than they might expect. Also, from my own
(David's) personal experience, I've seen users posting to the mailing
lists with two identical plans, one slow and one fast asking why their
query is sometimes slow. In many cases, this is due to additional reads.
Having BUFFERS on by default may help reduce some of these questions, and
if not, make it more obvious to the user before they post, or save a
round-trip to the mailing list when additional I/O effort is the cause of
the slowness.
The general consensus is that we want BUFFERS on by default with
ANALYZE. However, there were more than zero concerns raised with doing
so. The primary reason against is the additional verbosity, making it
harder to read large plans. Another concern was that buffer information
isn't always useful so may not make sense to have it on by default.
It's currently December, so let's commit this to see if anyone comes
forward with a strong objection against making this change. We have over
half a year remaining in the v18 cycle where we could still easily consider
reverting this if someone were to come forward with a convincing enough
reason as to why doing this is a bad idea.
There were two patches independently submitted to achieve this goal, one
by me and the other by Guillaume. This commit is a mix of both of these
patches with some additional work done by me to adjust various
additional places in the documentation which include EXPLAIN ANALYZE
output.
Author: Guillaume Lelarge, David Rowley
Reviewed-by: Robert Haas, Greg Sabino Mullane, Michael Christofides
Discussion: https://postgr.es/m/CANNMO++W7MM8T0KyXN3ZheXXt-uLVM3aEtZd+WNfZ=obxffUiA@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/c2a4078ebad71999dd451ae7d4358be3c9290b07
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 8 +--
contrib/postgres_fdw/sql/postgres_fdw.sql | 8 +--
doc/src/sgml/bloom.sgml | 16 +++--
doc/src/sgml/jit.sgml | 5 +-
doc/src/sgml/perform.sgml | 44 ++++++++----
doc/src/sgml/planstats.sgml | 18 ++---
doc/src/sgml/ref/explain.sgml | 10 ++-
src/backend/commands/explain.c | 7 ++
src/test/regress/expected/brin_multi.out | 18 ++---
src/test/regress/expected/explain.out | 38 +++++++---
src/test/regress/expected/incremental_sort.out | 4 +-
src/test/regress/expected/memoize.out | 2 +-
src/test/regress/expected/merge.out | 2 +-
src/test/regress/expected/partition_prune.out | 96 +++++++++++++-------------
src/test/regress/expected/select.out | 2 +-
src/test/regress/expected/select_into.out | 4 +-
src/test/regress/expected/select_parallel.out | 6 +-
src/test/regress/expected/subselect.out | 2 +-
src/test/regress/expected/tidscan.out | 6 +-
src/test/regress/sql/brin_multi.sql | 18 ++---
src/test/regress/sql/explain.sql | 16 ++---
src/test/regress/sql/incremental_sort.sql | 4 +-
src/test/regress/sql/memoize.sql | 2 +-
src/test/regress/sql/merge.sql | 2 +-
src/test/regress/sql/partition_prune.sql | 96 +++++++++++++-------------
src/test/regress/sql/select.sql | 2 +-
src/test/regress/sql/select_into.sql | 4 +-
src/test/regress/sql/select_parallel.sql | 6 +-
src/test/regress/sql/subselect.sql | 2 +-
src/test/regress/sql/tidscan.sql | 6 +-
30 files changed, 257 insertions(+), 197 deletions(-)
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-committers@postgresql.org
Cc: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Enable BUFFERS with EXPLAIN ANALYZE by default
In-Reply-To: <E1tLJ8I-002ApU-7t@gemulon.postgresql.org>
* 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