Date: Mon, 2 Nov 2020 13:53:39 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Philip Semanchuk <philip@americanefficient.com>
Cc: Guillaume Lelarge <guillaume@lelarge.info>,
	Tomas Vondra <tomas.vondra@2ndquadrant.com>,
	Michael Lewis <mlewis@entrata.com>,
	pgsql-performance@postgresql.org
Subject: Re: Understanding bad estimate (related to FKs?)
Message-ID: <20201102195339.GK22691@telsasoft.com>
References: <7AC7104E-E233-4CCA-B99E-12197D61A4DD@americanefficient.com>
 <20201026170405.GT9241@telsasoft.com>
 <DF40D416-6F79-4912-9306-F012D0BED09A@americanefficient.com>
 <CAHOFxGpcN+=Vo10=2roxOOm8UXu8zstdaggRq0vvu00wcC6YjQ@mail.gmail.com>
 <20201029011343.GO5380@telsasoft.com>
 <BBBF43FB-9C74-401B-807A-C8888CD7DA40@americanefficient.com>
 <20201029224845.ptteqhzq65bh6jsh@development>
 <44E8D04E-A362-409B-AD2B-1D5860D01B61@americanefficient.com>
 <CAECtzeUcBHVv0DgazyufH2r57=gsdC-hGObKLCTRFwKFSKZezQ@mail.gmail.com>
 <482DBA7F-1094-4768-8D45-40936E0D1F63@americanefficient.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <482DBA7F-1094-4768-8D45-40936E0D1F63@americanefficient.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Mon, Nov 02, 2020 at 02:09:03PM -0500, Philip Semanchuk wrote:
> Bien merci, yes, I've visited most of those links and learned an enormous amount from them. I've downloaded many of them for re-reading, including yours. :-) It's helpful to be reminded of them again.
> 
> EXPLAIN ANALYZE tells me what choices the planner made, but it doesn't tell me why the planner made those choices. For instance, Tomas Vondra's post enabled me to calculate how the planner arrived at its estimate of 7 rows for one node of my query. I would prefer not to reverse engineer the planner's calculation, but instead have the planner just tell me. 
> 
> If I was able to combine that information with a summary of the planner's algorithm (a lot to ask for!), then I could understand how the planner chose its plan.
> 
> The query I asked about in the original post of this thread has 13 relations in it. IIUC, that's 13! or > 6 billion possible plans. How did the planner pick one plan out of 6 billion? I'm curious, both for practical purposes (I want my query to run well) and also because it's fascinating.

I hestitate to suggest it, but maybe you'd want to use 

./configure CFLAGS='-DOPTIMIZER_DEBUG=1'

which will print out costs of each plan node considered.

You could also read in selfuncs.c and costsize.c and related parts of the
source.

-- 
Justin