Message-ID: <4E5D485F.8060300@squeakycode.net>
Date: Tue, 30 Aug 2011 15:30:23 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: IN or EXISTS
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,

I have read things someplace saying not exists was better than not in... 
or something like that.  Not sure if that was for in/exists and not 
in/not exists, and for a lot of records or not.

Here is my setup:

My website has a general table, let say 60k rows.  Its mostly read-only. 
  Every once and a while we get updated data, so I:
create schema upd;
create table upd.general(like public.general);

Then I dump the new data into upd.general.  (This has many table's and 
steps, I'm simplifying it here).

For the last step, I want to:

begin;
delete from public.general where gid in (select gid from upd.general);
insert into public.general select * from upd.general;
... 7 other tables same way ...
commit;


Most of the time upd.general will be < 500 rows.  Every once and a while 
things get messed up and we just update the entire database, so count(*) 
upd.general == count(*) public.general.

My question is:
fast is nice, but safe and less resource intensive is better, so which 
would I probably like better:

delete from public.general where gid in (select gid from upd.general);

or

-- currently dont have and index, so
create index general_pk on upd.general(gid);
delete from public.general a where exists(select 1 from upd.general b 
where a.gid=b.gid);


Thanks for any suggestions,

-Andy