Date: Tue, 06 May 2008 09:02:42 +0200
To: Justin <justin@emproshunts.com>, pgsql-performance@postgresql.org
Subject: Re: need to speed up query
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <481FBC0D.7010509@emproshunts.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.uap0issmcigqcu@apollo13.peufeu.com>
In-Reply-To: <481FBC0D.7010509@emproshunts.com>
User-Agent: Opera Mail/9.24 (Linux)


> i've had to write queries to get trail balance values out of the GL  =

> transaction table and i'm not happy with its performance The table has=
  =

> 76K rows growing about 1000 rows per working day so the performance is=
  =

> not that great it takes about 20 to 30 seconds to get all the records =
 =

> for the table and when we limit it to single accounting period it drop=
s  =

> down to 2 seconds

	What is a "period" ? Is it a month, or something more "custom" ? Can  =

periods overlap ?

>     COALESCE(( SELECT sum(gltrans.gltrans_amount) AS sum
>         FROM gltrans
>         WHERE gltrans.gltrans_date < period.period_start
>             AND gltrans.gltrans_accnt_id =3D accnt.accnt_id
>             AND gltrans.gltrans_posted =3D true), 0.00)::text::money A=
S  =

> beginbalance,

	Note that here you are scanning the entire table multiple times, the  =

complexity of this is basically (rows in gltrans)^2 which is something  =

you'd like to avoid.