Rod Taylor <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings