Willem Leenen <willem_leenen@hotmail.com> writes:
> To me, i see a mismatch between the optimizer and the actual records
> retrieved in the fast SQL as well, so plan instability is a realistic
> scenario.
Well, the rowcount estimates for a recursive union are certainly
pretty bogus, but those are the same either way. The reason this looks
like a bug and not just statistical issues is that the join inside the
recursive union is done as a hash, even though that's much more
expensive (according to the estimates, not reality) than a nestloop.
Presumably the planner is failing to even consider a
nestloop-with-inner-indexscan join there, else it would have picked that
type of plan. Why it's failing is as yet unclear.
regards, tom lane