Message-ID: <4A71CD54.2050806@pinpointresearch.com>
Date: Thu, 30 Jul 2009 09:41:56 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090318)
MIME-Version: 1.0
To: wkipjohn@gmail.com
CC: pgsql-sql@postgresql.org
Subject: Re: SQL report
References: <0016e64f68205aab0d046fe35882@google.com>
In-Reply-To: <0016e64f68205aab0d046fe35882@google.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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