Re: Performance on inserts

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > However, assume tab2.col2 equals 3. I assume this would cause an index
> > scan because the executor doesn't know about the most common value,
> > right? Is it worth trying to improve that?
>
> Oh, I see: you are assuming that a nestloop join is being done, and
> wondering if it's worthwhile to switch dynamically between seqscan
> and indexscan for each scan of the inner relation, depending on exactly
> what value is being supplied from the outer relation for that scan.
> Hmm.
>
> Not sure if it's worth the trouble or not. Nestloop is usually a
> last-resort join strategy anyway, and is unlikely to be picked when the
> tables are large enough to make performance be a big issue.

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?

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. If 3 is the most common value in
tab1.col, it is going to get lots more than one row, right?

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