From: Steve Crawford <scrawford@pinpointresearch.com>
To: wkipjohn@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL report
Date: Thu, 30 Jul 2009 09:41:56 -0700
Message-ID: <4A71CD54.2050806@pinpointresearch.com> (raw)
In-Reply-To: <0016e64f68205aab0d046fe35882@google.com>
References: <0016e64f68205aab0d046fe35882@google.com>
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
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: scrawford@pinpointresearch.com, wkipjohn@gmail.com
Subject: Re: SQL report
In-Reply-To: <4A71CD54.2050806@pinpointresearch.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