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.court...@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/
