Re: aggregate function ?

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