Re: No index usage with "left join" - Mailing list pgsql-performance

> SELECT count(*) FROM contacts LEFT JOIN companies ON contacts.sid =
> companies.intfield01
>
> contacts.sid (type text, b-tree index on it)
> companies.intfield01 (type bigint, b-tree index on it)
<snip>
> How can I force the usage of the indexes when using "left join". Or
> any other SQL construct that does the same !? Can anybody please give
> us a hint !?

You really don't need to use indexes since you're fetching all
information from both tables.

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) as
it won't join a bigint to a text field without a cast.

Try this:
    set enable_nestloop = false;
        SELECT count(*) FROM contacts LEFT JOIN companies ON
        cast(contacts.sid as bigint) = companies.intfield01;
    set enable_nestloop = true;