Date: Fri, 26 Jun 2020 01:11:54 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>,
	Justin Pryzby <pryzby@telsasoft.com>,
	David Rowley <dgrowleyml@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	pgsql-hackers@postgresql.org
Subject: Re: Default setting for enable_hashagg_disk
Message-ID: <20200625231154.uknvrnqwmahv6rtu@development>
References: 
 <CA+TgmoY4-zSMFf8XbpO6uRsMX4vNPPLXvtajAZpG3f9eoEyjdA@mail.gmail.com>
 <129c8f1739957589584d9525e1d8782eae3f412f.camel@j-davis.com>
 <CAApHDvqFZikXhAGW=UKZKq1_FzHy+XzmUzAJiNj6RWyTHH4UfA@mail.gmail.com>
 <20200624090628.GA19640@momjian.us>
 <20200624123843.GX4107@telsasoft.com>
 <2214502.1593019796@sss.pgh.pa.us>
 <fae0c8abc541f90c9078c9ffaa2904caad6a20b7.camel@j-davis.com>
 <20200625192442.GD12486@momjian.us>
 <20200625195607.GA16766@momjian.us>
 <e43828bbf4c1dc81ce8a38e9b769fd7bda45d5b4.camel@j-davis.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: <e43828bbf4c1dc81ce8a38e9b769fd7bda45d5b4.camel@j-davis.com>
Precedence: bulk

On Thu, Jun 25, 2020 at 02:28:02PM -0700, Jeff Davis wrote:
>On Thu, 2020-06-25 at 15:56 -0400, Bruce Momjian wrote:
>> It is my understanding that spill of sorts is mostly read
>> sequentially,
>> while hash reads are random.  Is that right?  Is that not being
>> costed
>> properly?
>
>I don't think there's a major problem with the cost model, but it could
>probably use some tweaking.
>
>Hash writes are random. The hash reads should be mostly sequential (for
>large partitions it will be 128-block extents, or 1MB). The cost model
>assumes 50% sequential and 50% random.
>

The important bit here is that while the logical writes are random,
those are effectively combined in page cache and the physical writes are
pretty sequential. So I think the cost model is fairly reasonable.

Note: Judging by iosnoop stats shared in the thread linked by Jeff.

>Sorts are written sequentially and read randomly, but there's
>prefetching to keep the reads from being too random. The cost model
>assumes 75% sequential and 25% random.
>
>Overall, the IO pattern is better for Sort, but not dramatically so.
>Tomas Vondra did some nice analysis here:
>
>
>https://www.postgresql.org/message-id/20200525021045.dilgcsmgiu4l5jpa@development
>
>That resulted in getting the prealloc and projection patches in.
>
>Regards,
>	Jeff Davis
>
>

regards

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