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

Tom Lane replied:

>> I have two queries for looking up related words which I think should
>> be equivalent, but 7.4.8 comes up with very different plans.
>
> They're not at all equivalent:

> If there are duplicate word1id,word2id entries in allwordrelations,
> the
> first query will produce duplicate outputs; the second will not.

Ah, that should have been my second guess - whenever I fail to get
stuff like this, it's usually to do with either duplicates or NULLs.

> If there were a unique constraint on (word1id, word2id), in theory
> the planner could prove that the IN form could be simplified to a
> plain
> join, but there is no such logic in HEAD let alone 7.4, and in any
> case
> you've not got such a constraint.

But such would reflect the reality of my data, so it should be there.

> The plan that gets chosen is to forcibly unique-ify the (word1id,
> word2id) data (via a "sort | uniq"-like pipeline) and then do a normal
> join with that. Which is expensive because allwordrelations is big.
> 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'? Anyway, thanks, the incremental enlightenment
continues.

- John Burger
MITRE