| From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
|---|---|
| To: | Michael Lewis <mlewis(at)entrata(dot)com> |
| Cc: | Philip Semanchuk <philip(at)americanefficient(dot)com>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Understanding bad estimate (related to FKs?) |
| Date: | 2020-10-29 01:13:43 |
| Message-ID: | 20201029011343.GO5380@telsasoft.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Mon, Oct 26, 2020 at 11:20:01AM -0600, Michael Lewis wrote:
> On Mon, Oct 26, 2020 at 11:14 AM Philip Semanchuk <philip(at)americanefficient(dot)com> wrote:
>
> > >> The item I'm focused on is node 23. The estimate is for 7 rows, actual
> > is 896 (multiplied by 1062 loops). I'm confused about two things in this
> > node.
> > >>
> > >> The first is Postgres' estimate. The condition for this index scan
> > contains three expressions --
> > >>
> > >> (five_uniform = zulu_five.five_uniform) AND
> > >> (whiskey_mike = juliet_india.whiskey_mike) AND
> > >> (bravo = 'mike'::text)
> >
>
> Are the columns correlated?
I guess it shouldn't matter, since the FKs should remove all but one of the
conditions.
Maybe you saw this other thread, which I tentatively think also affects your
case (equijoin with nonjoin condition)
https://www.postgresql.org/message-id/AM6PR02MB5287A0ADD936C1FA80973E72AB190%40AM6PR02MB5287.eurprd02.prod.outlook.com
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ehrenreich, Sigrid | 2020-10-29 06:31:50 | RE: Postgres Optimizer ignores information about foreign key relationship, severly misestimating number of returned rows in join |
| Previous Message | Olivier Poquet | 2020-10-29 00:53:09 | Re: query plan using partial index expects a much larger number of rows than is possible |