Re: aggregate function ?

[Raimon Fernandez <coder(at)montx(dot)com>]

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