From: Andy Colson <andy@squeakycode.net>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: IN or EXISTS
Date: Tue, 30 Aug 2011 15:30:23 -0500
Message-ID: <4E5D485F.8060300@squeakycode.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net
Subject: Re: IN or EXISTS
In-Reply-To: <4E5D485F.8060300@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox