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. 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). 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 ! ( http://stackoverflow.com/questions/18873797/plpgsql-expert-set-of-record-input-and-output-for-a-function/18874283#18874283 ) Cheers, Rémi-C 2013/10/22 Merlin Moncure <mmonc...@gmail.com> > On Tue, Oct 22, 2013 at 9:09 AM, Rémi Cura <remi.c...@gmail.com> wrote: > > > > 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) : > > That is not the case. With the approach above what you 'pay' vs > 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 >
