Re: Performance on inserts

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.

Yes, that was my point, that a nested loop could easily be involved if
the joined table has a restriction. Is there a TODO item here?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026