On Mon, 2004-08-02 at 06:08, [EMAIL PROTECTED] wrote:
> We have a "companies" and a "contacts" table with about 3000 records
> each.
>
> We run the following SQL-Command which runs about 2 MINUTES !:
>
> SELECT count(*) FROM contacts LEFT JOIN companies ON contacts.sid =
> companies.intfield01
>
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
> 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)
> How can I force the usage of the indexes when using "left join". Or
> any other SQL construct th
G u i d o B a r o s i o wrote:
> TIP 9: the planner will ignore your desire to choose an index scan if your
>joining column's datatypes do not match
And this is fixed in 7.5/8.0.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (6
> [mailto:[EMAIL PROTECTED] Behalf Of
> [EMAIL PROTECTED]
> Sent: maandag 2 augustus 2004 14:09
> To: [EMAIL PROTECTED]
> Subject: [PERFORM] No index usage with "left join"
>
>
> We have a "companies" and a "contacts" table with about 3000 records
&
Cannot you do a cast in your query? Does that help with using the indexes?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
[EMAIL PROTECTED]
Sent: maandag 2 augustus 2004 14:09
To: [EMAIL PROTECTED]
Subject: [PERFORM] No index usage with "left join&
We have a "companies" and a "contacts" table with about 3000 records
each.
We run the following SQL-Command which runs about 2 MINUTES !:
SELECT count(*) FROM contacts LEFT JOIN companies ON contacts.sid =
companies.intfield01
contacts.sid (type text, b-tree index on it)
companies.intfield01 (ty
