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 ) id 1gnkP3-0005I9-Re for pgsql-hackers@arkaria.postgresql.org; Sun, 27 Jan 2019 13:23:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gnkP1-0006iJ-5C for pgsql-hackers@arkaria.postgresql.org; Sun, 27 Jan 2019 13:23:11 +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 ) id 1gnkP0-0006iC-U7 for pgsql-hackers@lists.postgresql.org; Sun, 27 Jan 2019 13:23:10 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gnkOu-0004mi-Jm for pgsql-hackers@postgresql.org; Sun, 27 Jan 2019 13:23:10 +0000 Received: from [192.168.127.1] (port=14804 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.88 (FreeBSD)) (envelope-from ) id 1gnkOp-0000Ry-17; Sun, 27 Jan 2019 13:22:59 +0000 Received: from [127.0.0.1] (port=64755 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.89 (FreeBSD)) (envelope-from ) id 1gnkOo-0007t6-7u; Sun, 27 Jan 2019 13:22:58 +0000 From: Andrew Gierth To: Tom Lane Cc: Andreas Karlsson , David Fetter , Thomas Munro , Pg Hackers Subject: Re: Early WIP/PoC for inlining CTEs In-Reply-To: <31065.1548543317@sss.pgh.pa.us> (Tom Lane's message of "Sat, 26 Jan 2019 17:55:17 -0500") Message-ID: <87fttejl62.fsf@news-spur.riddles.org.uk> References: <87sh48ffhb.fsf@news-spur.riddles.org.uk> <20180725054236.GB17411@fetter.org> <87bmavfjhy.fsf@news-spur.riddles.org.uk> <20180725191447.GE17411@fetter.org> <20180727081058.GN17411@fetter.org> <8810.1542402910@sss.pgh.pa.us> <87r2dxrw1z.fsf@news-spur.riddles.org.uk> <29ce1707-0dae-ad33-8ab0-7d5226c7dfd3@proxel.se> <11140.1548454956@sss.pgh.pa.us> <31065.1548543317@sss.pgh.pa.us> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix) Date: Sun, 27 Jan 2019 13:22:57 +0000 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Tom" == Tom Lane writes: Tom> I was interested to find, while writing the docs, that it's a real Tom> struggle to invent plausible reasons to write MATERIALIZED given Tom> the above specification. You pretty much have to have lied to the Tom> planner, eg by making a volatile function that's not marked Tom> volatile, before there's a real need for that. Am I missing Tom> something? If I'm not, then we're in a really good place Tom> backwards-compatibility-wise, because the new default behavior Tom> shouldn't break any cases where people weren't cheating. The cases where the new default will break things are where people used WITH to force a choice of join order or otherwise constrain the planner in order to avoid a misplan. I'm not sure we should nail down the rule that the absence of NOT MATERIALIZED will mean a multiply-referenced CTE is evaluated once. One would hope that in the future the planner might be taught to inline or not in that case depending on cost. I think it makes more sense to say that we never inline if MATERIALIZED is specified, that we always inline if NOT MATERIALIZED is specified, and that if neither is specified the planner will choose (but perhaps note that currently it always chooses only based on refcount). -- Andrew (irc:RhodiumToad)