I wrote:
> It's still a bit odd that the case with two batteryidentifiers was
> estimated fairly accurately when the other wasn't; I'll go look into
> that.
For the sake of the archives: I looked into this, and it seems there's
not anything going wrong other than the bogusly small n_distinct for
observationresults.
I'm assuming that battery.batteryidentifier is unique (stop me here,
Tim, if not). That means that (a) there won't be any most-common-values
statistics list for it, and (b) the n_distinct estimate should be pretty
accurate.
What happens in the multiple-batteryidentifier case is that eqjoinsel()
doesn't have two MCV lists to work with, and so it bases its selectivity
estimate on the larger n_distinct, which in this case is the accurate
value from the battery table. So we come out with a decent estimate
even though the other n_distinct is all wrong.
What happens in the single-batteryidentifier case is that transitive
equality deduction removes the battery.batteryidentifier =
observationresults.batteryidentifier join condition altogether,
replacing it with two restriction conditions batteryidentifier = 1177470.
So eqjoinsel() is never called, and the join size estimate is just the
product of the indexscan size estimates, and the scan estimate for
observationresults is too high because its n_distinct is too small.
So the bottom line is that eqjoinsel() is actually a bit more robust
than one might have thought ...
regards, tom lane