X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C08929FB38B for ; Tue, 12 Sep 2006 15:59:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84344-04-3 for ; Tue, 12 Sep 2006 15:57:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id 03DBB9FB26B for ; Tue, 12 Sep 2006 15:47:04 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2751480pyd for ; Tue, 12 Sep 2006 11:46:31 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=nRygn6Ug1iuLdIiOYxRky9gQX2o2ACYcDPqLN0DTs4YVFjMHLI52FH9OSafhwhrxOmWX1urRCVmD5HEsE6r+HxSzL96W+HgdTT6mng7A3VNFG7HudyZMTajC7+2nWeG/2+mYm69lrdwTIFpWJOvM80mzcUAme1/mNSVTCu4QhRU= Received: by 10.35.37.13 with SMTP id p13mr11384703pyj; Tue, 12 Sep 2006 11:46:30 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Tue, 12 Sep 2006 11:46:30 -0700 (PDT) Message-ID: Date: Tue, 12 Sep 2006 13:46:30 -0500 From: "Aaron Bono" To: "=?ISO-8859-1?Q?Mezei_Zolt=E1n?=" Subject: Re: Two optimization questions Cc: pgsql-sql@postgresql.org In-Reply-To: <45068ABE.8040907@telefor.hu> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5059_15143696.1158086790233" References: <45068ABE.8040907@telefor.hu> X-Google-Sender-Auth: ad0134be77320542 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.519 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/84 X-Sequence-Number: 26295 ------=_Part_5059_15143696.1158086790233 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 9/12/06, Mezei Zolt=E1n wrote: > > Hi, > > I think it can be done better than I did and I want to learn... > > > 1. I have a table that registers the history of messages: > output_message_history(id, event_type, event_time) > I need those ID-s from the table where there is one 'MESSAGE SENT' event > and one 'MESSAGE SUBMITTED' event and there are no more events on that > message. > > select id > from output_message_history > group by content_id > having > sum(case when event_type=3D'MESSAGE SENT' then 1 else 0 end) =3D 1 > and > sum(case when event_type=3D'MESSAGE SUBMITTED' then 1 else 0 end) =3D 1 > and count(*) =3D 2 > > Can it be done another, more optimal way? You could split it into sub-queries but would that make the performance better or worse? I guess it depends on how much data is there, and what frequency you have ot the event_type's but indexing the event_type column would help. This may be worth a try - use EXPLAIN to see which is better. SELECT id FROM output_message_history WHERE NOT content_id IN ( SELECT content_id -- distinct(content_id) FROM output_message_history WHERE NOT event_type IN ('MESSAGE SENT', 'MESSAGE SUBMITTED') ) GROUP BY content_id HAVING sum(case when event_type=3D'MESSAGE SENT' then 1 else 0 end) =3D 1 AND sum(case when event_type=3D'MESSAGE SUBMITTED' then 1 else 0 end) =3D 1 2. I have to create reports like this one: > > time count > 12:00 3 > 12:01 2 > 12:02 4 > 12:03 0 <--- now this one is problematic for me > 12:04 5 > > So I need something like this: > > select date_trunc('minute', crd), count(*) from subscriber > where crd between '2006-09-08' and '2006-09-12' > group by date_trunc('minute', crd) > > But the output of this query won't show minutes with 0 count. I searched > the archives and found an example using a view to solve this problem, > but creating such a view for grouping by minutes or seconds doesn't seem > to be nice solution. Any ideas how to solve this problem? Questions like this come up frequently and there are some nice solutions: See if this gives you some insight: select '2006-01-15'::date + s.inc from generate_series(0, ('2006-02-20'::date - '2006-01-15'::date)::integer) as s(inc) See http://www.postgresql.org/docs/8.1/static/functions-srf.html and http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html for more. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ------=_Part_5059_15143696.1158086790233 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 9/12/06, Mezei Zolt=E1n <mezei.zoltan@telefor.hu> wrote:
Hi,

I think it can be done better than I did and I want to learn...<= br>

1. I have a table that registers the history of messages:
out= put_message_history(id, event_type, event_time)
I need those ID-s from t= he table where there is one 'MESSAGE SENT' event
and one 'MESSAGE SUBMITTED' event and there are no more events on that<= br>message.

select id
from output_message_history
group by con= tent_id
having
sum(case when event_type=3D'MESSAGE SENT' then 1 else = 0 end) =3D 1
and
sum(case when event_type=3D'MESSAGE SUBMITTED' then 1 else 0 end= ) =3D 1
and count(*) =3D 2

Can it be done another, more optimal w= ay?

You could split it into sub-queries but would that= make the performance better or worse?  I guess it depends on how much= data is there, and what frequency you have ot the event_type's but indexin= g the event_type column would help.  This may be worth a try - use EXP= LAIN to see which is better.

SELECT id
FROM output_message_history
WHERE NOT content_id IN= (
   SELECT content_id -- distinct(content_id)
  = ; FROM output_message_history
   WHERE NOT event_type IN ('MES= SAGE SENT', 'MESSAGE SUBMITTED')
)
GROUP BY content_id
HAVING
   sum(case when event_= type=3D'MESSAGE SENT' then 1 else 0 end) =3D 1
   AND
 = ;  sum(case when event_type=3D'MESSAGE SUBMITTED' then 1 else 0 end) = =3D 1

http://www.postgresql.org/docs/8.1/static/functions-srf.html = and http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html = for more.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ------=_Part_5059_15143696.1158086790233--