From: | Laurette Cisneros <laurette(at)nextbus(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | efficient query help |
Date: | 2002-04-22 18:10:41 |
Message-ID: | Pine.LNX.4.44.0204221110140.22730-100000@visor.corp.nextbus.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi all,
I have crafted the following query which returns the results I want but
runs extremely slow (I'm sure it's the "not in" part that does it) since
the tables can contain large amounts of data.
Is there a better, more efficient way to build this query?
It's important that I return zero counts for id flag combinations that do not
have an entry in the log table hence the union with the "not in":
id is a text field and flgtime is a timestamp. flags is a table that contains
a single column with a row for each unique flag (text).
select id, flag, count(*) as count
from log
where date(flgtime) = '2002-04-16'
and flag is not null
group by id, flag
union
select distinct l.id, f.flag, 0 as count
from log l, flags f
where (l.id, f.flag) not in
(select id, flag
from log
where date(flgtime) = '2002-04-16'
and fag is not null)
group by l.id, f.flag
;
Thanks for any suggestions.
--
Laurette Cisneros
Database Roadie
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Where's my....bus?
From | Date | Subject | |
---|---|---|---|
Next Message | Ruben Vivas | 2002-04-22 20:05:07 | calling a pl/pgsql function with array in argument |
Previous Message | Tom Lane | 2002-04-22 17:47:25 | Re: Is this a BUG? Is there anyone has the same problem? |