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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to