Thanks again for the precision !
I still don't understand perfectly. We call the aggregate n times, and each time we compute the aggregate, using (potentially) n rows, thus becoming (at most) O(n*n).
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).
I posted a lot about my issue, but only about the fraction of the problem I was blocked by, and I get no conclusive answer.
Aggregates only returns one row at most, array are dangerous with big data, temp table have to be created/deleted and have to be used in the same session, cursors arn't well supported by accessing library, view can't be written, mat view weren't available.
I still don't understand perfectly. We call the aggregate n times, and each time we compute the aggregate, using (potentially) n rows, thus becoming (at most) O(n*n).
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).
I posted a lot about my issue, but only about the fraction of the problem I was blocked by, and I get no conclusive answer.
My problem was to find a good way to have a plpgsql function taking set of rows as input and returning a set of rows.
I worked on range, and I wanted a polymorphic function (working with any range).
I worked on range, and I wanted a polymorphic function (working with any range).
Aggregates only returns one row at most, array are dangerous with big data, temp table have to be created/deleted and have to be used in the same session, cursors arn't well supported by accessing library, view can't be written, mat view weren't available.
Anyway I solved it using cursors, not optimal but works !
Cheers,
Rémi-C
2013/10/22 Merlin Moncure <mmoncure@xxxxxxxxx>
On Tue, Oct 22, 2013 at 9:09 AM, Rémi Cura <remi.cura@xxxxxxxxx> wrote:That is not the case. With the approach above what you 'pay' vs
>
> Thanks for this good example Merlin !
>
> I didn't know you could use variable inside custom aggregates, and this
> allow to solve the problem!
>
> In my own problem I couldn't use aggregates because
> _as it output at most one row, it would have mean a lots of useless
> computation (as in this example I guess, (please correct me if it's not the
> case) :
standard loop is basically one pl/pgsql function call per output row.
(you can do it in straight sql, but when with pl/pgsql to leverage
cached function parsing). What you 'get' is a more general function
because the loop structure is in the query itself as well as the
output structure. This cleanly separates action from the data.
Usually, the mechanics of executing the aggregate are not a huge part
of query execution time. Actually, the worst case is when the
aggregate is trivial but no matter what it's O(n).
I'm not clear what on the issue is with your particular case, since
you didn't post it :-). Maybe post some extra detail?
merlin
