From: wkipjohn@gmail.com
To: Steve Crawford <scrawford@pinpointresearch.com>
To: wkipjohn@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL report
Date: Fri, 31 Jul 2009 00:08:06 +0000
Message-ID: <0016e64f6820f0e8f3046ff53644@google.com> (raw)
In-Reply-To: <4A71CD54.2050806@pinpointresearch.com>
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 <scrawford@pinpointresearch.com>
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
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: wkipjohn@gmail.com, scrawford@pinpointresearch.com
Subject: Re: SQL report
In-Reply-To: <0016e64f6820f0e8f3046ff53644@google.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