Hi all !
PostgreSQL 8.4 here.
I have a simple update query that looks like this :
UPDATE t1
SET col = t2.col
FROM t2
WHERE t1.key1 = t2.key1 AND t1.key2 = t2.key2;
There is an index on (key1,key2) on the joined table (t2).
This query does not use the index.
If I rewrite it with a subselect, the index is used :
UPDATE t1
SET col = (SELECT t2.col FROM t2
WHERE t1.key1 = t2.key1
AND t1.key2 = t2.key2);
I know both queries are not exactly equivalent (I guess the second one
would throw an error if two rows were returned for the same key1,key2
pair, right?), but even though they are not equivalent, why does the
first one not use the index ?
I could provide the output from explain analyze, but the query takes
some time to run so I'll only do it if requested. Now, what is the
theorical differences between the two queries that prevent the use of
the index in the first case ?
Thanks a lot for your clarifications on this matter !
Regards
Arnaud
