Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 554916338B7 for ; Fri, 31 Jul 2009 00:56:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 21561-07 for ; Fri, 31 Jul 2009 03:55:53 +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 DA2E2632D1A for ; Fri, 31 Jul 2009 00:55:53 -0300 (ADT) Received: by pxi20 with SMTP id 20so145815pxi.19 for ; Thu, 30 Jul 2009 20:55:52 -0700 (PDT) MIME-Version: 1.0 In-Reply-To: <4A71B953.4070208@gmail.com> Received: by 10.114.88.19 with SMTP id l19mr141326wab.27.1249012552949; Thu, 30 Jul 2009 20:55:52 -0700 (PDT) Message-ID: <0016364c637589ebcb046ff865c1@google.com> Date: Fri, 31 Jul 2009 03:55:52 +0000 Subject: Re: SQL report From: wkipjohn@gmail.com To: Rob Sargent , wkipjohn@gmail.com Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016364c637589ebb2046ff865be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/4 X-Sequence-Number: 32913 --0016364c637589ebb2046ff865be Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Hi Rob, I have default B-Tree indexes created for each of the indexed columes and primary key columes. (No multiple columes indexe or NULL FIRST or DESC/ASC). I am using PostgreSQL 8.3 with the auto vacuum daemon on. I assume analyse will be automatically run to collect statistics for use by the planner and there is no maintainance for B-tree indexes once it is created. (Please point me out if I am wrong about this) I will probably try to partition the status table to group more recent status records together to minimize the dataset I am querying. Thx John On Jul 31, 2009 1:16am, Rob Sargent wrote: > I would be curious to know the performance curve for let's say 20K, 40K , > 60K, 80K, 100K records. And what sort of indexing you have, whether or > not it's clustered, re-built and so on. > One could envision partitioning the status table such that recent records > were grouped together (on the assumption that they will be most > frequently "reported"). > 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. > 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 > 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 > 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 --0016364c637589ebb2046ff865be Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Rob,

I have default B-Tree indexes created for each of the in= dexed columes and primary key columes. (No multiple columes indexe or NULL = FIRST or DESC/ASC). I am using PostgreSQL 8.3 with the auto vacuum daemon o= n. I assume analyse will be automatically run to collect statistics for use= by the planner and there is no maintainance for B-tree indexes once it is = created. (Please point me out if I am wrong about this)

I will p= robably try to partition the status table to group more recent status recor= ds together to minimize the dataset I am querying.

Thx
Joh= n


On Jul 31, 2009 1:16am, Rob Sargent <robjsargent@gmai= l.com> wrote:
> I would be curious to know the performance curve= for let's say 20K, 40K , 60K, 80K, 100K records. And what sort of ind= exing you have, whether or not it's clustered, re-built and so on.
>
>
>
> One could envision partitioning the = status table such that recent records were grouped together (on the assumpt= ion that they will be most frequently "reported").
>
>
>
> wkipjohn@gmail.com wrote:
>
>=
> I have the following senario.
>
>
> <= br />> 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 w= ill 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 p= otentially have a table size of 100 million records.
>
> <= br />>
> I have to generate a report on the latest status of al= l objects being tracked at a particular point in time, and also I have to a= llow user to sort and filter on different columes in the status record disp= layed in the report.
>
>
>
> The followi= ng is a brief description in the status record (they are not actual code)>
>
>
> ObjectRecord(
>
>= ; objectId bigint PrimaryKey
>
> desc varchar
> > )
>
>
>
> StatusRecord (
&= gt;
> id bigint PrimaryKey
>
> objectId bigint in= dexed
>
> datetime bigint indexed
>
> cap= acity double
>
> reliability double
>
> e= fficiency 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 o= bjects it becomes very very slow. (I don't even have patience to wait f= or it to return.... I kill it after 30 mins)
>
>
>= ;
> select * from statusrecord s1 INNER JOIN ( SELECT objectId , M= AX(datetime) AS msdt FROM statusrecord WHERE startDatetime
>
>
> 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 getS= tatus(pitvalue BIGINT) RETURNS SETOF statusrecord AS $BODY$
>
> DECLARE
>
> id VARCHAR;
>
> status = statusrecord%ROWTYPE;
>
> BEGIN
>
> FOR o= bject IN SELECT * FROM objectRecord
>
> LOOP
> > EXECUTE 'SELECT * FROM statusrecord WHERE objectId =3D ' ||= quote_literal(object.objectId) ||
>
> ' AND datetime =
> INTO status;
>
> IF FOUND THEN
>
> RETURN NEXT status;
>
> END IF;
>
>= END LOOP;
>
> RETURN;
>
> END
> =
> $BODY$ LANGUAGE plpgsql;
>
>
>
&= gt; Just wanna to know if anyone have a different approach to my senario. T= hanks alot.
>
>
>
> John
>
> --0016364c637589ebb2046ff865be--