Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, I realize only nested loop has this problem. Mergejoin and
> Hashjoin actually would grab the whole table via sequential scan, so the
> index is not involved, right?
They'd grab the whole table after applying restriction clauses. An
indexscan might be used if there's an appropriate restriction clause
for either table, or to sort a table for merge join...
> Let me ask, if I do the query, "tab1.col = tab2.col and tab2.col = 3",
> the system would use an index to get tab2.col, but then what method
> would it use to join to tab1? Nested loop because it thinks it is going
> to get only one row from tab1.col1.
I don't think it'd think that. The optimizer is not presently smart
enough to make the transitive deduction that tab1.col = 3 (it does
recognize transitive equality of Vars, but doesn't extend that to
non-variable values). So it won't see any restriction clause for
tab1 here.
If it thinks that tab2.col = 3 will yield one row, it might well choose
a nested loop with tab2 as the outer, rather than merge or hash join.
So an inner indexscan for tab1 is definitely a possible plan.
regards, tom lane