From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christophe Courtois <christophe.courtois@dalibo.com>
cc: pgsql-bugs <pgsql-bugs@postgresql.org>
Subject: Re: No access to TOAST tables shown in EXPLAIN ( ANALYZE, BUFFERS )
In-reply-to: <eccc14bc-74f6-f7fa-1616-c31bb6aee840@dalibo.com>
References: <eccc14bc-74f6-f7fa-1616-c31bb6aee840@dalibo.com>
Comments: In-reply-to Christophe Courtois <christophe.courtois@dalibo.com>
	message dated "Thu, 27 Jan 2022 11:11:50 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2586582.1643295137.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 27 Jan 2022 09:52:17 -0500
Message-ID: <2586583.1643295137@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2586583.1643295137%40sss.pgh.pa.us>
Precedence: bulk

Christophe Courtois <christophe.courtois@dalibo.com> writes:
> I've found that EXPLAIN (ANALYZE,BUFFERS) does not show any access to th=
e TOAST tables when a toasted column is only SELECTed and not used in any =
way in the query.

This is probably because of the documented, long-standing behavior that
EXPLAIN does not convert the query's output rows to text, nor send them
to the client.  If the datatype output functions aren't called, the
output datums won't get detoasted either.

I don't see anything here we want to change.  The argument for not
bothering to model output costs has always been that they'd be the
same for every possible query plan, and I think that that applies to
detoasting costs as much as the actual output conversions.  Moreover,
if we're not sending data to the client, the skipped network I/O could
easily represent a larger cost than anything else --- but there's no
reasonable way to account for that.

If you do care about those costs, a possible answer is to run
auto_explain, allowing you to capture data behind-the-scenes for
queries that really are sending data to clients.

			regards, tom lane