Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CF2A4634D8A for ; Thu, 30 Jul 2009 21:08:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 24432-07 for ; Fri, 31 Jul 2009 00:08:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f207.google.com (mail-px0-f207.google.com [209.85.216.207]) by mail.postgresql.org (Postfix) with ESMTP id 1ACCD63479A for ; Thu, 30 Jul 2009 21:08:07 -0300 (ADT) Received: by pxi20 with SMTP id 20so138076pxi.19 for ; Thu, 30 Jul 2009 17:08:06 -0700 (PDT) MIME-Version: 1.0 In-Reply-To: <4A71CD54.2050806@pinpointresearch.com> Received: by 10.115.90.11 with SMTP id s11mr130085wal.20.1248998886269; Thu, 30 Jul 2009 17:08:06 -0700 (PDT) Message-ID: <0016e64f6820f0e8f3046ff53644@google.com> Date: Fri, 31 Jul 2009 00:08:06 +0000 Subject: Re: SQL report From: wkipjohn@gmail.com To: Steve Crawford , wkipjohn@gmail.com Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e64f6820f0e8db046ff53641 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/3 X-Sequence-Number: 32912 --0016e64f6820f0e8db046ff53641 Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Hi Steve, Thanks for you suggestions. In my senario, what is current depends on users. Because if user wants a status report at 00:00 1st Jan 2009, then 00:00 1st Jan 2009 is current. So it is not possible to flag any records as current unless the user tells us what is current. cheers John On Jul 31, 2009 2:41am, Steve Crawford wrote: > wkipjohn@gmail.com wrote: > I have the following senario. > I have a tracking system. The system will record the status of an object > regularly, all the status records are stored in one table. And it will > keep a history of maximum 1000 status record for each object it tracks. > The maximum objects the system will track is 100,000. Which means I will > potentially have a table size of 100 million records. > I have to generate a report on the latest status of all objects being > tracked at a particular point in time, and also I have to allow user to > sort and filter on different columes in the status record displayed in > the report. > ... > Just wanna to know if anyone have a different approach to my senario. > Thanks alot. > Not knowing all the details of your system, here are some things you > could experiment with: > 1. Add a "latest record id" field in your object table (automatically > updated with a trigger) that would allow you to do a simple join with the > tracking table. I suspect that such a join will be far faster than > calculating "max" 100,000 times at the expense of a slightly larger main > table. > 2. Add a "current record flag" in the status table that simply flags the > most recent record for each object (again, use triggers to keep the flag > appropriately updated). This would also eliminate the need for the "max" > subquery. You could even create a partial index filtering on the "current > record flag" which could speed things up if the reporting query is > written correctly. > 3. Partition the table into a "current status table" and "historical > status table" (each inheriting from the main table). Use a trigger so > that anytime a new status record in added, the old "current" record is > moved from the "current" to the "historical" table and the new one added > to the "current" table. The latest status report will only need a simple > join on the "current" table with a max size of 100,000 rather than a more > complex query over a 100,000,000 record table. > Cheers, > Steve --0016e64f6820f0e8db046ff53641 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Steve,

Thanks for you suggestions. In my senario, what is cur= rent depends on users. Because if user wants a status report at 00:00 1st J= an 2009, then 00:00 1st Jan 2009 is current. So it is not possible to flag = any records as current unless the user tells us what is current.

cheers
John

On Jul 31, 2009 2:41am, Steve Crawford <sc= rawford@pinpointresearch.com> wrote:
> wkipjohn@gmail.com wrote:=
>
>
> I have the following senario.
> >
>
> I have a tracking system. The system will re= cord the status of an object regularly, all the status records are stored i= n one table. And it will keep a history of maximum 1000 status record for e= ach object it tracks. The maximum objects the system will track is 100,000.= Which means I will potentially have a table size of 100 million records.>
>
>
> I have to generate a report on th= e latest status of all objects being tracked at a particular point in time,= and also I have to allow user to sort and filter on different columes in t= he status record displayed in the report.
>
>
> .= ..
>
>
>
>
> Just wanna to know= if anyone have a different approach to my senario. Thanks alot.
> =
>
>
>
> Not knowing all the details of = your system, here are some things you could experiment with:
>
>
>
> 1. Add a "latest record id" field in= your object table (automatically updated with a trigger) that would allow = you to do a simple join with the tracking table. I suspect that such a join= will be far faster than calculating "max" 100,000 times at the e= xpense of a slightly larger main table.
>
>
> > 2. Add a "current record flag" in the status table that s= imply flags the most recent record for each object (again, use triggers to = keep the flag appropriately updated). This would also eliminate the need fo= r the "max" subquery. You could even create a partial index filte= ring on the "current record flag" which could speed things up if = the reporting query is written correctly.
>
>
> <= br />> 3. Partition the table into a "current status table" an= d "historical status table" (each inheriting from the main table)= . Use a trigger so that anytime a new status record in added, the old "= ;current" record is moved from the "current" to the "hi= storical" table and the new one added to the "current" table= . The latest status report will only need a simple join on the "curren= t" table with a max size of 100,000 rather than a more complex query o= ver a 100,000,000 record table.
>
>
>
> = Cheers,
>
> Steve
>
>
> --0016e64f6820f0e8db046ff53641--