Re: aggregate function ? - Mailing list pgsql-novice

Hello again,


Well, now it's working ...

SELECT oid, concepte, deure, haver,
        delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE
( A3.haver ) )
                        FROM Assentaments A3
                       WHERE A3.clau_compte = '0257000000002'
                         AND A3.data BETWEEN '2006-01-01' AND
'2006-06-30' ) AS starting_Sum
   FROM( SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
                sum( COALESCE( A2.deure, 0 ) -
                     COALESCE( A2.haver, 0 )) AS value_sum
           FROM Assentaments AS A1
     INNER JOIN Assentaments AS A2
             ON A1.oid >= A2.oid
            AND A1. clau_compte = A2. clau_compte -- this was
A1.numero=A2.numero but should be Ax.clau_compte
          WHERE A1.clau_compte = '0257000000002'
       GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
       ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte,
deure, haver, delta_sum );

This returns 3217 rows, and the value_sum is ok, but it takes too
long (89.45 sec)

Can anyone confirm that it's doing for every row the
'starting_sum' (first select), and if so, how to do it just once ?


Here is the explain:
---------------------------------------------------------
Subquery Scan summed_assentaments  (cost=39706774.23..39784540.01
rows=3941 width=96)
   InitPlan
     ->  Aggregate  (cost=259.83..259.84 rows=1 width=28)
           ->  Bitmap Heap Scan on assentaments a3
(cost=181.43..259.78 rows=20 width=28)
                 Recheck Cond: ((data >= '2006-01-01'::date) AND
(data <= '2006-06-30'::date) AND (clau_compte =
'0257000000002'::bpchar))
                 ->  BitmapAnd  (cost=181.43..181.43 rows=20 width=0)
                       ->  Bitmap Index Scan on data
(cost=0.00..75.48 rows=3941 width=0)
                             Index Cond: ((data >=
'2006-01-01'::date) AND (data <= '2006-06-30'::date))
                       ->  Bitmap Index Scan on clau_compte
(cost=0.00..105.70 rows=3941 width=0)
                             Index Cond: (clau_compte =
'0257000000002'::bpchar)
   ->  GroupAggregate  (cost=39706514.39..39784230.90 rows=3941
width=92)
         ->  Sort  (cost=39706514.39..39719457.29 rows=5177160 width=92)
               Sort Key: a1.oid, a1.concepte, a1.deure, a1.haver
               ->  Nested Loop  (cost=105.70..38067432.51
rows=5177160 width=92)
                     Join Filter: (a1.oid >= a2.oid)
                     ->  Index Scan using clau_compte on assentaments
a1  (cost=0.00..14345.11 rows=3941 width=120)
                           Index Cond: (clau_compte =
'0257000000002'::bpchar)
                     ->  Bitmap Heap Scan on assentaments a2
(cost=105.70..9606.43 rows=3941 width=88)
                           Recheck Cond: ('0257000000002'::bpchar =
clau_compte)
                           ->  Bitmap Index Scan on clau_compte
(cost=0.00..105.70 rows=3941 width=0)
                                 Index Cond: ('0257000000002'::bpchar
= clau_compte)


thanks in advance!


regards,


raimon