> On Oct 28, 2020, at 9:13 PM, Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxx> 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
