EXPLAIN BUFFERS and I/O timing accounting questions

From: Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: EXPLAIN BUFFERS and I/O timing accounting questions
Date: 2019-10-22 06:18:32
Message-ID: CAOtHd0AjLpmE1gZ35czZcGVyoje5vg+OTSRuvnL6TT7tT_BzwA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hello,

I ran across an EXPLAIN plan and had some questions about some of its
details. The BUFFERS docs say

>The number of blocks shown for an upper-level node includes those used by
all its child nodes.

I initially assumed this would be cumulative, but I realized it's probably
not because some of the blocks affected by each child will actually
overlap. But this particular plan has a Shared Hit Blocks at the root (an
Aggregate) that is smaller than some of its children (three ModifyTables
and a CTE Scan). This seems to contradict the documentation (since if
children overlap fully in their buffers usage, the parent should still have
a cost equal to the costliest child)--any idea what's up? I can send the
whole plan (attached? inline? it's ~15kb) if that helps.

I also noticed the I/O Read Time (from track_io_timing) of two children in
this plan is equal to the I/O Read Time in the root. Is I/O time
potentially fully parallelized across children? There are no parallel
workers according to the plan, so I'm surprised at this and would like to
understand better.

Also, a tangential question: why is the top-level structure of a JSON plan
an array? I've only ever seen one root node with a Plan key there.

Thanks,
Maciek

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Amarendra Konda 2019-10-22 08:57:20 Re: Too many SET TimeZone and Application_name queries
Previous Message Adrian Klaver 2019-10-22 00:24:37 Re: A question about sequences and backup/restore cycles

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2019-10-22 06:32:45 pgbench - refactor init functions with buffers
Previous Message Dilip Kumar 2019-10-22 05:31:48 Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions