From: wkipjohn@gmail.com
To: pgsql-sql@postgresql.org
Subject: SQL report
Date: Thu, 30 Jul 2009 02:48:57 +0000
Message-ID: <0016e64f68205aab0d046fe35882@google.com> (raw)
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.
The following is a brief description in the status record (they are not
actual code)
ObjectRecord(
objectId bigint PrimaryKey
desc varchar
)
StatusRecord (
id bigint PrimaryKey
objectId bigint indexed
datetime bigint indexed
capacity double
reliability double
efficiency double
)
I have tried to do the following, it works very well with around 20,000
objects. (The query return in less than 10s) But when I have 100,000
objects it becomes very very slow. (I don't even have patience to wait for
it to return.... I kill it after 30 mins)
select * from statusrecord s1 INNER JOIN ( SELECT objectId , MAX(datetime)
AS msdt FROM statusrecord WHERE startDatetime <= 1233897527657 GROUP BY
objectId ) AS s2 ON ( s1.objectId = s2.objectId AND s1.datetime = s2.msdt )
where ( capacity < 10.0 ) order by s1.datetime DESC, s1.objectId DESC;
I did try to write a store procedure like below, for 100,000 objects and
1000 status records / object, it returns in around 30 mins.
CREATE OR REPLACE FUNCTION getStatus(pitvalue BIGINT) RETURNS SETOF
statusrecord AS $BODY$
DECLARE
id VARCHAR;
status statusrecord%ROWTYPE;
BEGIN
FOR object IN SELECT * FROM objectRecord
LOOP
EXECUTE 'SELECT * FROM statusrecord WHERE objectId = ' ||
quote_literal(object.objectId) ||
' AND datetime <= ' || quote_literal(pitvalue) || ' ORDER BY datetime DESC'
INTO status;
IF FOUND THEN
RETURN NEXT status;
END IF;
END LOOP;
RETURN;
END
$BODY$ LANGUAGE plpgsql;
Just wanna to know if anyone have a different approach to my senario.
Thanks alot.
John
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
Subject: Re: SQL report
In-Reply-To: <0016e64f68205aab0d046fe35882@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