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
