and the reason that it is awful is that the planner is not aware of the 100% correlation between the contents of the tables, specifically the fact that the v1 and v2 join conditions add precisely zero selectivity beyond the id join condition.
Yeah, and the planner thinks the three join conditions have independent probabilities and estimates them by taking the product of their selectivities.
BTW, the fact that it isn't noticing the marginally-cheaper-estimated- cost plan isn't a bug either. We intentionally treat plans of essentially equal cost as redundant and keep only one, in order to reduce planning time. I've not traced through this example in detail, but I'm sure that some pruning step of that kind eliminated consideration of the slightly cheaper plan. The fact that in reality it's a *lot* cheaper escapes the planner because of the poor selectivity estimates.
Right. The planner does fuzzy cost comparison with a factor STD_FUZZ_FACTOR. So they are fuzzily the same on total cost, while the second plan is fuzzily worse on startup.
