From: eric soroos <eric-psql@soroos.net>
To: pgsql-sql@postgresql.org
Subject: Query for filtering records
Date: Tue, 03 Dec 2002 11:01:33 -0800
Message-ID: <57018518.1173209203@[4.42.179.151]> (raw)
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
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: eric-psql@soroos.net
Subject: Re: Query for filtering records
In-Reply-To: <57018518.1173209203@[4.42.179.151]>
* 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