pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSeeking reason behind performance gain in 12 with HashAggregate
9+ messages / 5 participants
[nested] [flat]
* Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 16:29 Shira Bezalel <shira@sfei.org>
0 siblings, 2 replies; 9+ messages in thread
From: Shira Bezalel @ 2020-01-13 16:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 16:42 Justin Pryzby <pryzby@telsasoft.com>
parent: Shira Bezalel <shira@sfei.org>
1 sibling, 1 reply; 9+ messages in thread
From: Justin Pryzby @ 2020-01-13 16:42 UTC (permalink / raw)
To: Shira Bezalel <shira@sfei.org>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 17:34 Shira Bezalel <shira@sfei.org>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shira Bezalel @ 2020-01-13 17:34 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
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
www.sfei.org
Ph: 510-746-7304
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 18:07 Michael Lewis <mlewis@entrata.com>
parent: Shira Bezalel <shira@sfei.org>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Lewis @ 2020-01-13 18:07 UTC (permalink / raw)
To: Shira Bezalel <shira@sfei.org>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance
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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 20:44 Shira Bezalel <shira@sfei.org>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shira Bezalel @ 2020-01-13 20:44 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance
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
www.sfei.org
Ph: 510-746-7304
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 21:11 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Shira Bezalel <shira@sfei.org>
0 siblings, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2020-01-13 21:11 UTC (permalink / raw)
To: Shira Bezalel <shira@sfei.org>; +Cc: Michael Lewis <mlewis@entrata.com>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 21:45 Shira Bezalel <shira@sfei.org>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shira Bezalel @ 2020-01-13 21:45 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Michael Lewis <mlewis@entrata.com>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance
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
www.sfei.org
Ph: 510-746-7304
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-13 22:15 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Shira Bezalel <shira@sfei.org>
1 sibling, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2020-01-13 22:15 UTC (permalink / raw)
To: Shira Bezalel <shira@sfei.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Seeking reason behind performance gain in 12 with HashAggregate
@ 2020-01-14 00:11 Shira Bezalel <shira@sfei.org>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shira Bezalel @ 2020-01-14 00:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-01-14 00:11 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-13 16:29 Seeking reason behind performance gain in 12 with HashAggregate Shira Bezalel <shira@sfei.org>
2020-01-13 16:42 ` Justin Pryzby <pryzby@telsasoft.com>
2020-01-13 17:34 ` Shira Bezalel <shira@sfei.org>
2020-01-13 18:07 ` Michael Lewis <mlewis@entrata.com>
2020-01-13 20:44 ` Shira Bezalel <shira@sfei.org>
2020-01-13 21:11 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2020-01-13 21:45 ` Shira Bezalel <shira@sfei.org>
2020-01-13 22:15 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-01-14 00:11 ` Shira Bezalel <shira@sfei.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox