Thread: Does PG cache results of an aggregate function, (and results of non-volatile functions)?

Hi,
I do have a query which make use of the results of an aggregate
function (for example bit_or) several times in the output column list
of the SELECT clause, does PostgreSQL simply execute the aggregate
function only once and provide the output to the other calls to the
same aggregate function.
How about the case of non volatile functions? Do they get executed as
many times as they occur in the select clause?

Allan.

Allan Kamau wrote:
> Hi,
> I do have a query which make use of the results of an aggregate
> function (for example bit_or) several times in the output column list
> of the SELECT clause, does PostgreSQL simply execute the aggregate
> function only once and provide the output to the other calls to the
> same aggregate function.
> How about the case of non volatile functions? Do they get executed as
> many times as they occur in the select clause?

I did not see a reply to this.  Does anyone know the answer?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Thu, Sep 3, 2009 at 3:44 AM, Allan Kamau <kamauallan@gmail.com> wrote:
> Hi,
> I do have a query which make use of the results of an aggregate
> function (for example bit_or) several times in the output column list
> of the SELECT clause, does PostgreSQL simply execute the aggregate
> function only once and provide the output to the other calls to the
> same aggregate function.
> How about the case of non volatile functions? Do they get executed as
> many times as they occur in the select clause?

I don't think so...could you provide a better example?  It sounds like
your query is one that might benefit from use of CTE...

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Sep 3, 2009 at 3:44 AM, Allan Kamau <kamauallan@gmail.com> wrote:
>> I do have a query which make use of the results of an aggregate
>> function (for example bit_or) several times in the output column list
>> of the SELECT clause, does PostgreSQL simply execute the aggregate
>> function only once and provide the output to the other calls to the
>> same aggregate function.
>> How about the case of non volatile functions? Do they get executed as
>> many times as they occur in the select clause?

> I don't think so...could you provide a better example?  It sounds like
> your query is one that might benefit from use of CTE...

Might be overkill --- nodeAgg.c does check for textually identical
aggregate calls and avoid computing them more than once.  But looking
at the actual query and its explain plan would be necessary to be sure.

            regards, tom lane

Well I don't think the result would keep if you run a "hot" test. However, in  one SELECT clause, maybe it can be optimised .