Re: No access to TOAST tables shown in EXPLAIN ( ANALYZE, BUFFERS )

Hi,

(redirecting to pgsql-docs, original discussion:
https://www.postgresql.org/message-id/flat/2586583.1643295137%40sss.pgh.pa.us#40d03d924838af34d61f243860ac5e01 )

Le 27/01/2022 à 15:52, Tom Lane a écrit :
> Christophe Courtois <christophe(dot)courtois(at)dalibo(dot)com> writes:
>> I've found that EXPLAIN (ANALYZE,BUFFERS) does not show any access to the 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.

So I suggest to add this case to the caveats
https://www.postgresql.org/docs/14/using-explain.html#USING-EXPLAIN-CAVEATS

"First, since no output rows are delivered to the client,
network transmission costs, I/O conversion costs
**(as TOAST tables access)**, are not included."

> 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.

OK.

> 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.

I'm afraid that auto_explain is not better in this case :-\

In the following example, auto_explain only shows the 30000 blocks
of the toast table when forced to use it,
although the whole table was dumped to a file through \o.

"*** SELECT without TOAST : short ; only heap table is read"

LOG: duration: 2.739 ms plan:

Query Text: SELECT from noises ;
Seq Scan on public.noises (cost=0.00..106.40 rows=10000 width=0) (actual time=0.006..1.668 rows=10000 loops=1)
Buffers: shared hit=64

-rw-rw-r-- 1 christ christ 19 févr. 4 14:33 /tmp/noise.txt

"*** SELECT including TOAST : long (the file is generated) but autoexplain shows only the heap!"

LOG: duration: 183.165 ms plan:

Query Text: SELECT noise from noises ;
Seq Scan on public.noises (cost=0.00..106.40 rows=10000 width=32) (actual time=0.005..1.311 rows=10000 loops=1)
Output: noise
Buffers: shared hit=64

-rw-rw-r-- 1 christ christ 24677211 févr. 4 14:33 /tmp/noise.txt

"*** SELECT with manipulated TOAST : same file and autoexplain shows all the hits"

LOG: duration: 198.416 ms plan:

Query Text: SELECT noise||'' from noises ;
Seq Scan on public.noises (cost=0.00..131.40 rows=10000 width=32) (actual time=0.034..51.882 rows=10000 loops=1)
Output: (noise || ''::text)
Buffers: shared hit=30064

-rw-rw-r-- 1 christ christ 24677211 févr. 4 14:33 /tmp/noise.txt

--
Christophe Courtois
Consultant Dalibo
https://dalibo.com/