Message-ID: <47E26A42.5050106@postnewspapers.com.au>
Date: Thu, 20 Mar 2008 22:44:34 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Gurjeet Singh <singh.gurjeet@gmail.com>
Cc: Joe <dev@freedomcircle.net>,
 Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>,
	pgsql-sql@postgresql.org
Subject: Re: Select into
References: <94F93C0F-DA95-4435-B253-57C90961DF34@eclinic.com.au>	
	<20080320104922.GB18935@a-kretschmer.de>	
	<65937bea0803200358i290bdc87hdeca777b593b5df1@mail.gmail.com>	
	<786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au>	
	<47E25447.6040109@freedomcircle.net>
	<65937bea0803200613k4593da12hd62233f8a745309@mail.gmail.com>
In-Reply-To: <65937bea0803200613k4593da12hd62233f8a745309@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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