Date: Thu, 21 May 2020 16:45:10 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Trouble with hashagg spill I/O pattern and costing
Message-ID: <20200521144510.2kz5rw2hjqdnf3iz@development>
References: <20200519151202.u2p2gpiawoaznsv2@development>
 <88dd39d782178539d7ee46f98b98579940e2a12e.camel@j-davis.com>
 <20200519175320.tl4qm6le5dbg2pcf@development>
 <acad6f2ba8d560b9dfa9a564c7b7d3162abf8347.camel@j-davis.com>
 <20200521001255.kfaihp3afv6vy6uq@development>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: <20200521001255.kfaihp3afv6vy6uq@development>
Precedence: bulk

On Thu, May 21, 2020 at 02:12:55AM +0200, Tomas Vondra wrote:
>
> ...
>
>I agree that's pretty nice. I wonder how far would we need to go before
>reaching a plateau. I'll try this on the other machine with temporary
>tablespace on SATA, but that'll take longer.
>

OK, I've managed to get some numbers from the other machine, with 75GB
data set and temp tablespace on SATA RAID. I haven't collected I/O data
using iosnoop this time, because we already know how that changes from
the other machine. I've also only done this with 128MB work_mem, because
of how long a single run takes, and with 128 blocks pre-allocation.

The patched+tlist means both pre-allocation and with the tlist tweak
I've posted to this thread a couple minutes ago:

                master       patched       patched+tlist
    -----------------------------------------------------
     sort         485            472                 462
     hash       24686           3060                 559

So the pre-allocation makes it 10x faster, and the tlist tweak makes it
5x faster. Not bad, I guess.

Note: I've slightly tweaked read-ahead on the RAID device(s) on those
patched runs, but the effect was pretty negligible (compared to other
patched runs with the old read-ahead setting).


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services