"Looby, Denis" <denis.looby@hp.com> writes:
> What I don't understand is why the plan is different just because the group_id = has changed value?
> Does the planner have some statistical info on the contents of non-indexed rows?
Of course. In this case it knows that a nestloop would be a loser
because many rows would be fetched from the outer table.
> I don't quite understand why this plan executes the sequential scan once, whereas the slow one does it 5001 times,
whichI believe is the main source of the difference.
In the hash join case, it builds the hash table, discovers that it's
empty (because there are no rows with type = 2), and concludes that it
need not scan the outer relation. This is a corner case that the
planner doesn't try to account for because it couldn't be predicted
reliably. I wouldn't get too excited about it if I were you, because
presumably the case isn't going to happen all that much in production
either (else why are you bothering with the query?).
Personally I'd suggest getting rid of the
WHERE outer_tab.outer_key = inner_tab_1.key_to
clause, which is entirely redundant with the IN condition and is
preventing the planner from seeing that it could optimize the IN
into a semijoin.
regards, tom lane