From: Antonin Houska <ah@cybertec.at>
To: pgsql-hackers@postgresql.org
Subject: Re: WIP: Aggregation push-down
In-reply-to: 
 <CAM2+6=W2J-iaQBgj-sdMERELQLUm5dvOQEWQ2ho+Q7KZgnonkQ@mail.gmail.com>
References: <9666.1491295317@localhost> <8160.1493369169@localhost>
 <29613.1502983342@localhost>
 <CAM2+6=W2J-iaQBgj-sdMERELQLUm5dvOQEWQ2ho+Q7KZgnonkQ@mail.gmail.com>
Comments: In-reply-to Jeevan Chalke <jeevan.chalke@enterprisedb.com>
   message dated "Thu, 07 Sep 2017 14:44:31 +0530."
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=-=-="
Date: Fri, 08 Sep 2017 14:36:03 +0200
Message-ID: <22851.1504874163@localhost>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

--=-=-=
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:

> 1.
> + if (aggref->aggvariadic ||
> + aggref->aggdirectargs || aggref->aggorder ||
> + aggref->aggdistinct || aggref->aggfilter)
>=20
> I did not understand, why you are not pushing aggregate in above cases?
> Can you please explain?

Currently I'm trying to implement infrastructure to propagate result of
partial aggregation from base relation or join to the root of the join tree
and to use these as input for the final aggregation. Some special cases are
disabled because I haven't thought about them enough so far. Some of these
restrictions may be easy to relax, others may be hard. I'll get back to them
at some point.

> 2. "make check" in contrib/postgres_fdw crashes.
>=20
> SELECT COUNT(*) FROM ft1 t1;
> ! server closed the connection unexpectedly
> ! This probably means the server terminated abnormally
> ! before or while processing the request.
> ! connection to server was lost
>=20
> From your given setup, if I wrote a query like:
> EXPLAIN VERBOSE SELECT count(*) FROM orders_0;
> it crashes.
>=20
> Seems like missing few checks.

Please consider this a temporary limitation.

> 3. In case of pushing partial aggregation on the remote side, you use sch=
ema
> named "partial", I did not get that change. If I have AVG() aggregate,
> then I end up getting an error saying
> "ERROR: schema "partial" does not exist".

Attached to his email is an extension that I hacked quickly at some point, =
for
experimental purposes only. It's pretty raw but may be useful if you just w=
ant
to play with it.

> 4. I am not sure about the code changes related to pushing partial
> aggregate on the remote side. Basically, you are pushing a whole aggregate
> on the remote side and result of that is treated as partial on the
> basis of aggtype =3D transtype. But I am not sure that is correct unless
> I miss something here. The type may be same but the result may not.

You're right. I mostly used sum() and count() in my examples but these are
special cases. It should also be checked whether aggfinalfn exists or
not. I'll fix it, thanks.

> 5. There are lot many TODO comments in the patch-set, are you working
> on those?

Yes. I wasn't too eager to complete all the TODOs soon because reviews of t=
he
partch may result in a major rework. And if large parts of the code will ha=
ve
to be wasted, some / many TODOs will be gone as well.

> Thanks
>=20
> On Thu, Aug 17, 2017 at 8:52 PM, Antonin Houska <ah@cybertec.at> wrote:
>=20
>  Antonin Houska <ah@cybertec.at> wrote:
>=20
>  > Antonin Houska <ah@cybertec.at> wrote:
>  >
>  > > This is a new version of the patch I presented in [1].
>  >
>  > Rebased.
>  >
>  > cat .git/refs/heads/master
>  > b9a3ef55b253d885081c2d0e9dc45802cab71c7b
>=20
>  This is another version of the patch.
>=20
>  Besides other changes, it enables the aggregation push-down for postgres=
_fdw,
>  although only for aggregates whose transient aggregation state is equal =
to the
>  output type. For other aggregates (like avg()) the remote nodes will hav=
e to
>  return the transient state value in an appropriate form (maybe bytea typ=
e),
>  which does not depend on PG version.
>=20
>  shard.tgz demonstrates the typical postgres_fdw use case. One query show=
s base
>  scans of base relation's partitions being pushed to shard nodes, the oth=
er
>  pushes down a join and performs aggregation of the join result on the re=
mote
>  node. Of course, the query can only references one particular partition,=
 until
>  the "partition-wise join" [1] patch gets committed and merged with this =
my
>  patch.
>=20
>  One thing I'm not sure about is whether the patch should remove
>  GetForeignUpperPaths function from FdwRoutine, which it essentially makes
>  obsolete. Or should it only be deprecated so far? I understand that
>  deprecation is important for "ordinary SQL users", but FdwRoutine is an
>  interface for extension developers, so the policy might be different.
>=20
>  [1] https://commitfest.postgresql.org/14/994/
>=20
>  Any feedback is appreciated.
>=20
>  --
>  Antonin Houska
>  Cybertec Sch=C3=B6nig & Sch=C3=B6nig GmbH
>  Gr=C3=B6hrm=C3=BChlgasse 26
>  A-2700 Wiener Neustadt
>  Web: http://www.postgresql-support.de, http://www.cybertec.at
>=20
>  --
>  Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-hackers
>=20
> --=20
> Jeevan Chalke
> Principal Software Engineer, Product Development
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
>=20
>=20

--=20
Antonin Houska
Cybertec Sch=C3=B6nig & Sch=C3=B6nig GmbH
Gr=C3=B6hrm=C3=BChlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at


--=-=-=
Content-Type: application/x-gzip
Content-Disposition: attachment; filename=partial.tgz
Content-Transfer-Encoding: base64

H4sIAKmIslkAA+0baXPaSDZf0a/oeGfG4MKAxL2uTBXBmLBlg8vgZKY2W1Rb
NKCykIgOO94p72/f97p1NEgYnGScOeiqREh6/a5+Z7dcKF7Sz+8YnTDHLWha
VasUl9TxDGq++majBKNWqfArjPVrSS2rr+BftVItVTRVe1VStbKmviKfvx0L
m4fvetQBVl6C1h9wlEtk4RkL9kYF5TfqWrVRK5Sb1Wa50SyXFa1J6Prbhqry
twpM1TdP/d6C7cdOI3D24u9JA328Xq3iVa1XVX6vxvGAx4J1/1frr0j192Qq
HKH/0/k2OIiQT7xfD25/khGu/6YsUNBty3Psr8oGu8V/MImqViuVAU6tluH1
Pv6/wIAIH8T/Sr1ZVyGGNwrNmlauaJUGRvg4/NeajYZaKjTqKsR4rdaU4n+l
UW1W6/V6qaCWK+W6Wq5X9/H/zzFC//+G7p4Ykv+nxn8Nfq/7f7X2iryIT/7N
479uLxbM8sgbctiazRw2ox5zicM837EMa0Y8h1qugRCgJ48RwyI3Dx6jZGo7
C+odKhM2pb7pje9APYZtISK1UDpUFvbEN9l4Sb25RRcMn/9gGjcTwwlt7VBx
mGnr1KM3Jr6fUtNliqvP2YIieAT2vXX0Vx4b8/8FvWVTw2TfgMb2/K9F/o+J
Hx7U6vV9/n+JEfd/PP9D/NUKtXqlgqtS2xcAf/kR+v83dPfE2Jr/a/V1/6/X
1H3+f4lxMTi9Pu+Mb4wZpNzAGJTB238N49uCTX7Ifuj1y9pVZ5hTLrtnvfPO
aWfYBpCDAIbQqHg4UJTOL6NOf9gb9CWcp61RK749PoYioeB+MhXA98uQ/PMN
0HDnzDThetkdtwf9s143R46Pl7PPbk4xLN30J4y//AWY+N5q+8uM7f3/19PY
Mf9rarVaKVcgPqiVUqW8z/8vMaT8D8m93ChVmgVYB2jAqlo1Pf9D9n8q/zcw
/zf2+f/PMRL9/+9A4+n8j8m+tO7/qlbf5/+XGMUjhRyRDw5dLplDpr4FHm1b
LqGO7VsT4s0ZuewS3XaYlOLhMfUINU37nkD/b1gGTiL2FHHpoFF7IUEX4Ckn
wojF2ITQBAiBye6cOhNi2ZDkOXZgB7cYXHiEkw+ACjWN/1KkdFAgQ6gIGGeP
8xYzPqd3jBwYlsccmHBAvIcl37UAUM4d9ahpz/J4/wB3lmV75IYRWN4JgrWv
Oq1Rh7S63atOF3/hBgm1JgUymjOHTZHYvRAE0Xm2UACLpYp3TDhp6gI8lDVw
jZnkEt7bzi25N7w5MTzUUVH5R1jmHCxt1wPluIX5gfR0upg5q09QX26R/5/2
gn1mevQSKq1xUO5dtLq99omiIIfAP3E9x9c9cimCQGs2G+Jmj/KbkgH7yICc
IDyhuudTMw9aOl6xB1x6vjlUQFABPjgdkGsX1GrhW5DdtcWVTibcWKBkROJo
aEKbbMKnF4FkMWCn7y+YY+ghO+SIUzlRMqfU8xeZ4C7iseeRBXQxQItZQBqW
Rqc68H0GWutZUxvskxvM1GGffFgfE9ffNGHdo4WJJWjLVoW4HOY5BrsTNheL
C/yG+DNTazxj3ljii5xJGCCAGGjCktkKPAksEWQKFt227pjjcRy27y19D+WS
J3EoUKFhzZKoYcqJ8ri+0kCHfUaPIVy1YZUuRBkbFpbkZ9f99ghq+nHrqjvM
nTw1AYg8c4Znj/m24tZplrCJMdV1fzEOkKTNKhZX5kFAqGyfpCA3hr6uHrCH
laljDAq2NcbAl0CTVzLSIIQMjAmRTQPoBGQ26C5An01wwSEAf2gPbTBfbtlT
3YALCiBz0+ufDcbv1ez6agIcp6xsX2YMAQziZbZzdTW4ykf9XjwlRzCCGoul
yXAjmU0OAP3jLoyApBs4SbUfZCVdI1FEgICJW9HckvJE3KELGVOC+ABNb9i/
Pj/PlnI5WCV4dNUZXV/1x/whqk8EFehTk8rPQSIcdzsjwDK+HPT6o84V4AHa
MtW4w00up1g9aa04X6/BPHrue/DciYA4/lm4aQEvxgQ5BckxDHAK8IY7dQaT
wRhRZctqnvy0MhfZegw5A55kg1Gzq7ChooQAnK9YMaGgAkSsrCgZWhA8XQ9j
DURb3wmy9mpUWt8cwBBmuBjCXANaTYi8nCaicyhMdRCJRQ44f5DkP0ByRHm9
OcxKmkgOUqhvTjB9B4H85oEnZWAipZDgsR+rCJmfgshV7YAlzCAQNxFLGr0Z
szD6gMi2P5tDUhMkDA8rCYkPlAXjsC3y+nZfCOPfBofYHB6f6RV/XG9AAx9i
GIGsbC8fIGCjEYF5CXMSmSxpmFzELvPeIr3LNszMrpjz5mCUmkki/e+YZ55S
/yZNB96sNspl8r/VhCY8O9T65pwjtJbnOPhCIcII091MKgbWYoVWr5RWokUE
GcQMhodwnMfnLz5MarmA0MumBrUoA0ah7SSeEYAJoq+BKl+HHAnw8lC5okuV
W21mZd5qpNPkSBcRl/MzkWfnJalOW6PrCyTBlZJmeGEq5wGRpBlYsuTIneBy
ALhI/OL3LpVJAPpbcN1kcviav08zvHBuhCRT/MitsApWGDNBjj4WI4hnWWI1
FzPAcVPHoQ9jnrAknJItas36JlsMwR/DH2iXSRGebaMxh8+01IjkdnuNaaRa
bYTpC2w3nru7Ba+oUpjPJmsOQENITIM9bO6DvmLtW4Ab0UDhZwIyk3HbBH0z
uXfoEhIkogIjs9dLa5EgN5XdyjPK7mSdjcF5vVSGVaJB0hljCwb+fcEWtvMA
FYAHHQPWBmNd/M4T25yENyeJQJ9ZDfSv4Xl7zvRbJBVgi9zjJwktj1srRXW8
ERKpLigkDAsqGes4BghwYJGdkbgD61kRY3hvePp8ZGdluidPJ/QlbujopawL
a21P1yF4KEynIfHBg3Xs4au2m2bRfALaWNpCZaWFSp+eJ1oUz8FU79CPwQ7C
uuL452gp0ftQBAllgTqzf5f+QyTnTQFQESDpV0lIyzdNgc5zfLbhPcfGv3QJ
VyPd/0EW+5Zlf5JwRC2DjNdwEW3CoiI7+tEPPuYBY0IVHHB1yR5QmJpCVUGo
QypiCgns+1FZaViTzdqWhpVs6ld3dNA+1vJHsR/KZe3TbVSUUKRmak1R8sYJ
72SjeMc72Wh36dSG3sDygcsHco9F/0pP1bahrwr7kQk4PN/2E/udEPSW1HU5
ljWzxHeJPUySFY8EBF6pYbkkcsRw3yk2hRjfmzfC0HlFCP8ByTN6G2w0Yb+S
WiQC5xObuVx+hxqQaLl6OCMCScRbBOe70lYYRPqHe/pQ+MShseKM41JWLF+O
b87h72wkyUlYbkrgaxJ9UXiIBIO6JD087BYWZP98yt9Df07tt9M9+WUdOfSV
x8Sp3Lbz3+ig/ivOGHY8/63USpUqtCd4/lOv1/bnvy8x5PPfWrlebZYqhXqz
pMFds/6M89/k3O8t2X7sMtbOf7+FuyfGlu+/wOnVNf+vlmra/vz3JUbxKPFJ
Fm/+jo/x5HNpQulBIFW5umMsPdy8dW3f0UVnsgTg/MrO7Z1BwxPU6Bsw5SPT
5zY/DTxYfxfSPsBCyLTpRGz14oeIBfLxkw99oxLMCZu9xNkUyQaFNFQR4tRL
ySmivR2KDK60huQwOPm8bI3e9VsXncN89IF5hOlQOW/1u9etboe0leGo9fa8
c7KNPu5uxAwIcjH5kKGdGABU6RzAWkBh6zNxgAn64QfL7pwXmxa7A+3fYCFm
44461miGe5vHYs9eGGJL3mEmUJggJuoBSzc+P5cPRBv9etkJ6risQsTo9S+v
R4n9W8PKhwCD61EaBB4oKCEMkHtH9Vt35ZQAOMW/B/AcRj3+pwfRwUF8JEqg
wcPvBiI0YpMeu2tZHvFpwoTxjQZjsXRscT7rMhL8XUOMhO9z9Fvn551+d/QO
+K5Eoly2hsPO6dtf37fOrztKbuuah7vxTyy8ONfYadlDbCtrf9m6QlbPybB1
1knYYrs1HBHR5pBWQCxHPvRAri0sR9uWCRFTN7xje5DljBQHRXAmE0xUcmQn
p0ulc7gmcKyJmNP4kwzoXcLd+1zM4RBFgXVNJRCxPOTGHpT2kgUE1OEFnTLZ
BmKyrr/4HmR127e8JwgbltcwLCBsPSTI4bt87AG9UXvQP8W/rSkdPo8JVDnu
EafTr/B2kkueSu23Up6UHg8T7I1X+Nt5EZ5ixJVYSNXCzuJOIR+l0hEvtom7
QWI+OeamPbh42+t3ZMwN3E25MawvsM4tLC+T9ihePMWOPOsZ9rqRla8111UW
ccaXMXi0mbcX4et7F377sR/7sR/7sR/7sR/7sR/78bcb/wejEr1fAFAAAA==

--=-=-=
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--=-=-=--