From: Joel Burton <joel@joelburton.com>
To: eric soroos <eric-psql@soroos.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query for filtering records
Date: Tue, 3 Dec 2002 14:47:28 -0500
Message-ID: <20021203194728.GA917@temp.joelburton.com> (raw)
In-Reply-To: <57018518.1173209203@[4.42.179.151]>
References: <57018518.1173209203@[4.42.179.151]>
On Tue, Dec 03, 2002 at 11:01:33AM -0800, eric soroos wrote:
> I'm having trouble subtracting groups from other groups.
>
>
> I've got a data model that has the following essential features:
>
> create table contacts (num int, properties....);
> create table groups (groupNum int, contactNum int);
>
> Where not all contacts will be in a group, some groups will contain most contacts, and there will be something like hundreds of groups and tens of thousands of contacts. I allow people to build groups using criteria, which I need to programatically translate to sql.
>
> One somewhat common pattern is:
>
> Select all contacts in group a, who have property b, and who aren't in groups c,d,e,f...
>
> My first shot was subqueries:
>
> select num, p1,p2 ... from contacts
> inner join groups using (contacts.num=groups.contactNum)
> where groups.groupNum=a
> and contact.p3=b
> and not num in (select contactNum from groups where groupNum=c)
> and not num in (select contactNum from groups where groupNum=d)
> and not num in (select contactNum from groups where groupNum=e)
> and not num in (select contactNum from groups where groupNum=f)
>
> This is .... slow. agonizingly so.
I'd say so!
Something like:
SELECT * ...
FROM ...
WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or
groupnum='d' OR ... )
is bound to be _much_ faster!
And even better is
SELECT *
FROM ... contacts c1
WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or
groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum)
EXISTS is almost always faster in PG.
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
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-sql@postgresql.org
Cc: joel@joelburton.com, eric-psql@soroos.net
Subject: Re: Query for filtering records
In-Reply-To: <20021203194728.GA917@temp.joelburton.com>
* 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