Thread: Temporary files usage in explain
Hi everybody,
I have a query that creates a large number of temporary files, in this example ~ 45GB.
Looking at the query plan I can't figure out where temporary files are being generated.
Explain plan: https://explain.depesz.com/s/BXGT
Usually I'm looking for "external merge Disk" to see temp files usage but, in this case, the only reference to that is 299,368kB in the last but one node of explain.
Can anyone help me understand where the 45GB are in that explain?
Thanks,
Matteo
On Tue, 2021-04-27 at 11:15 +0200, Matteo Bonardi wrote: > I have a query that creates a large number of temporary files, in this example ~ 45GB. > Looking at the query plan I can't figure out where temporary files are being generated. > > Explain plan: https://explain.depesz.com/s/BXGT > > Usually I'm looking for "external merge Disk" to see temp files usage but, in this case, the only reference to that is299,368kB in the last but one node of explain. > Can anyone help me understand where the 45GB are in that explain? I see at first glance: Sort Method: external merge Disk: 299,368kB To see all, use EXPLAIN (ANALYZE, BUFFERS). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On Tue, 27 Apr 2021 at 21:15, Matteo Bonardi <mbona92@gmail.com> wrote: > Explain plan: https://explain.depesz.com/s/BXGT > > Usually I'm looking for "external merge Disk" to see temp files usage but, in this case, the only reference to that is299,368kB in the last but one node of explain. > Can anyone help me understand where the 45GB are in that explain? If you do EXPLAIN (ANALYZE, BUFFERS) you might find that the recursive CTE is writing the rest of them out. Something like "temp written = ", likely around 5 million or so, I imagine. David
Okay, I had a misconception of the buffers option.
As I reread the documentation, I realized how stupid the question was.
I confirm that with the buffers option I now see:
Buffers: shared hit=9617011 read=1328356 dirtied=793 written=397, temp read=2996659 written=5956399
Thank you both,
Matteo
Il giorno mar 27 apr 2021 alle ore 11:38 David Rowley <dgrowleyml@gmail.com> ha scritto:
On Tue, 27 Apr 2021 at 21:15, Matteo Bonardi <mbona92@gmail.com> wrote:
> Explain plan: https://explain.depesz.com/s/BXGT
>
> Usually I'm looking for "external merge Disk" to see temp files usage but, in this case, the only reference to that is 299,368kB in the last but one node of explain.
> Can anyone help me understand where the 45GB are in that explain?
If you do EXPLAIN (ANALYZE, BUFFERS) you might find that the recursive
CTE is writing the rest of them out. Something like "temp written =
", likely around 5 million or so, I imagine.
David