Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 82AC5633080
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 29 Jul 2009 23:49:10 -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 61347-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 30 Jul 2009 02:48:59 +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 CB38A632F61
	for <pgsql-sql@postgresql.org>; Wed, 29 Jul 2009 23:48:59 -0300 (ADT)
Received: by pxi20 with SMTP id 20so46452pxi.19
	for <pgsql-sql@postgresql.org>; Wed, 29 Jul 2009 19:48:57 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.115.90.11 with SMTP id s11mr33618wal.20.1248922137437;
 Wed, 29
	Jul 2009 19:48:57 -0700 (PDT)
Message-ID: <0016e64f68205aab0d046fe35882@google.com>
Date: Thu, 30 Jul 2009 02:48:57 +0000
Subject: SQL report
From: wkipjohn@gmail.com
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0016e64f68205aaafb046fe3587f
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=3.197 tagged_above=-10 required=5
	tests=HTML_MESSAGE=0.001, LONGWORDS=3.196
X-Spam-Level: ***
X-Archive-Number: 200907/148
X-Sequence-Number: 32889

--0016e64f68205aaafb046fe3587f
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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

--0016e64f68205aaafb046fe3587f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I have the following senario.<br /><br />I have a tracking system. The syst=
em will record the status of an object regularly, all the status records ar=
e stored in one table. And it will keep a history of maximum 1000 status re=
cord for each object it tracks. The maximum objects the system will track i=
s 100,000. Which means I will potentially have a table size of 100 million =
records.<br /><br />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 al=
low user to sort and filter on different columes in the status record displ=
ayed in the report.<br /><br />The following is a brief description in the =
status record (they are not actual code)<br /><br />ObjectRecord(<br />	obj=
ectId 		bigint	PrimaryKey<br />	desc			varchar	<br />)<br /><br />StatusRec=
ord (<br />	id 			bigint 	PrimaryKey<br />	objectId		bigint	indexed<br />	d=
atetime		bigint	indexed<br />	capacity 		double				<br />	reliability		doub=
le<br />	efficiency		double<br />)<br /><br />I have tried to do the follow=
ing, it works very well with around 20,000 objects. (The query return in le=
ss than 10s) But when I have 100,000 objects it becomes very very slow. (I =
don&#39;t even have patience to wait for it to return.... I kill it after 3=
0 mins) <br /><br />select * from statusrecord s1 INNER JOIN ( SELECT objec=
tId ,  MAX(datetime) AS msdt FROM statusrecord WHERE startDatetime &lt;=3D =
1233897527657 GROUP BY objectId ) AS s2 ON (  s1.objectId =3D  s2.objectId =
AND  s1.datetime =3D s2.msdt ) where  ( capacity &lt; 10.0 ) order by s1.da=
tetime DESC, s1.objectId DESC;<br /><br />I did try to write a store proced=
ure like below, for 100,000 objects and 1000 status records / object, it re=
turns in around 30 mins. <br /><br />CREATE OR REPLACE FUNCTION getStatus(p=
itvalue BIGINT) RETURNS SETOF statusrecord AS $BODY$<br />DECLARE<br />	id =
VARCHAR;<br />	status statusrecord%ROWTYPE;<br />BEGIN<br />	FOR object IN =
SELECT * FROM objectRecord	<br />	LOOP<br />		EXECUTE &#39;SELECT * FROM st=
atusrecord WHERE objectId =3D &#39; || quote_literal(object.objectId) || <b=
r />			&#39; AND datetime &lt;=3D &#39; || quote_literal(pitvalue) || &#39;=
 ORDER BY datetime DESC&#39;<br />		INTO status;<br />		IF FOUND THEN 		<br=
 />			RETURN NEXT status;<br />		END IF;<br />	END LOOP;<br />	RETURN;<br /=
>END<br />$BODY$ LANGUAGE plpgsql;<br />    <br />Just wanna to know if any=
one have a different approach to my senario. Thanks alot. <br /><br />John
--0016e64f68205aaafb046fe3587f--