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 1kZg7n-000365-38 for pgsql-performance@arkaria.postgresql.org; Mon, 02 Nov 2020 20:08:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kZg7m-0003q0-2K for pgsql-performance@arkaria.postgresql.org; Mon, 02 Nov 2020 20:08:18 +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 1kZg7l-0003ps-RO for pgsql-performance@lists.postgresql.org; Mon, 02 Nov 2020 20:08:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kZg7j-00079n-M7 for pgsql-performance@postgresql.org; Mon, 02 Nov 2020 20:08:16 +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 0A2K8Com741232; Mon, 2 Nov 2020 15:08:12 -0500 From: Tom Lane To: Philip Semanchuk cc: Guillaume Lelarge , Tomas Vondra , Justin Pryzby , Michael Lewis , postgres performance list Subject: Re: Understanding bad estimate (related to FKs?) In-reply-to: <482DBA7F-1094-4768-8D45-40936E0D1F63@americanefficient.com> References: <7AC7104E-E233-4CCA-B99E-12197D61A4DD@americanefficient.com> <20201026170405.GT9241@telsasoft.com> <20201029011343.GO5380@telsasoft.com> <20201029224845.ptteqhzq65bh6jsh@development> <44E8D04E-A362-409B-AD2B-1D5860D01B61@americanefficient.com> <482DBA7F-1094-4768-8D45-40936E0D1F63@americanefficient.com> Comments: In-reply-to Philip Semanchuk message dated "Mon, 02 Nov 2020 14:09:03 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <741230.1604347692.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 02 Nov 2020 15:08:12 -0500 Message-ID: <741231.1604347692@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Philip Semanchuk writes: > The query I asked about in the original post of this thread has 13 relat= ions in it. IIUC, that's 13! or > 6 billion possible plans. How did the pl= anner pick one plan out of 6 billion? I'm curious, both for practical purp= oses (I want my query to run well) and also because it's fascinating. The twenty-thousand-foot overview is https://www.postgresql.org/docs/devel/planner-optimizer.html and then ten-thousand-foot level is the planner README file, https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob_plain;f=3Ds= rc/backend/optimizer/README;hb=3DHEAD and then you pretty much gotta start reading code. You could also dig into various planner expository talks that people have given at PG conferences. I don't have links at hand, but there have been several. regards, tom lane