Re: Understanding bad estimate (related to FKs?)

> On Oct 28, 2020, at 9:13 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
> 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.

Yes, I had the same expectation. I thought Postgres would calculate the selectivity as 1.0 * 1.0 * whatever estimate it has for the frequency of ‘mike’, but since the frequency estimate is very accurate but the planner’s estimate is not, there’s something else going on.

> 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

Yes, thank you, I read that thread with interest. I tried your clever trick using BETWEEN, but it didn’t change the plan. Does that suggest there’s some other cause for the planner’s poor estimate?

Cheers
Philip