Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B22C0476CA5
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 15:01:18 -0500 (EST)
Received: from temp.joelburton.com (65.eebed1.client.atlantech.net
	[209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id AEA22476D31
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 14:47:27 -0500 (EST)
Received: by temp.joelburton.com (Postfix, from userid 501)
	id 3BE7C2BA27; Tue,  3 Dec 2002 14:47:28 -0500 (EST)
Date: Tue, 3 Dec 2002 14:47:28 -0500
From: Joel Burton <joel@joelburton.com>
To: eric soroos <eric-psql@soroos.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query for filtering records
Message-ID: <20021203194728.GA917@temp.joelburton.com>
References: <57018518.1173209203@[4.42.179.151]>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <57018518.1173209203@[4.42.179.151]>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/43
X-Sequence-Number: 10631

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