Skip site navigation (1) Skip section navigation (2)

Re: aggregate function ?

From: Raimon Fernandez <coder(at)montx(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: aggregate function ?
Date: 2007-05-21 07:50:11
Message-ID: 157BB2F4-383C-4B27-8AFA-5C210888BD99@montx.com (view raw or flat)
Thread:
Lists: 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



In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-05-21 12:46:27
Subject: Re: aggregate function ?
Previous:From: AndreasDate: 2007-05-20 19:49:08
Subject: Re: How export from 8.2.4 down to 8.1.8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group