Message-ID: <ceb7770a-a9d0-fd70-c7d6-020155596a3f@dalibo.com>
Date: Fri, 4 Feb 2022 18:52:09 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Subject: Re: No access to TOAST tables shown in EXPLAIN ( ANALYZE, BUFFERS )
Content-Language: fr-FR
To: pgsql-docs <pgsql-docs@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
References: <eccc14bc-74f6-f7fa-1616-c31bb6aee840@dalibo.com>
 <2586583.1643295137@sss.pgh.pa.us>
From: Christophe Courtois <christophe.courtois@dalibo.com>
Organization: Dalibo
In-Reply-To: <2586583.1643295137@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/ceb7770a-a9d0-fd70-c7d6-020155596a3f%40dalibo.com>
Precedence: bulk

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.courtois@dalibo.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/