Thread: Seeking reason behind performance gain in 12 with HashAggregate
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
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:
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
On Mon, Jan 13, 2020 at 08:29:05AM -0800, Shira Bezalel wrote: > 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> > Is there something that was changed/improved in either 10, 11 or 12 that this > behavior can be attributed to? v12 has JIT enabled by default. You can test if that's significant with SET jit=off.
Hi Justin,
I'm seeing no difference in the query plan with JIT disabled in 12.1.
Thanks,
Shira
On Mon, Jan 13, 2020 at 8:42 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
On Mon, Jan 13, 2020 at 08:29:05AM -0800, Shira Bezalel wrote:
> 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>
> Is there something that was changed/improved in either 10, 11 or 12 that this
> behavior can be attributed to?
v12 has JIT enabled by default.
You can test if that's significant with SET jit=off.
Shira Bezalel
Database Administrator & Desktop Support Manager
San Francisco Estuary Institute
Ph: 510-746-7304
I am not at all familiar with PostGIS so perhaps this is a silly question, is bloat an issue on the older instance? Correlation isn't causation, but half the buffers scanned and half the runtime in the v12 plan has me curious why that might be.
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.
Thanks,
Shira
On Mon, Jan 13, 2020 at 10:07 AM Michael Lewis <mlewis@entrata.com> wrote:
I am not at all familiar with PostGIS so perhaps this is a silly question, is bloat an issue on the older instance? Correlation isn't causation, but half the buffers scanned and half the runtime in the v12 plan has me curious why that might be.
Shira Bezalel
Database Administrator & Desktop Support Manager
San Francisco Estuary Institute
Ph: 510-746-7304
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
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 <tomas.vondra@2ndquadrant.com> 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
Ph: 510-746-7304
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 great,
> 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 <sfrost@snowman.net>
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 to
indicate if it's ok for the call to finish before consuming all of the
source or destination buffers.
Author: Paul Ramsey
Reviewed-By: Rafia Sabih, Darafei Praliaskouski, Regina Obe
Discussion: https://postgr.es/m/CACowWR07EDm7Y4m2kbhN_jnys%3DBBf9A6768RyQdKm_%3DNpkcaWg%40mail.gmail.com
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Jan 13, 2020 at 2:15 PM Alvaro Herrera <alvherre@2ndquadrant.com> 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 great,
> 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 <sfrost@snowman.net>
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 to
indicate if it's ok for the call to finish before consuming all of the
source or destination buffers.
Author: Paul Ramsey
Reviewed-By: Rafia Sabih, Darafei Praliaskouski, Regina Obe
Discussion: https://postgr.es/m/CACowWR07EDm7Y4m2kbhN_jnys%3DBBf9A6768RyQdKm_%3DNpkcaWg%40mail.gmail.com
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
That sounds like a possibility. Thanks Alvaro.
Shira