From: David Conlin <dc345@cantab.net>
To: pgsql-performance@lists.postgresql.org
Subject: Change in CTE treatment in query plans?
Date: Tue, 15 Oct 2019 12:28:19 +0100
Message-ID: <560a2cb6-556f-67a7-3676-dba69f2c69e6@cantab.net> (raw)
Hi folks -
Does anyone know if there's been a change in the way values for CTEs are
displayed in query plans?
I think that it used to be the case that, for keys that include the
values of child nodes values (eg "Shared Hit Blocks", or "Actual Total
Time"), CTE scans included the CTE itself, even if it wasn't included as
one of its children in the plan. If you didn't subtract the CTE scan,
you would see surprising things, like sort operations reading table
data, or the total time of the nodes in a single-threaded query plan
adding up to significantly more than 100% of the total query time.
Now (I think since v11, but I'm not sure), it looks like these values
only include the children listed in the plan. For example, I've seen CTE
scans that have smaller times and buffers values than the CTE itself,
which couldn't be true if the CTE was included in the scan.
I'm much less sure, but I *think* the same is also true of other
InitPlan nodes - for example, if a node includes the filter "value >
$1", its time and buffers used to (but no longer does) include the total
for the InitPlan node which returned the value "$1".
Am I way off base with this, or did this change happen, and if so, am I
right in thinking that it was changed in v11?
Thanks in advance
Dave
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dc345@cantab.net, pgsql-performance@lists.postgresql.org
Subject: Re: Change in CTE treatment in query plans?
In-Reply-To: <560a2cb6-556f-67a7-3676-dba69f2c69e6@cantab.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox