pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joel Burton <joel@joelburton.com>
To: Joel Burton <joel@joelburton.com>
To: Laurette Cisneros <laurette@nextbus.com>
To: Pgsql-Sql@Postgresql. Org <pgsql-sql@postgresql.org>
Subject: Re: efficient query help
Date: Wed, 24 Apr 2002 10:47:12 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNEEAICLAA.joel@joelburton.com> (raw)
In-Reply-To: <JGEPJNMCKODMDHGOBKDNKEAGCLAA.joel@joelburton.com>

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
> >




view thread (4+ messages)

Message-ID: <JGEPJNMCKODMDHGOBKDNEEAICLAA.joel@joelburton.com>
Permalink:  ../JGEPJNMCKODMDHGOBKDNEEAICLAA.joel@joelburton.com/
Also on:    postgresql.org/message-id/JGEPJNMCKODMDHGOBKDNEEAICLAA.joel@joelburton.com

 · 

reply

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: joel@joelburton.com, laurette@nextbus.com
  Subject: Re: efficient query help
  In-Reply-To: <JGEPJNMCKODMDHGOBKDNEEAICLAA.joel@joelburton.com>

* 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