Re: Parallel Aggregates for string_agg and array_agg

On Thu, 14 Mar 2019 at 09:53, Tomer Praizler <tomer.praizler@gmail.com> wrote:
> Just wanted to check if there is any magic that can be done to make this happen.
> The closest thing I ran into was this guy patch -
https://www.postgresql.org/message-id/CAKJS1f8LV7AT%3DAAhdYGKtGrGkSkEgO6C_SW2Ztz1sR3encisqw%40mail.gmail.com

If you're able to change the SQLs and point them at some other
aggregate function, then you could create an extension with the code
from that patch and CREATE AGGREGATE your own version of those
functions using the combine and [de]serial functions.  I know that
creating your own extension is pretty out there for the novice mailing
list, but if I thought of another easier way if have told you that
instead.

> I didn't try it, but wanted to check if there is any way to deal with the need to aggregate on a string, by creating
anarray while doing a group by? Should I manipulate my data to be able to do it, maybe by generating an int out of
thosestrings? Any other idea? 

Well, array_agg is non-parallel too, so don't see how having an array
and converting that into a string later would help.  The other
aggregates that are parallel aware don't really let you get individual
values back out of the aggregated state, so there's not really a way
to turn that into an array or a string containing all the values that
were aggregated.

Does your use-case really need parallel versions of these aggregates?
I imagined that these would perform best when the underlying scan had
to skip lots of values, or when the aggregate had a FILTER (WHERE ...)
clause. Maybe if the filtering can be done by using an index then
performance would up to the level you need?

If you could share a simplified version of your use case perhaps
someone can suggest a way to speed it up another way.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services