Re: Understanding bad estimate (related to FKs?)

On Mon, Nov 02, 2020 at 03:08:12PM -0500, Tom Lane wrote:
>Philip Semanchuk <philip@americanefficient.com> writes:
>> The query I asked about in the original post of this thread has 13 relations in it. IIUC, that's 13! or > 6 billion
possibleplans. How did the planner pick one plan out of 6 billion? I'm curious, both for practical purposes (I want my
queryto 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=postgresql.git;a=blob_plain;f=src/backend/optimizer/README;hb=HEAD
>
>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.
>

Yeah. The jump from high-level overviews to reading source code is a bit
brutal, though ...


FWIW a short list of relevant talks I'm aware of & would recommend:

* Explaining the Postgres Query Optimizer [Bruce Momjian]
   https://www.postgresql.org/files/developer/tour.pdf

* Intro to Postgres Planner Hacking [Melanie Plageman]
   https://www.pgcon.org/2019/schedule/events/1379.en.html

* Learning to Hack on Postgres Planner [Melanie Plageman]
   https://www.pgcon.org/2019/schedule/attachments/540_debugging_planner_pgcon2019_v4.pdf

* What’s in a Plan? [Robert Haas]
   https://www.postgresql.eu/events/pgconfeu2019/schedule/session/2741-whats-in-a-plan/
   
* A Tour of PostgreSQL Internals [Tom Lane]
   https://www.postgresql.org/files/developer/tour.pdf

* Inside thePostgreSQL Query Optimizer [Neil Conway]
   http://www.neilconway.org/talks/optimizer/optimizer.pdf

Some are a bit dated, but the overall principles don't change much.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services