From: Justin <justin@emproshunts.com>
To: pgsql-performance@postgresql.org
Subject: Re: need to speed up query
Date: Tue, 06 May 2008 08:22:02 -0500
Message-ID: <48205B7A.90301@emproshunts.com> (raw)
In-Reply-To: <op.uap0issmcigqcu@apollo13.peufeu.com>
References: <481FBC0D.7010509@emproshunts.com>
<op.uap0issmcigqcu@apollo13.peufeu.com>
PFC wrote:
>
>> 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 ?
No periods can never overlap. If the periods did you would be in
violation of many tax laws around the world. Plus it you would not know
how much money you are making or losing.
Generally yes a accounting period is a normal calendar month. but you
can have 13 periods in a normal calendar year. 52 weeks in a year / 4
weeks in month = 13 periods or 13 months in a Fiscal Calendar year.
This means if someone is using a 13 period fiscal accounting year the
start and end dates are offset from a normal calendar.
To make this really funky you can have a Fiscal Calendar year start
June 15 2008 and end on June 14 2009
http://en.wikipedia.org/wiki/Fiscal_year>
>> 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.
>
For accounting purposes you need to know the Beginning Balances,
Debits, Credits, Difference between Debits to Credits and the Ending
Balance for each account. We have 133 accounts with presently 12
periods defined so we end up 1596 rows returned for this query.
So period 1 should have for the most part have Zero for Beginning
Balances for most types of Accounts. Period 2 is Beginning Balance is
Period 1 Ending Balance, Period 3 is Period 2 ending balance so and so
on forever.
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-performance@postgresql.org
Cc: justin@emproshunts.com
Subject: Re: need to speed up query
In-Reply-To: <48205B7A.90301@emproshunts.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