Date: Mon, 26 Mar 2018 17:20:06 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	David Rowley <david.rowley@2ndquadrant.com>,
	Andres Freund <andres@anarazel.de>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Parallel Aggregates for string_agg and array_agg
Message-ID: <20180326212006.GT24540@tamriel.snowman.net>
References: 
 <CAKJS1f8LV7AT=AAhdYGKtGrGkSkEgO6C_SW2Ztz1sR3encisqw@mail.gmail.com>
 <1b6ee15c-c218-761e-0120-c1ed438e2db2@2ndquadrant.com>
 <657be73a-a21d-0ae1-5c62-870afbdfddd4@2ndquadrant.com>
 <CAKJS1f-bKAD+s-CJetRQPjmrR-gF-kfvwfzhQm-A0PA-nsr1xw@mail.gmail.com>
 <20180326022647.GL24540@tamriel.snowman.net>
 <CAKJS1f-axkksX3XzduLJuTL=o8CSwf1NaQYO6=iHSXR_B3pCKQ@mail.gmail.com>
 <20180326132009.GN24540@tamriel.snowman.net>
 <CAKJS1f8JVpUUkYP2c+RyVRdpS1dnjB_DA5fbcDSL3qfMRqc81Q@mail.gmail.com>
 <6538.1522096067@sss.pgh.pa.us>
 <cd620e7f-a9dd-ad15-72a8-6c0ac10ea4c3@2ndquadrant.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="gn1ylXQ+YRNuZICZ"
Content-Disposition: inline
In-Reply-To: <cd620e7f-a9dd-ad15-72a8-6c0ac10ea4c3@2ndquadrant.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--gn1ylXQ+YRNuZICZ
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Tomas Vondra (tomas.vondra@2ndquadrant.com) wrote:
> On 03/26/2018 10:27 PM, Tom Lane wrote:
> > I fear that what will happen, if we commit this, is that something like
> > 0.01% of the users of array_agg and string_agg will be pleased, another
> > maybe 20% will be unaffected because they wrote ORDER BY which prevents
> > parallel aggregation, and the remaining 80% will scream because we broke
> > their queries.  Telling them they should've written ORDER BY isn't going
> > to cut it, IMO, when the benefit of that breakage will accrue only to s=
ome
> > very tiny fraction of use-cases.
>=20
> Isn't the ordering unreliable *already*? It depends on ordering of
> tuples on the input. So if the table is scanned by index scan or
> sequential scan, that will affect the array_agg/string_agg results. If
> the input is a join, it's even more volatile.

It's not even just that- for a seq scan, it'll also depend on if anyone
else is also scanning the table as we might not start at the beginning
of the table thanks to synchronize seq scans.

> IMHO it's not like we're making the ordering unpredictable - it's been
> like that since forever.

Yeah, there certainly seems like a lot of opportunity for the ordering
to end up being volatile already and queries depending on it not
changing really shouldn't be making that assumption.  I do think it was
probably a bad move on our part to say that ordering a subquery will
"usually" work in the documentation but having that slip-up in the
documentation mean that we actually *are* going to guarantee it'll
always work to use a subquery ordering to feed an aggregate is a pretty
big stretch (and is it even really true today anyway?  the documentation
certainly doesn't seem to be clear on that...).

> Also, how is this different from ORDER BY clause? If a user does not
> specify an ORDER BY clause, I don't think we'd care very much about
> changes to output ordering due to plan changes, for example.

This seems like it boils down to "well, everyone *knows* that rows can
come back in any order" combined with "our docs claim that an ordered
subquery will *usually* work".

In the end, I do tend to agree that we probably should add parallel
support to these aggregates, but it'd also be nice to hear from those
who had worked to add parallelism to the various aggregates as to if
there was some reason these were skipped.

Thanks!

Stephen

--gn1ylXQ+YRNuZICZ
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJauWQGAAoJEO1sijiDR2RVa2sQAId5/u+m8l17kG7oYB6QftsW
aeJ7rNZPAhbbvvtH78BpmbifLv9h7bu7sP9CI6exZk+/yQar0McmAd5FpBVNQXgn
R+onhBVfX1/CRYXrSOkGc1yXjgjo39pn4xdO6YP2rlnpNJ1vhfUc5vrnX+mKFLT0
YpNn5LnunXGrGRR0ECz5uq5M4zqMKtz9EAbcWZsY2kq2kp9krbUSbuYdWuUfsdHn
C/GftzEmC46qf8DI/u4IbflNF2fGXxzLeD1ill5E6Vml5+S93KB0GNM0qxTEWTt3
DRBCFPeh6ORp4JinfzOG6CNWKxvMA0nSf/yML1QLHnp6ygKqL7iZvFs5IRdtNkZY
lGxvDzAiHl4D1Ul10p2BMwo+bNmePbfZeWQ6dxi/fFbc2LaAQwINQBM6fgXseEXN
Vd+PqAZN6eGwkuV9O2AKT/g+ij+e/0xkTTN7hfUkRavMo9JH4z5fwrEyVx2+0Hys
xbtm8iNy083nyR3b0xRsroaO+uPvXJgaXg4gzxLfVmtQfjORgPHd9/h4gVcECgvn
YkqWH6osQXW8Nxs3eIVEohOu9Es1GLApoWdLh0ehhvYvFI1+dL2Vi3+q772TdjsH
7SVABzC9ZeYvKdMe7jPDHbksRcOGP9TUk6YuoADVpA1rhybQmrwJYXYf3x3+GTuE
pPjG+ZGt0l/1TeQiK5Mc
=rwqJ
-----END PGP SIGNATURE-----

--gn1ylXQ+YRNuZICZ--