Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1iMXdV-0007Wd-0z
	for pgsql-performance@arkaria.postgresql.org; Mon, 21 Oct 2019 13:22:13 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1iMXdR-0005w6-To
	for pgsql-performance@arkaria.postgresql.org; Mon, 21 Oct 2019 13:22:09 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <dc345@cantab.net>)
	id 1iMXdR-0005uL-IB
	for pgsql-performance@lists.postgresql.org; Mon, 21 Oct 2019 13:22:09 +0000
Received: from mta01.prd.rdg.aluminati.org ([94.76.243.214])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <dc345@cantab.net>)
	id 1iMXdP-0007IX-6U
	for pgsql-performance@lists.postgresql.org; Mon, 21 Oct 2019 13:22:08 +0000
Received: from mta01.prd.rdg.aluminati.org (localhost [127.0.0.1])
	by mta01.prd.rdg.aluminati.org (Postfix) with ESMTP id ECD5420241;
	Mon, 21 Oct 2019 14:22:05 +0100 (BST)
Received: from localhost (localhost [127.0.0.1])
	by mta01.prd.rdg.aluminati.org (Postfix) with ESMTP id E79E520087;
	Mon, 21 Oct 2019 14:22:05 +0100 (BST)
Authentication-Results: mta01.prd.rdg.aluminati.org (amavisd-new);
	dkim=pass (2048-bit key) reason="pass (just generated, assumed good)"
	header.d=cantab.net
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=cantab.net; h=
	content-language:content-transfer-encoding:in-reply-to
	:mime-version:user-agent:date:date:message-id:from:from
	:references:subject:subject:received:received; s=dkim; t=
	1571664125; bh=xbT6gMDYq8KDC+cbO3RwC2WdhjD3gKAN7tq13SuEDMQ=; b=a
	mUM5mOmrdLiHFOYFeLKYRy9CD5tKomizNY7C9RhVnEce90aXBCyLMt6azSnVmASg
	HIC15NDD/fqEkRpZLjv9SkJ8A4N7auMcEA9mjA4h69GG5ckc6cw/MbI28sDagcRc
	7nf21MoQxs5S2xz6i5I6KgLybOigq7g5DeZE06JC4+iPsFSsbNINHYDK6jHY/e9w
	iaZ33SYsRusPLcGU2fH9GzlCSRFisQJuwMmxImLl/TtvnWr/zQp6VaKu7j2yL9iD
	D3qnsrmM30rDqWaZ7L93+rd+BBxxNf+mbNzy9QFYFMlH6/EzjnCR9eMhA8AH/sip
	Tb1lknJQr+SeDGBwWi/ww==
X-Quarantine-ID: <f9xyKxJRHjht>
X-Virus-Scanned: Debian amavisd-new at mta01.prd.rdg.aluminati.org
Received: from mta.aluminati.local ([127.0.0.1])
	by localhost (mta01.prd.rdg.aluminati.org [127.0.0.1]) (amavisd-new,
 port 10026)
	with ESMTP id f9xyKxJRHjht; Mon, 21 Oct 2019 14:22:05 +0100 (BST)
Received: from [192.168.1.54]
 (cpc148252-chap9-2-0-cust661.18-1.cable.virginm.net [92.233.230.150])
	by mta01.prd.rdg.aluminati.org (Postfix) with ESMTPSA id 96DCA5F866;
	Mon, 21 Oct 2019 14:22:02 +0100 (BST)
Subject: Re: Change in CTE treatment in query plans?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
References: <560a2cb6-556f-67a7-3676-dba69f2c69e6@cantab.net>
 <19441.1571303072@sss.pgh.pa.us>
From: David Conlin <dc345@cantab.net>
Message-ID: <a2e0867f-ef98-471a-c2bf-2184d304f76c@cantab.net>
Date: Mon, 21 Oct 2019 14:22:01 +0100
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: <19441.1571303072@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-GB
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

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