need to speed up query - Mailing list pgsql-performance

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"