> On Oct 28, 2020, at 9:13 PM, Justin Pryzby <pryzby@telsasoft.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@americanefficient.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
hasfor the frequency of ‘mike’, but since the frequency estimate is very accurate but the planner’s estimate is not,
there’ssomething 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