Review: listagg aggregate
in [PgSql]
|
From: Pavel Stehule on 28 Jan 2010 12:12 2010/1/28 Robert Haas <robertmhaas(a)gmail.com>: > On Thu, Jan 28, 2010 at 10:56 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> Robert Haas <robertmhaas(a)gmail.com> writes: >>> On Thu, Jan 28, 2010 at 9:01 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >>>> simplest could not be a best. There have to be only a const >>>> expression. But we have not possibility to check it in pg. >> >>> Well... that's an entirely arbitrary limitation. Â I admit that it >>> doesn't seem likely that someone would want to have a variable >>> delimiter, but putting extra effort and code complexity into >>> preventing it seems pointless. >> >> Yeah. Â The real issue here is that in some cases you'd like to have >> non-aggregated parameters to an aggregate, but SQL has no notation >> to express that. > > Right. > >> I think Pavel's underlying complaint is that if the delimiter >> argument isn't constant, then we're exposing an implementation >> dependency in terms of just which values get separated by which >> delimiters. Â The most practical implementation seems to be that >> the first-call delimiter isn't actually used at all, and on >> subsequent calls the delimiter *precedes* the associated value, >> which is a bit surprising given the order in which one writes >> them. Â Not sure if this is worth documenting though. Â Those two >> or three people who actually try it will figure it out soon enough. > ok - there is one query, in 99.99% the second argument will be a constant. Can we use this information and optimize function for this case? The detoast on every row can take some percent from a performance. Pavel > Yeah, I'm thoroughly unworried about it. > > ...Robert > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 28 Jan 2010 12:14 2010/1/28 Robert Haas <robertmhaas(a)gmail.com>: > On Wed, Jan 27, 2010 at 9:47 PM, Takahiro Itagaki > <itagaki.takahiro(a)oss.ntt.co.jp> wrote: >> * Do we need better names for string_agg1_transfn and string_agg2_transfn? >> Â They are almost "internal names", but we could have more >> Â like string_agg_with_sep_transfn. > > Surely the names of the transition and final functions should match > the name of the aggregate. Â But if there's a proposal on the table to > call this string_app_with_sep() instead of just string_agg(), +1 from > me. no, >>>string_app_with_sep<<< is too long for common using. Pavel > > ...Robert > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 28 Jan 2010 12:16 Pavel Stehule <pavel.stehule(a)gmail.com> writes: > in 99.99% the second argument will be a constant. Can we use this > information and optimize function for this case? > The detoast on every row can take some percent from a performance. What detoast? There won't be one for a constant, nor even for a variable in any sane situation --- who's going to be using multi-kilobyte delimiter values? And if they do, aren't they likely to run out of memory for the result long before the repeated detoasts become an interesting problem? You're arguing about a case that seems quite irrelevant to the real world. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 28 Jan 2010 12:17 2010/1/28 Hitoshi Harada <umi.tanuki(a)gmail.com>: > 2010/1/29 Pavel Stehule <pavel.stehule(a)gmail.com>: >> 2010/1/28 Robert Haas <robertmhaas(a)gmail.com>: >>> On Thu, Jan 28, 2010 at 9:01 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >>>> simplest could not be a best. There have to be only a const >>>> expression. But we have not possibility to check it in pg. >>> >>> Well... that's an entirely arbitrary limitation.  I admit that it >>> doesn't seem likely that someone would want to have a variable >>> delimiter, but putting extra effort and code complexity into >>> preventing it seems pointless. >> >> It is only a few lines with zero complexity. >> >> The main issue of Takahiro proposal is  "unclean" behave. >> >> we can have a content >> >> c1   c2 >> ----------- >> c11, c12, >> c21, c22 >> >> and result of string_agg(c1, c2) >> >> have to be ?? c11 c12 c21 or c11 c22 c21 ?? What if some content of c2 >> will be NULL ?? I checked oracle. Oracle doesn't allow variable as >> delimiter. We can't check it. But we can fix first value and using it >> as constant. > > What about get_fn_expr_arg_stable() to check if the argument is stable > during aggregate? I newer know so this function exists. Now we can a) check and allow only stable params b) when second parameter is stable, then store it and use it as constant. I prefer a) Pavel > > Regards, > > > -- > Hitoshi Harada > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 28 Jan 2010 12:19
2010/1/28 Tom Lane <tgl(a)sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule(a)gmail.com> writes: >> in 99.99% the second argument will be a constant. Can we use this >> information and optimize function for this case? > >> The detoast on every row can take some percent from a performance. > > What detoast? Â There won't be one for a constant, nor even for a > variable in any sane situation --- who's going to be using > multi-kilobyte delimiter values? Â And if they do, aren't they likely > to run out of memory for the result long before the repeated detoasts > become an interesting problem? Â You're arguing about a case that > seems quite irrelevant to the real world. > I asking with get_fn_expr_arg_stable() we are able to fix second parameter without some performance issues. Regards Pavel > Â Â Â Â Â Â Â Â Â Â Â Â regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |