Re: Count of records in a row - Mailing list pgsql-general

From Rémi Cura
Subject Re: Count of records in a row
Date
Msg-id CAJvUf_vp-oKw3-x0=Y8u6O-gtpnnhg4YqOievL6EuKsVEobp8A@mail.gmail.com
Whole thread Raw
In response to Re: Count of records in a row  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
Ok thanks for this precision Merlin.
Seems like aggregates are way more powerful than I thought.

Obviously I need a lot more reading about custom aggregates before fully understanding it.

Elliot's query is pure SQL so obviously very cool ! 

It could be improved at the margin, and aggregates/function are certainly faster on big data. 
But if you have no specific needs I would say Elliot is easier and more universal.


Cheers & thanks all for this good discussion.

Rémi-C


2013/10/23 Merlin Moncure <mmoncure@gmail.com>
> 2013/10/22 Merlin Moncure <mmoncure@gmail.com>
>> > With a standard loop, I loop n times, and each times I only need the
>> > current
>> > row plus the previous row which I put in memory, thus O(n).
>>
>> For posterity, the above is incorrect.  Since the aggregate is ordered
>> through the window function, it gets executed exactly once per output
>> row.  It behaves exactly like a loop.  You know this because there is
>> no array in the aggregate state.
>>
> just out of pure curiosity,
> is it always the case or is it due to this particular aggregate?

It is always the case.  Generally speaking, aggregates, especially
user defined aggregates, are run once per input row.   In this case
the main utility of window functions is to order the aggregate
execution calls and (especially) allow intermediate output per input
row, instead of per aggregate grouping.

On Tue, Oct 22, 2013 at 6:01 PM, Robert James <srobertjames@gmail.com> wrote:
> Wow, this is an excellent discussion - and I must admit, a bit beyond
> my abilities.  Is there a consensus as to the best approach to adopt?
> Is Elliot's the best?

For this *specific* problem, I would give Elliot's (extremely clever)
query the nod on the basis that it does not require any supporting
infrastructure, which is always nice.  That being said, once you start
getting the mojo of user defined aggregates + window functions it
starts to become clear that it's a cleaner way of doing many types of
things that are normally handled by loops.

merlin

pgsql-general by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: Backup Question
Next
From: Albe Laurenz
Date:
Subject: Re: streaming replication: could not receive data from client: Connection reset by peer