X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 79CC99FD16E for ; Mon, 28 Aug 2006 16:23:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 68729-02 for ; Mon, 28 Aug 2006 19:23:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31815.mail.mud.yahoo.com (web31815.mail.mud.yahoo.com [68.142.206.168]) by postgresql.org (Postfix) with SMTP id CF3F29FD16C for ; Mon, 28 Aug 2006 16:23:08 -0300 (ADT) Received: (qmail 54204 invoked by uid 60001); 28 Aug 2006 19:23:07 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=BWozsT7OJ9GFtp5IFznEq+/BXvUBv5PzMQBLpK4MP4bxNa67JXf3cGRQ/0vEGrKqXqRdjVSs06+3VOVA/DuEQgTyCj6VUxAzR1ZsQhv8B19mzi3/SVlkqumqkv+KYwC2daM0RerN3lfuYqs9Cx9aum5A+e8iLOB5879djBNakKg= ; Message-ID: <20060828192307.54202.qmail@web31815.mail.mud.yahoo.com> Received: from [63.203.180.122] by web31815.mail.mud.yahoo.com via HTTP; Mon, 28 Aug 2006 12:23:07 PDT Date: Mon, 28 Aug 2006 12:23:07 -0700 (PDT) From: Richard Broersma Jr Subject: Re: Fastest way to get max tstamp To: Henry Ortega , pgsql-sql@postgresql.org In-Reply-To: <2bffcc330608281110j404e3fd1q9d6d27a6a97ad31d@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.355 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: *** X-Archive-Number: 200608/225 X-Sequence-Number: 26183 > name | program | effective | tstamp | rate > ------+---------+------------+----------------------------+------ > jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > > I want to get: > name | program | effective | tstamp | rate > ------+---------+------------+----------------------------+------ > jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > > Basically, for effective='08-16-2006', it only gets the latest inserted > record (using tstamp) for that effective date, which is 2006-08-25 11:57: > 17.394854. > > So what is the quickest way to do this? > I can always do: > Select * from Table t where tstamp=(select max(tstamp) from Table t2 where > t2.name=t.name and t2.effective=t.effective) > but it takes so long since this is a huge table. > > Any suggestions? SELECT name, program, effective, tstamp, rate FROM TABLE AS T1 JOIN ( SELECT max(tstamp) as maxtstamp FROM Table WHERE tstamp between current_timestamp - interval '7 days' and current_timestamp GROUP BY name, program, effective ) AS T2 ON (T1.tstamp = T2.maxtstamp) ; A smaller date range on a large table will really speed up your query also. If you really need to see the results of the same table over and over again, a materialized view(i.e. push the query results into a table and then add incremental updates over time) would probably work better for you.