From: Justin Pryzby <pryzby@telsasoft.com>
To: Philip Semanchuk <philip@americanefficient.com>
Cc: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Understanding bad estimate (related to FKs?)
Date: Mon, 2 Nov 2020 13:53:39 -0600
Message-ID: <20201102195339.GK22691@telsasoft.com> (raw)
In-Reply-To: <482DBA7F-1094-4768-8D45-40936E0D1F63@americanefficient.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, philip@americanefficient.com, guillaume@lelarge.info, tomas.vondra@2ndquadrant.com, mlewis@entrata.com
Subject: Re: Understanding bad estimate (related to FKs?)
In-Reply-To: <20201102195339.GK22691@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox