From: PFC <lists@peufeu.com>
To: Justin <justin@emproshunts.com>
To: pgsql-performance@postgresql.org
Subject: Re: need to speed up query
Date: Tue, 06 May 2008 09:02:42 +0200
Message-ID: <op.uap0issmcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <481FBC0D.7010509@emproshunts.com>
References: <481FBC0D.7010509@emproshunts.com>
> 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.
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: lists@peufeu.com, justin@emproshunts.com
Subject: Re: need to speed up query
In-Reply-To: <op.uap0issmcigqcu@apollo13.peufeu.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