Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pw1A8-0004hj-Ql for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 13:44:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pw1A7-00042F-OE for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 13:44:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pw1A7-000425-Eb for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 13:44:23 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pw1A4-001D7L-UB for pgsql-general@postgresql.org; Mon, 08 May 2023 13:44:22 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 348DiIFS338273; Mon, 8 May 2023 09:44:18 -0400 From: Tom Lane To: Kent Tong cc: pgsql-general@postgresql.org Subject: Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED) In-reply-to: References: Comments: In-reply-to Kent Tong message dated "Mon, 08 May 2023 20:29:09 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <338271.1683553458.1@sss.pgh.pa.us> Date: Mon, 08 May 2023 09:44:18 -0400 Message-ID: <338272.1683553458@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Kent Tong writes: > I have a complex query involving over 15 joins and a CTE query and it takes > over 17s to complete. If you want constructive comments about that, you're going to need to show us the whole thing (and the underlying tables' schemas). Little bits in isolation are not very intelligible. https://wiki.postgresql.org/wiki/Slow_Query_Questions One thought is that you might need to raise from_collapse_limit and/or join_collapse_limit to at least 15 to get the planner to do a complete search of the join-order space. regards, tom lane