Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVZkH-000cAv-By for pgsql-general@arkaria.postgresql.org; Mon, 14 Aug 2023 15:44:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qVZkF-005pHH-Ta for pgsql-general@arkaria.postgresql.org; Mon, 14 Aug 2023 15:44:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVZkF-005pH9-IY for pgsql-general@lists.postgresql.org; Mon, 14 Aug 2023 15:44:39 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qVZkD-0006XP-Aj for pgsql-general@lists.postgresql.org; Mon, 14 Aug 2023 15:44:38 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-583b3aa4f41so46901277b3.2 for ; Mon, 14 Aug 2023 08:44:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692027876; x=1692632676; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=WxdwuEaZul0o9v/oCKsC99fEENx9wvE20V8lfoH/LgY=; b=ffO5eMT/X8oevdKCuvjDKeob0ecQxxHTxfOBHi/20uZ91EemF6ktfgUJr1iU/zJycN EZnRrYIKmQ4isJb6lT/R8Vo6+OAwCHy+Zdsht/pFbeciVIR0Q6CFbTbE8u9taYRDBZuC Z8CUri218qMdsZmBr0t0tr+FOudrhsKKqPWv0fr0WhMGXhSzYxNd66EswgmkAtDt+FJ0 BHBE0Kk0DIhsvVs/CxWuM/vL6pkLQbkRp9m5kpzb831vmd4bIEI/FvsqfLfmbfuw7PKL iv+lbT2dSEWoyq6Xq/SnQTqY73RTrjLQ+YFT32yFsVhh+KOVf2WY1AGrQDxSWqhLOPyH VX8Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692027876; x=1692632676; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=WxdwuEaZul0o9v/oCKsC99fEENx9wvE20V8lfoH/LgY=; b=Albd19bNe4VUZhODl/pWybAtfeEH38fBKg7xbEnrjDpYq6aJ/PdZxDTCPm799Xp9xV LA1/C4CsOn0AalnrXjZUeJotbkcxN6Gsz5vS9gVQ9TjEW5o/CheKJZn5pV/7mLD3vv7Z +7WOJ4/lL4Ht2we1HMAmQHU7W86ycHe1VY9DnJf3FJ4oEJJzCggEmaleLPFE5ximIQK5 pu1XF0rNePDXMY4kTnnFIwhuye/77Ec3Vf+0MoVsaBzEFKB5WxeElqpuSLsKaNeg5fJS d9FDU88dV3ayJSwm1rQDpHqNmM9VNY7sdVx9vsRMY1lHz7EUJpKUsptKyAarukkL8Dho ozTg== X-Gm-Message-State: AOJu0Yw43AKWlUJS3I8r+hGQeTycAc1xvmw2aH4yPIisYMw8omi7uLLs ULIhRNcIVlu/JfQlD3HOFum4nqymk9s= X-Google-Smtp-Source: AGHT+IGP5OghXg313PA3HriLqPgMpKunNnRcPU3xQgdDfCTVfmINSE9FrOb+4CNypAcM9jHatdilPw== X-Received: by 2002:a81:77d6:0:b0:56d:2ad0:cb45 with SMTP id s205-20020a8177d6000000b0056d2ad0cb45mr11354521ywc.1.1692027876310; Mon, 14 Aug 2023 08:44:36 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:9147:7a53:b761:df1b? ([2600:1700:bd2c:b810:9147:7a53:b761:df1b]) by smtp.googlemail.com with ESMTPSA id y6-20020a818806000000b0057736c436f1sm2831412ywf.141.2023.08.14.08.44.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 14 Aug 2023 08:44:35 -0700 (PDT) Message-ID: <30357cd7-d085-88b3-ad33-47636578911c@gmail.com> Date: Mon, 14 Aug 2023 10:44:35 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Query plan regression between CTE and views To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 8/14/23 09:54, David Gilman wrote: > I have a query that was originally written as a handful of CTEs out of > convenience. It is producing a reasonable query plan because the CTE > materialization was kicking in at an appropriate place. The CTEs > aren't totally linear. The graph looks like this, where A, B, C and D > are CTEs, and B -> A means B selects from A. In Graphviz format: > > G { > B -> A; > C -> A; > C -> B; > D -> C; > } > > Out of curiosity I tried turning the query into a series of views and > ran that query. The query plan is vastly different, there is no > materialization and it runs much slower. > > My question is: is this a valid bug? I am not sure if I should expect > the view version to find a way to materialize and produce a comparable > query plan. Also, making a minimal test case is going to take a bit > and I don't want to start unless this smells like a genuine bug. What version of Postgresql? (Also, back before, I think, v12, CTEs were optimizer fences.  You were better using views or sub-queries.) -- Born in Arizona, moved to Babylonia.