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

Re: aggregate function ?

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Raimon Fernandez <coder(at)montx(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: aggregate function ?
Date: 2007-05-21 12:46:27
Message-ID: 475282.74849.qm@web31803.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- Raimon Fernandez <coder(at)montx(dot)com> wrote:
> 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 ?

Yes, any sub-select in the Select expression list will be executed many times. To fix this, you
will need to reform your query by pushing this sub-select down to the from clause.  Also you will
need to add clau_compte to the SELECT expression list of both sub-Selects

    SELECT <your expression list>, Summed_assentaments.delta_sum + Initialvalue.starting_sum
      FROM ( <your first initial sub-select> ) AS Summed_Assentaments( oid, concepte,deure, haver,
delta_sum, clau_compte )
INNER JOIN ( <your initial value sub-select> ) AS Initialvalue( starting_sum, clau_compte )
        ON Summed_Assentaments.clau_compte = Initialvalue.clau_compte;

I hope this can help to improve query time.

Regards,
Richard Broersma Jr.

In response to

Responses

pgsql-novice by date

Next:From: Raimon FernandezDate: 2007-05-21 18:40:29
Subject: Re: aggregate function ?
Previous:From: Raimon FernandezDate: 2007-05-21 07:50:11
Subject: Re: aggregate function ?

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