Gurjeet Singh wrote:
Except that it doesn't work... Did you try to execute that query; I am
assuming not.
It does, or at least a query written to work the same way works fine for
me. Not only that, but at least in the presence of a unique index the
query planner optimises it to the same query plan as the one I proposed.
From my earlier test data:
craig=# update x set val = x2.val from x as x2 where x.id = 1000 and
x2.id = 1024;
UPDATE 1
craig=# select * from x where id in (1000,1024);
id | val
------+------
1024 | 1021
1000 | 1021
(2 rows)
craig=# explain update x set val = x2.val from x as x2 where x.id = 1000
and x2.id = 1024;
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop (cost=0.00..16.55 rows=1 width=14)
-> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10)
Index Cond: (id = 1000)
-> Index Scan using x_id_idx on x x2 (cost=0.00..8.27 rows=1 width=4)
Index Cond: (x2.id = 1024)
(5 rows)
The above query actually executes slightly faster, presumably because
the query planner has to do less work to reach the same point than it
does with the subquery-based one I proposed. You should probably use
this one instead of the subquery one.
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
