pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedefficient query help
4+ messages / 3 participants
[nested] [flat]
* efficient query help
@ 2002-04-22 18:10 Laurette Cisneros <laurette@nextbus.com>
2002-04-22 21:23 ` Re: efficient query help Clinton Adams <clinton@vote-smart.org>
2002-04-24 14:06 ` Re: efficient query help Joel Burton <joel@joelburton.com>
0 siblings, 2 replies; 4+ messages in thread
From: Laurette Cisneros @ 2002-04-22 18:10 UTC (permalink / raw)
To: 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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: efficient query help
2002-04-22 18:10 efficient query help Laurette Cisneros <laurette@nextbus.com>
@ 2002-04-22 21:23 ` Clinton Adams <clinton@vote-smart.org>
1 sibling, 0 replies; 4+ messages in thread
From: Clinton Adams @ 2002-04-22 21:23 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; pgsql-sql
> 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?
Why not try NOT EXISTS? I find that NOT IN bogs down on even simple queries.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: efficient query help
2002-04-22 18:10 efficient query help Laurette Cisneros <laurette@nextbus.com>
@ 2002-04-24 14:06 ` Joel Burton <joel@joelburton.com>
2002-04-24 14:47 ` Re: efficient query help Joel Burton <joel@joelburton.com>
1 sibling, 1 reply; 4+ messages in thread
From: Joel Burton @ 2002-04-24 14:06 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; pgsql-sql
Hmmm... Is this better? Please let me know; it looks better with a small set
of sample data, but I'd be curious how it does with your real table:
select id,
flag,
sum(count)
from ( select id,
flag,
1 as count
from log
where flgtime='2002-04-16'
union all
select id,
f.flag,
0 as count
from log l,
flags f
/* don't think this is neccessary where flgtime <> '2002-04-16' */
) as t
where flag is not null
group by id, flag;
[ for others: the point of the query is to count the number of unique
id/flag combos on 4/16/2002, and union this with all possible combos of
ids/flags that aren't present on non-4/16/2002 ]
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Laurette Cisneros
> Sent: Monday, April 22, 2002 2:11 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] efficient query help
>
>
>
> 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?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: efficient query help
2002-04-22 18:10 efficient query help Laurette Cisneros <laurette@nextbus.com>
2002-04-24 14:06 ` Re: efficient query help Joel Burton <joel@joelburton.com>
@ 2002-04-24 14:47 ` Joel Burton <joel@joelburton.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joel Burton @ 2002-04-24 14:47 UTC (permalink / raw)
To: Joel Burton <joel@joelburton.com>; Laurette Cisneros <laurette@nextbus.com>; pgsql-sql
P.S. It might be faster if your change the second half of the inner query to
"SELECT DISTINCT ON(id, f.flag)" and case the numbers to int2 (assuming your
data won't overflow this); this improves things about 20% with my dataset
(flags n=26, log n=896).
By my tests, this is about 4-5x faster than your original query.
Is there anything better than this?
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
> -----Original Message-----
> From: Joel Burton [mailto:joel@joelburton.com]
> Sent: Wednesday, April 24, 2002 10:06 AM
> To: Laurette Cisneros; Pgsql-Sql@Postgresql. Org
> Subject: RE: [SQL] efficient query help
>
>
> Hmmm... Is this better? Please let me know; it looks better with
> a small set of sample data, but I'd be curious how it does with
> your real table:
>
> select id,
> flag,
> sum(count)
>
> from ( select id,
> flag,
> 1::int2 as count <- change here
> from log
> where flgtime='2002-04-16'
> and flag is not null <- change here
>
> union all
>
> select DISTINCT ON (id, f.flag) id, <- change here
> f.flag,
> 0::int2 as count <- change here
> from log l,
> flags f
>
> /* don't think this is neccessary, but might make it faster: where
flgtime <> '2002-04-16' and l.flag is not null */
>
> ) as t
>
> where flag is not null
>
> group by id, flag;
>
>
> [ for others: the point of the query is to count the number of
> unique id/flag combos on 4/16/2002, and union this with all
> possible combos of ids/flags that aren't present on non-4/16/2002 ]
>
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Laurette Cisneros
> > Sent: Monday, April 22, 2002 2:11 PM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] efficient query help
> >
> >
> >
> > 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?
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-04-24 14:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-22 18:10 efficient query help Laurette Cisneros <laurette@nextbus.com>
2002-04-22 21:23 ` Clinton Adams <clinton@vote-smart.org>
2002-04-24 14:06 ` Joel Burton <joel@joelburton.com>
2002-04-24 14:47 ` Joel Burton <joel@joelburton.com>
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