IN or EXISTS

From: Andy Colson <andy(at)squeakycode(dot)net>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: IN or EXISTS
Date: 2011-08-30 20:30:23
Message-ID: 4E5D485F.8060300@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Craig Ringer 2011-08-31 01:33:28 Re: IN or EXISTS
Previous Message Stefan Keller 2011-08-30 17:23:10 Summaries on SSD usage?