Re: No index usage with "left join"

Rod Taylor <pg(at)rbt(dot)ca> writes:
>> How can I force the usage of the indexes when using "left join".

> Anyway, we can be fairly sure this isn't PostgreSQL 7.4 (which would
> likely choose a far better plan -- hash join rather than nested loop)

Indeed, the lack of any join-condition line in the EXPLAIN output
implies it's 7.2 or older. IIRC 7.4 is the first release that is
capable of using merge or hash join with a condition more complicated
than plain "Var = Var". In this case, since the two fields are of
different datatypes, the planner sees something like "Var = Var::text"
(ie, there's an inserted cast function). 7.2 will just say "duh, too
complicated for me" and generate a nestloop. With the columns being
of different datatypes, you don't even have a chance for an inner
indexscan in the nestloop.

In short: change the column datatypes to be the same, or update to
7.4.something. There are no other solutions.

(Well, if you were really desperate you could create a set of
mergejoinable "text op bigint" comparison operators, and then 7.2
would be able to cope; but I should think that updating to 7.4 would
be much less work.)

regards, tom lane