UPDATE with JOIN not using index

From: Arnaud Lesauvage <arnaud(dot)listes(at)codata(dot)eu>
To: pgsql-general(at)postgresql(dot)org
Subject: UPDATE with JOIN not using index
Date: 2010-03-16 13:05:58
Message-ID: 4B9F8236.7010109@codata.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2010-03-16 13:49:16 Re: Text search
Previous Message Chris Roffler 2010-03-16 12:36:25 Re: Text search