Date: Mon, 15 Nov 2021 13:09:54 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nikolay Samokhvalov <samokhvalov@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
Message-ID: <20211115190954.GO17618@telsasoft.com>
References: 
 <CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="Q/AGl/UrDvkbRExF"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211115190954.GO17618%40telsasoft.com>
Precedence: bulk


--Q/AGl/UrDvkbRExF
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Fri, Nov 12, 2021 at 02:58:07PM -0800, Nikolay Samokhvalov wrote:
> Re-reading the thread [1] (cannot answer there – don't have those emails in
> my box anymore), I see that there was strong support for enabling BUFFERS
> in EXPLAIN ANALYZE by default. And there were patches. Commitfest entry [2]
> was marked Rejected because there were questions to the implementation
> based on GUCs.

I think the only reason this isn't already the default is because of (3):

> 3. How to adjust regression tests? Of course, now many tests fail. Same
> question as for documentation. Excluding buffer, numbers would be an easier
> fix, of course – but at least some tests should
> check the behavior of BUFFERS (such as both default options – with and
> without ANALYZE).

Some time ago, I had a few relevant patches:
1) add explain(REGRESS) which is shorthand for (BUFFERS OFF, TIMING OFF, COSTS OFF, SUMMARY OFF)
2) add explain(MACHINE) which elides machine-specific output from explain;
   for example, Heap Fetches, sort spaceUsed, hash nbuckets, and tidbitmap stuff.

https://www.postgresql.org/message-id/flat/20200306213310.GM684@telsasoft.com

> 1. Should BUFFERS be enabled for regular (w/o ANALYZE) EXPLAIN? Now it may
> make sense because of the buffer numbers the planner uses. This patch
> doesn't do that, but it definitely may make sense because it can help
> people understand why planning time is so big, in some cases.

I think it *should* be enabled for planning, since that makes the default
easier to understand and document, and it makes a user's use of "explain"
easier.

However, that makes this patch series more complicated: explain(ANALYZE) is
rare in the regression tests, but this would affect the output of bare
"explain", which affects almost every test.

I think the answer to that is to add a GUC (as Tom suggested in an old thread)
like explain_regress=on, which causes explain to omit these details.  This
would be instead of my explain(REGRESS), and would change the defaults of
various params in a central place, to avoid the need to update many regression
tests.

In the future, this might also handle the stuff that my "explain(MACHINE)"
attempted to handle.

I've rebased my patches like this.  I think the explain(REGRESS) should be
re-written as a GUC which is set by regress.c.  I'm interested to hear from a
reviewer if this is worth pursing like this.

-- 
Justin

--Q/AGl/UrDvkbRExF
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0001-Add-explain-REGRESS.patch"