Re: Seeking reason behind performance gain in 12 with HashAggregate

--000000000000509290059c0e72c1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jan 13, 2020 at 2:15 PM Alvaro Herrera <[email protected]>
wrote:

> On 2020-Jan-13, Shira Bezalel wrote:
>
> > Hi All,
> >
> > I'm testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a
> > significant performance gain in one specific query. This is really grea=
t,
> > but I'm just looking to understand why.
>
> pg12 reads half the number of buffers.  I bet it's because of this change=
:
>
> commit 4d0e994eed83c845a05da6e9a417b4efec67efaf
> Author:     Stephen Frost <[email protected]>
> AuthorDate: Tue Apr 2 12:35:32 2019 -0400
> CommitDate: Tue Apr 2 12:35:32 2019 -0400
>
>     Add support for partial TOAST decompression
>
>     When asked for a slice of a TOAST entry, decompress enough to return
> the
>     slice instead of decompressing the entire object.
>
>     For use cases where the slice is at, or near, the beginning of the
> entry,
>     this avoids a lot of unnecessary decompression work.
>
>     This changes the signature of pglz_decompress() by adding a boolean t=
o
>     indicate if it's ok for the call to finish before consuming all of th=
e
>     source or destination buffers.
>
>     Author: Paul Ramsey
>     Reviewed-By: Rafia Sabih, Darafei Praliaskouski, Regina Obe
>     Discussion:
> https://postgr.es/m/CACowWR07EDm7Y4m2kbhN_jnys%3DBBf9A6768RyQdKm_%3DNpkca=
Wg%40mail.gmail.com
>
> --
> =C3=81lvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

That sounds like a possibility. Thanks Alvaro.

Shira

--000000000000509290059c0e72c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:tahoma,sans-serif"><br></div></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Mon, Jan 13, 2020 at 2:15 PM Alvaro He=
rrera &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">alv=
[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">On 2020-Jan-13, Shira Bezalel wrote:<br>
<br>
&gt; Hi All,<br>
&gt; <br>
&gt; I&#39;m testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a<b=
r>
&gt; significant performance gain in one specific query. This is really gre=
at,<br>
&gt; but I&#39;m just looking to understand why.<br>
<br>
pg12 reads half the number of buffers.=C2=A0 I bet it&#39;s because of this=
 change:<br>
<br>
commit 4d0e994eed83c845a05da6e9a417b4efec67efaf<br>
Author:=C2=A0 =C2=A0 =C2=A0Stephen Frost &lt;<a href=3D"mailto:sfrost@snowm=
an.net" target=3D"_blank">[email protected]</a>&gt;<br>
AuthorDate: Tue Apr 2 12:35:32 2019 -0400<br>
CommitDate: Tue Apr 2 12:35:32 2019 -0400<br>
<br>
=C2=A0 =C2=A0 <span class=3D"gmail_default" style=3D"font-family:tahoma,san=
s-serif"></span>Add support for partial TOAST decompression<br>
<br>
=C2=A0 =C2=A0 When asked for a slice of a TOAST entry, decompress enough to=
 return the<br>
=C2=A0 =C2=A0 slice instead of decompressing the entire object.<br>
<br>
=C2=A0 =C2=A0 For use cases where the slice is at, or near, the beginning o=
f the entry,<br>
=C2=A0 =C2=A0 this avoids a lot of unnecessary decompression work.<br>
<br>
=C2=A0 =C2=A0 This changes the signature of pglz_decompress() by adding a b=
oolean to<br>
=C2=A0 =C2=A0 indicate if it&#39;s ok for the call to finish before consumi=
ng all of the<br>
=C2=A0 =C2=A0 source or destination buffers.<br>
<br>
=C2=A0 =C2=A0 Author: Paul Ramsey<br>
=C2=A0 =C2=A0 Reviewed-By: Rafia Sabih, Darafei Praliaskouski, Regina Obe<b=
r>
=C2=A0 =C2=A0 Discussion: <a href=3D"https://postgr.es/m/CACowWR07EDm7Y4m2k=
bhN_jnys%3DBBf9A6768RyQdKm_%3DNpkcaWg%40mail.gmail.com" rel=3D"noreferrer" =
target=3D"_blank">https://postgr.es/m/CACowWR07EDm7Y4m2kbhN_jnys%3DBBf9A676=
8RyQdKm_%3DNpkcaWg%40mail.gmail.com</a><br>
<br>
-- <br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
<a href=3D"https://www.2ndQuadrant.com/" rel=3D"noreferrer" target=3D"_blan=
k">https://www.2ndQuadrant.com/</a><br>
PostgreSQL Development, 24x7 Support, Remote DBA, Training &amp; Services<b=
r>
</blockquote></div><br clear=3D"all"><div><div class=3D"gmail_default" styl=
e=3D"font-family:tahoma,sans-serif">That sounds like a possibility. Thanks =
Alvaro.</div><div class=3D"gmail_default" style=3D"font-family:tahoma,sans-=
serif"></div><div class=3D"gmail_default" style=3D"font-family:tahoma,sans-=
serif"><br></div><div class=3D"gmail_default" style=3D"font-family:tahoma,s=
ans-serif">Shira</div><br></div><div dir=3D"ltr"><div dir=3D"ltr"><div><div=
 dir=3D"ltr"><div><div dir=3D"ltr"><div style=3D"font-weight:bold;font-styl=
e:normal;font-variant:normal;line-height:20px;margin:0px"><br style=3D"colo=
r:rgb(0,0,0);font-family:Tahoma;font-size:13px;font-weight:normal;line-heig=
ht:normal"></div>
<div style=3D"padding-top:8px">
	=C2=A0</div></div></div></div></div></div></div>
</div>

--000000000000509290059c0e72c1--