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
Here is the query and explain . PostgreSql is 8.3.1 on new server with
raid 10 Serial SCSI.
SELECT period.period_id,
period.period_start,
period.period_end,
accnt.accnt_id,
accnt.accnt_number,
accnt.accnt_descrip,
period.period_yearperiod_id,
accnt.accnt_type,
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,
COALESCE(( SELECT sum(gltrans.gltrans_amount) AS sum
FROM gltrans
WHERE gltrans.gltrans_date <= period.period_end
AND gltrans.gltrans_date >= period.period_start
AND gltrans.gltrans_amount <= 0::numeric
AND gltrans.gltrans_accnt_id = accnt.accnt_id
AND gltrans.gltrans_posted = true), 0.00)::text::money AS
negative,
COALESCE(( SELECT sum(gltrans.gltrans_amount) AS sum
FROM gltrans
WHERE gltrans.gltrans_date <= period.period_end
AND gltrans.gltrans_date >= period.period_start
AND gltrans.gltrans_amount >= 0::numeric
AND gltrans.gltrans_accnt_id = accnt.accnt_id
AND gltrans.gltrans_posted = true), 0.00)::text::money AS
positive,
COALESCE(( SELECT sum(gltrans.gltrans_amount) AS sum
FROM gltrans
WHERE gltrans.gltrans_date <= period.period_end
AND gltrans.gltrans_date >= period.period_start
AND gltrans.gltrans_accnt_id = accnt.accnt_id
AND gltrans.gltrans_posted = true), 0.00)::text::money AS
difference,
COALESCE(( SELECT sum(gltrans.gltrans_amount) AS sum
FROM gltrans
WHERE gltrans.gltrans_date <= period.period_end
AND gltrans.gltrans_accnt_id = accnt.accnt_id
AND gltrans.gltrans_posted = true), 0.00)::text::money AS
endbalance
FROM period, accnt
ORDER BY period.period_id, accnt.accnt_number;
"Sort (cost=4083970.56..4083974.89 rows=1729 width=57) (actual
time=24680.402..24681.386 rows=1729 loops=1)"
" Sort Key: period.period_id, accnt.accnt_number"
" Sort Method: quicksort Memory: 292kB"
" -> Nested Loop (cost=1.14..4083877.58 rows=1729 width=57) (actual
time=4.043..24674.258 rows=1729 loops=1)"
" -> Seq Scan on accnt (cost=0.00..4.33 rows=133 width=41)
(actual time=0.011..0.158 rows=133 loops=1)"
" -> Materialize (cost=1.14..1.27 rows=13 width=16) (actual
time=0.001..0.010 rows=13 loops=133)"
" -> Seq Scan on period (cost=0.00..1.13 rows=13
width=16) (actual time=0.005..0.023 rows=13 loops=1)"
" SubPlan"
" -> Aggregate (cost=1093.64..1093.65 rows=1 width=8) (actual
time=6.039..6.039 rows=1 loops=1729)"
" -> Bitmap Heap Scan on gltrans (cost=398.21..1092.18
rows=585 width=8) (actual time=5.171..5.623 rows=428 loops=1729)"
" Recheck Cond: ((gltrans_accnt_id = $1) AND
(gltrans_date <= $3))"
" Filter: gltrans_posted"
" -> BitmapAnd (cost=398.21..398.21 rows=636
width=0) (actual time=5.158..5.158 rows=0 loops=1729)"
" -> Bitmap Index Scan on
gltrans_gltrans_accnt_id_idx (cost=0.00..30.57 rows=1908 width=0)
(actual time=0.078..0.078 rows=574 loops=1729)"
" Index Cond: (gltrans_accnt_id = $1)"
" -> Bitmap Index Scan on
gltrans_gltrans_date_idx (cost=0.00..367.10 rows=25446 width=0) (actual
time=7.407..7.407 rows=63686 loops=1183)"
" Index Cond: (gltrans_date <= $3)"
" -> Aggregate (cost=58.19..58.20 rows=1 width=8) (actual
time=0.920..0.921 rows=1 loops=1729)"
" -> Bitmap Heap Scan on gltrans (cost=38.90..58.16
rows=9 width=8) (actual time=0.843..0.878 rows=40 loops=1729)"
" Recheck Cond: ((gltrans_date <= $3) AND
(gltrans_date >= $0) AND (gltrans_accnt_id = $1))"
" Filter: gltrans_posted"
" -> BitmapAnd (cost=38.90..38.90 rows=10
width=0) (actual time=0.839..0.839 rows=0 loops=1729)"
" -> Bitmap Index Scan on
gltrans_gltrans_date_idx (cost=0.00..8.08 rows=382 width=0) (actual
time=0.782..0.782 rows=5872 loops=1729)"
" Index Cond: ((gltrans_date <= $3) AND
(gltrans_date >= $0))"
" -> Bitmap Index Scan on
gltrans_gltrans_accnt_id_idx (cost=0.00..30.57 rows=1908 width=0)
(actual time=0.076..0.076 rows=574 loops=798)"
" Index Cond: (gltrans_accnt_id = $1)"
" -> Aggregate (cost=58.20..58.21 rows=1 width=8) (actual
time=0.897..0.898 rows=1 loops=1729)"
" -> Bitmap Heap Scan on gltrans (cost=38.89..58.19
rows=4 width=8) (actual time=0.845..0.874 rows=20 loops=1729)"
" Recheck Cond: ((gltrans_date <= $3) AND
(gltrans_date >= $0) AND (gltrans_accnt_id = $1))"
" Filter: (gltrans_posted AND (gltrans_amount >=
0::numeric))"
" -> BitmapAnd (cost=38.89..38.89 rows=10
width=0) (actual time=0.840..0.840 rows=0 loops=1729)"
" -> Bitmap Index Scan on
gltrans_gltrans_date_idx (cost=0.00..8.08 rows=382 width=0) (actual
time=0.783..0.783 rows=5872 loops=1729)"
" Index Cond: ((gltrans_date <= $3) AND
(gltrans_date >= $0))"
" -> Bitmap Index Scan on
gltrans_gltrans_accnt_id_idx (cost=0.00..30.57 rows=1908 width=0)
(actual time=0.077..0.077 rows=574 loops=798)"
" Index Cond: (gltrans_accnt_id = $1)"
" -> Aggregate (cost=58.20..58.21 rows=1 width=8) (actual
time=0.908..0.909 rows=1 loops=1729)"
" -> Bitmap Heap Scan on gltrans (cost=38.89..58.19
rows=4 width=8) (actual time=0.854..0.885 rows=20 loops=1729)"
" Recheck Cond: ((gltrans_date <= $3) AND
(gltrans_date >= $0) AND (gltrans_accnt_id = $1))"
" Filter: (gltrans_posted AND (gltrans_amount <=
0::numeric))"
" -> BitmapAnd (cost=38.89..38.89 rows=10
width=0) (actual time=0.843..0.843 rows=0 loops=1729)"
" -> Bitmap Index Scan on
gltrans_gltrans_date_idx (cost=0.00..8.08 rows=382 width=0) (actual
time=0.785..0.785 rows=5872 loops=1729)"
" Index Cond: ((gltrans_date <= $3) AND
(gltrans_date >= $0))"
" -> Bitmap Index Scan on
gltrans_gltrans_accnt_id_idx (cost=0.00..30.57 rows=1908 width=0)
(actual time=0.078..0.078 rows=574 loops=798)"
" Index Cond: (gltrans_accnt_id = $1)"
" -> Aggregate (cost=1093.64..1093.65 rows=1 width=8) (actual
time=5.485..5.485 rows=1 loops=1729)"
" -> Bitmap Heap Scan on gltrans (cost=398.21..1092.18
rows=585 width=8) (actual time=4.699..5.110 rows=388 loops=1729)"
" Recheck Cond: ((gltrans_accnt_id = $1) AND
(gltrans_date < $0))"
" Filter: gltrans_posted"
" -> BitmapAnd (cost=398.21..398.21 rows=636
width=0) (actual time=4.687..4.687 rows=0 loops=1729)"
" -> Bitmap Index Scan on
gltrans_gltrans_accnt_id_idx (cost=0.00..30.57 rows=1908 width=0)
(actual time=0.079..0.079 rows=574 loops=1729)"
" Index Cond: (gltrans_accnt_id = $1)"
" -> Bitmap Index Scan on
gltrans_gltrans_date_idx (cost=0.00..367.10 rows=25446 width=0) (actual
time=6.717..6.717 rows=57814 loops=1183)"
" Index Cond: (gltrans_date < $0)"
"Total runtime: 24682.580 ms"
