Re: Seeking reason behind performance gain in 12 with HashAggregate

--000000000000a0d8d8059c0c685d
Content-Type: text/plain; charset="UTF-8"

Thanks Tomas. I ran a vacuum full on the 9.6 table -- still no difference
in the query plan. The shared buffers hit went up slightly to 36069.

Shira

On Mon, Jan 13, 2020 at 1:12 PM Tomas Vondra <[email protected]>
wrote:

> On Mon, Jan 13, 2020 at 12:44:14PM -0800, Shira Bezalel wrote:
> >Hi Michael,
> >
> >I appreciate your question. I ran a vacuum analyze on the 9.6 table and it
> >yielded no difference. Same number of buffers were read, same query plan.
> >
>
> VACUUM ANALYZE won't shrink the table - the number of buffers will be
> exactly the same. You need to do VACUUM FULL, but be careful as that
> acquires exclusive lock on the table.
>
>
> regards
>
> --
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


-- 
Shira Bezalel
Database Administrator & Desktop Support Manager
San Francisco Estuary Institute
www.sfei.org
Ph: 510-746-7304

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

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:tahoma,s=
ans-serif">Thanks Tomas. I ran a vacuum full on the 9.6 table -- still no d=
ifference in the query plan. The shared buffers hit went up slightly to 360=
69.=C2=A0=C2=A0</div><div class=3D"gmail_default" style=3D"font-family:taho=
ma,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-family:=
tahoma,sans-serif">Shira</div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Mon, Jan 13, 2020 at 1:12 PM Tomas Vondra =
&lt;<a href=3D"mailto:[email protected]" target=3D"_blank">tomas=
[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex">On Mon, Jan 13, 2020 at 12:44:14PM -0800, Shira Beza=
lel wrote:<br>
&gt;Hi Michael,<br>
&gt;<br>
&gt;I appreciate your question. I ran a vacuum analyze on the 9.6 table and=
 it<br>
&gt;yielded no difference. Same number of buffers were read, same query pla=
n.<br>
&gt;<br>
<br>
VACUUM ANALYZE won&#39;t shrink the table - the number of buffers will be<b=
r>
exactly the same. You need to do VACUUM FULL, but be careful as that<br>
acquires exclusive lock on the table.<br>
<br>
<br>
regards<br>
<br>
-- <br>
Tomas Vondra=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
<a href=3D"http://www.2ndQuadrant.com" rel=3D"noreferrer" target=3D"_blank"=
>http://www.2ndQuadrant.com</a><br>
PostgreSQL Development, 24x7 Support, Remote DBA, Training &amp; Services <=
br>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div><fo=
nt color=3D"#666666" face=3D"tahoma, sans-serif" size=3D"2">Shira Bezalel=
=C2=A0</font></div><div><font color=3D"#666666" face=3D"tahoma, sans-serif"=
 size=3D"2">Database Administrator &amp; Desktop Support=C2=A0</font><span =
style=3D"color:rgb(102,102,102);font-family:tahoma,sans-serif;font-size:sma=
ll">Manager</span></div><div><font color=3D"#666666" face=3D"tahoma, sans-s=
erif" size=3D"2">San Francisco Estuary Institute</font></div><div><font col=
or=3D"#666666" face=3D"tahoma, sans-serif" size=3D"2"><a href=3D"http://www=
.sfei.org" target=3D"_blank">www.sfei.org</a></font></div><div><font color=
=3D"#666666" face=3D"tahoma, sans-serif" size=3D"2">Ph: 510-746-7304</font>=
</div></div><div style=3D"font-weight:bold;font-style:normal;font-variant:n=
ormal;line-height:20px;margin:0px"><br style=3D"color:rgb(0,0,0);font-famil=
y:Tahoma;font-size:13px;font-weight:normal;line-height:normal"></div>
<div style=3D"padding-top:8px">
	=C2=A0</div></div></div></div></div></div></div>

--000000000000a0d8d8059c0c685d--