pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery for filtering records
4+ messages / 3 participants
[nested] [flat]
* Query for filtering records
@ 2002-12-03 19:01 eric soroos <eric-psql@soroos.net>
2002-12-03 19:47 ` Re: Query for filtering records Joel Burton <joel@joelburton.com>
2002-12-03 19:53 ` Re: Query for filtering records Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 4+ messages in thread
From: eric soroos @ 2002-12-03 19:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query for filtering records
2002-12-03 19:01 Query for filtering records eric soroos <eric-psql@soroos.net>
@ 2002-12-03 19:47 ` Joel Burton <joel@joelburton.com>
2002-12-03 20:52 ` Re: Query for filtering records eric soroos <eric-psql@soroos.net>
1 sibling, 1 reply; 4+ messages in thread
From: Joel Burton @ 2002-12-03 19:47 UTC (permalink / raw)
To: eric soroos <eric-psql@soroos.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query for filtering records
2002-12-03 19:01 Query for filtering records eric soroos <eric-psql@soroos.net>
2002-12-03 19:47 ` Re: Query for filtering records Joel Burton <joel@joelburton.com>
@ 2002-12-03 20:52 ` eric soroos <eric-psql@soroos.net>
0 siblings, 0 replies; 4+ messages in thread
From: eric soroos @ 2002-12-03 20:52 UTC (permalink / raw)
To: pgsql-sql
> SELECT * ...
> FROM ...
> WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or
> groupnum='d' OR ... )
>
> is bound to be _much_ faster!
Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version and some pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client specs pathological sometimes. No, they don't know that.
> 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.
Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort.
Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and believable answers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database) What's useful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more, exists clauses are really easy to integrate into my query generation routine.
thanks
eric
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query for filtering records
2002-12-03 19:01 Query for filtering records eric soroos <eric-psql@soroos.net>
@ 2002-12-03 19:53 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-12-03 19:53 UTC (permalink / raw)
To: eric soroos <eric-psql@soroos.net>; +Cc: pgsql-sql
Eric try:
select num, p1,p2 ... from contacts
inner join groups using (contacts.num=groups.contactNum)
where groups.groupNum=a
and contact.p3=b
and not exists (
select 1 from groups g2
where g2.contactNum = groups.contactNum and
g2.groupNum != a);
or
select num, p1,p2 ... from contacts
inner join groups using (contacts.num=groups.contactNum)
where groups.groupNum=a
and contact.p3=b
and groups.groupNum in (
select contactNum from groups group by contactNum having count(*) =
1);
The IN version may still be faster as the sub-select should be evaluated
only once;
JLL
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.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-12-03 20:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-03 19:01 Query for filtering records eric soroos <eric-psql@soroos.net>
2002-12-03 19:47 ` Joel Burton <joel@joelburton.com>
2002-12-03 20:52 ` eric soroos <eric-psql@soroos.net>
2002-12-03 19:53 ` Jean-Luc Lachance <jllachan@nsd.ca>
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