pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Default setting for enable_hashagg_disk
Date: Fri, 26 Jun 2020 01:11:54 +0200
Message-ID: <20200625231154.uknvrnqwmahv6rtu@development> (raw)
In-Reply-To: <e43828bbf4c1dc81ce8a38e9b769fd7bda45d5b4.camel@j-davis.com>
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>

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





view thread (91+ messages)  latest in thread

Message-ID: <20200625231154.uknvrnqwmahv6rtu@development>
Permalink:  ../20200625231154.uknvrnqwmahv6rtu@development/
Also on:    postgresql.org/message-id/20200625231154.uknvrnqwmahv6rtu@development

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, pgsql@j-davis.com, bruce@momjian.us, tgl@sss.pgh.pa.us, pryzby@telsasoft.com, dgrowleyml@gmail.com, robertmhaas@gmail.com, melanieplageman@gmail.com, pgsql-hackers@postgresql.org
  Subject: Re: Default setting for enable_hashagg_disk
  In-Reply-To: <20200625231154.uknvrnqwmahv6rtu@development>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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