From: | Philip Semanchuk <philip(at)americanefficient(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Guillaume Lelarge <guillaume(at)lelarge(dot)info>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Michael Lewis <mlewis(at)entrata(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Understanding bad estimate (related to FKs?) |
Date: | 2020-11-03 15:27:38 |
Message-ID: | E54FF53E-B7A5-4D1C-9DC2-7AF5D104E343@americanefficient.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> On Nov 2, 2020, at 10:17 PM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> On Mon, Nov 02, 2020 at 03:08:12PM -0500, Tom Lane wrote:
>> Philip Semanchuk <philip(at)americanefficient(dot)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 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.
>>
>> 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.
Thank you so much to Tomas V, Tom L, Guillaume, Justin, and Michael for all the suggestions and direction. I really appreciate your time & wisdom (not to mention your contributions to Postgres!)
Cheers
Philip
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-11-03 15:45:21 | Re: Partition pruning with joins |
Previous Message | Philip Semanchuk | 2020-11-03 15:07:24 | Re: Understanding bad estimate (related to FKs?) |