Re: need to speed up query

> 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 drops
> 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 = accnt.accnt_id
>             AND gltrans.gltrans_posted = true), 0.00)::text::money AS
> 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.