pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedChange in CTE treatment in query plans?
3+ messages / 2 participants
[nested] [flat]
* Change in CTE treatment in query plans?
@ 2019-10-15 11:28 David Conlin <dc345@cantab.net>
0 siblings, 1 reply; 3+ messages in thread
From: David Conlin @ 2019-10-15 11:28 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Change in CTE treatment in query plans?
@ 2019-10-17 09:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Conlin <dc345@cantab.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2019-10-17 09:04 UTC (permalink / raw)
To: David Conlin <dc345@cantab.net>; +Cc: pgsql-performance@lists.postgresql.org
David Conlin <dc345@cantab.net> writes:
> Does anyone know if there's been a change in the way values for CTEs are
> displayed in query plans?
Offhand I don't recall any such changes, nor does a cursory look
through explain.c find anything promising.
If you're concerned with a multiply-referenced CTE, one possibility
for funny results is that the blame for its execution cost could be
spread across the multiple call sites. The same can happen with
initplans/subplans. But I'm just guessing; you didn't show any
concrete examples so it's hard to be definite.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Change in CTE treatment in query plans?
@ 2019-10-21 13:22 David Conlin <dc345@cantab.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: David Conlin @ 2019-10-21 13:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Hi Tom -
Thanks so much for getting back to me.
I didn't realise that the costs of init/sub plans would be spread across
the call sites - I had (stupidly) assumed that each call site would
include the full cost.
Having taken a couple of days to go back over the problems I was seeing,
you were absolutely right - it was all to do with multiple call sites -
the postgres version was just a red herring.
Thanks for your help & all the best,
Dave
On 17/10/2019 10:04, Tom Lane wrote:
> David Conlin <dc345@cantab.net> writes:
>> Does anyone know if there's been a change in the way values for CTEs are
>> displayed in query plans?
> Offhand I don't recall any such changes, nor does a cursory look
> through explain.c find anything promising.
>
> If you're concerned with a multiply-referenced CTE, one possibility
> for funny results is that the blame for its execution cost could be
> spread across the multiple call sites. The same can happen with
> initplans/subplans. But I'm just guessing; you didn't show any
> concrete examples so it's hard to be definite.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-10-21 13:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-15 11:28 Change in CTE treatment in query plans? David Conlin <dc345@cantab.net>
2019-10-17 09:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-10-21 13:22 ` David Conlin <dc345@cantab.net>
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