Seeking reason behind performance gain in 12 with HashAggregate

--0000000000008111fb059c07fb70
Content-Type: text/plain; charset="UTF-8"

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 great,
but I'm just looking to understand why. Reading through the release notes
across all the new versions (10, 11, 12) hasn't yielded an obvious cause,
but maybe I missed something. Also, I realize it could be related to other
factors (config parameters, physical hosts, etc), but the systems are
pretty similar so just wondering about Postgres changes.

The query is the following:

SELECT pvc.value, SUM(pvc.count) AS sum
FROM
(SELECT (ST_ValueCount(cv.rast, 1)).*
FROM calveg_whrtype_20m AS cv) AS pvc
GROUP BY pvc.value

Here is the EXPLAIN (ANALYZE ON, BUFFERS ON) output from both systems:

9.6 plan <https://explain.depesz.com/s/W8HN>
12.1 plan <https://explain.depesz.com/s/lIRS>

In the 9.6 plan, the Seq Scan node produced 15,812 rows.
In the 12 plan, the Seq Scan produced 2,502 rows, and then the ProjectSet
node produced 15,812 rows.

Note that the table (calveg_whrtype_20m) in the two databases have the same
number of rows (2,502).

So it seems something about the introduction of the ProjectSet node between
the Seq Scan and HashAggregate is optimizing things...? Is this the right
conclusion to draw and if so, why might this be happening? Is there
something that was changed/improved in either 10, 11 or 12 that this
behavior can be attributed to?

Two more notes --

1. If I run the inner subquery without the outer sum/group by, the plans
between the two systems are identical.

2. As the calgeg_whrtype_20m table is a raster, I started my question on
the PostGIS list, but there was no obvious answer that the gain is related
to a change in the PostGIS code so I'm now turning to this list.

Thank you,
Shira

--0000000000008111fb059c07fb70
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"><div class=3D"gmail_default">Hi All,</div><div class=3D"gmail_de=
fault"><br></div><div class=3D"gmail_default">I&#39;m testing an upgrade fr=
om Postgres 9.6.16 to 12.1 and seeing a significant performance gain in one=
 specific query. This is really great, but I&#39;m just looking to understa=
nd why. Reading through the release notes across all the new versions (10, =
11, 12) hasn&#39;t yielded an obvious cause, but maybe I missed something. =
Also, I realize it could be related to other factors (config parameters, ph=
ysical hosts, etc), but the systems are pretty similar so just wondering ab=
out Postgres changes.</div><div class=3D"gmail_default"><br></div><div clas=
s=3D"gmail_default">The query is the following:</div><div class=3D"gmail_de=
fault"><br></div><div class=3D"gmail_default">SELECT pvc.value, SUM(pvc.cou=
nt) AS sum<br>FROM<br>(SELECT (ST_ValueCount(cv.rast, 1)).*<br>FROM calveg_=
whrtype_20m AS cv) AS pvc<br>GROUP BY pvc.value=C2=A0</div><div class=3D"gm=
ail_default"><br></div><div class=3D"gmail_default">Here is the EXPLAIN (AN=
ALYZE ON, BUFFERS ON) output from both systems:</div><div class=3D"gmail_de=
fault"><br></div><div class=3D"gmail_default"><div class=3D"gmail_default">=
<a href=3D"https://explain.depesz.com/s/W8HN" target=3D"_blank">9.6 plan</a=
></div><div class=3D"gmail_default"><a href=3D"https://explain.depesz.com/s=
/lIRS" target=3D"_blank">12.1 plan</a></div><div class=3D"gmail_default"><b=
r></div><div class=3D"gmail_default">In the 9.6 plan, the=20

Seq Scan=20

node produced 15,812 rows.=C2=A0<br></div><div class=3D"gmail_default">In t=
he=C2=A012 plan, the=20

Seq Scan produced 2,502 rows, and then the ProjectSet node produced 15,812 =
rows.=C2=A0</div><div class=3D"gmail_default"><br></div><div class=3D"gmail=
_default"><div class=3D"gmail_default"><div class=3D"gmail_default">Note th=
at the=20



table (calveg_whrtype_20m) in the two databases have the same number of row=
s (2,502).</div></div></div><div class=3D"gmail_default"><br></div><div cla=
ss=3D"gmail_default">So it seems something about the introduction of the Pr=
ojectSet node between the Seq Scan and HashAggregate is optimizing things..=
.? Is this the right conclusion to draw and if so, why might this be happen=
ing? Is there something that was changed/improved in either 10, 11 or 12 th=
at this behavior can be attributed to?=C2=A0</div><div class=3D"gmail_defau=
lt"><br></div><div class=3D"gmail_default">Two more notes --=C2=A0</div><di=
v class=3D"gmail_default"><br></div><div class=3D"gmail_default">1. If I ru=
n the inner subquery without the outer sum/group by, the plans between the =
two systems are identical.</div><div class=3D"gmail_default"><br></div><div=
 class=3D"gmail_default">2. As the calgeg_whrtype_20m table is a raster, I =
started my question on the=20

PostGIS=20

list, but there was no obvious answer that the gain is related to a change =
in the PostGIS code so I&#39;m now turning to this list.=C2=A0

</div><div class=3D"gmail_default"><br></div><div class=3D"gmail_default">T=
hank you,</div><div class=3D"gmail_default">Shira</div><div class=3D"gmail_=
default"><br></div></div></div></div>

--0000000000008111fb059c07fb70--