Re: DIfferent plans for explicit versus implicit join using link table

"John D. Burger" <john(at)mitre(dot)org> writes:
> Tom Lane replied:
>> But the alternative is probably even worse: without that
>> allwordrelations has to be joined to w1 and w2 simultaneously, meaning
>> that the unconstrained cartesian product of w1 and w2 has to be formed
>> first.

> Hmm, but wouldn't it at least filter one side per my where clause:
> w1.word = 'dogging'?

Ah, right, it would do that --- but you still then have to join each of
those rows to every row of w2 before you can do the IN check, and each
of those IN checks would be an index probe into allwordrelations, which
is not that cheap. (Or at least 7.4 doesn't think so --- it does not
have any understanding about multiple index probes on the inside of a
nestloop being cheaper than single probes due to caching of the upper
index levels. You really ought to think about getting onto a newer
version; 8.2 is quite a lot smarter than 7.4.)

regards, tom lane