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
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? |