Re: Performance on inserts

> 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.

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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026