Re: Seeking reason behind performance gain in 12 with HashAggregate

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