Mime-Version: 1.0
Message-Id: <57018518.1173209203@[4.42.179.151]>
Date: Tue, 03 Dec 2002 11:01:33 -0800
To: pgsql-sql@postgresql.org
From: eric soroos <eric-psql@soroos.net>
Subject: Query for filtering records
Content-Type: text/plain; charset=us-ascii

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. 

With an inner join, I'm not convinced that the subtraction is actually correct., but it is much faster. Unfortunatley, faster incorrect answers are rarely helpful. 

Outer joins seem even worse than subselects for speed, but it does appear to give the correct answer. (example with a single join.)

select num from contacts 
   left outer join groups 
        on (contacts.num=groups.contactNum
	    and  groups.groupNum=b)
   where
   dl_groupDonor._groupNum is null
   and p3=c

I've got to be missing something here, because this is much slower from the (slow) procedural system that I'm porting from. 

I've been avoiding using union / intersect since I don't really ever know what columns are going to be in the query. perhaps I should revisit that decision and try to work around it.

eric