Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jbqLX-0006Mj-6c for pgsql-hackers@arkaria.postgresql.org; Thu, 21 May 2020 18:55:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jbqLU-0000Aa-9a for pgsql-hackers@arkaria.postgresql.org; Thu, 21 May 2020 18:55:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jbqLU-0000AS-2Q for pgsql-hackers@lists.postgresql.org; Thu, 21 May 2020 18:55:08 +0000 Received: from mail-ed1-x542.google.com ([2a00:1450:4864:20::542]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jbqLQ-0004nm-RF for pgsql-hackers@postgresql.org; Thu, 21 May 2020 18:55:07 +0000 Received: by mail-ed1-x542.google.com with SMTP id bs4so7408078edb.6 for ; Thu, 21 May 2020 11:55:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=S8H7xSUPEVarLZpa43dCObKi+7oi1VJwbIex+1Rf0Nw=; b=XxAwJ2LwVqdA45C+v9wrtXVnkQZ59GwaWID8rU2xtcIyjHjPeNiAeJ8WDWhy5/bpDe ozeEEYXF2I/MwsAzqhLFYTTiPjeiF1TPNl4whA25BnXZArEtYT6RSqhKwYlnBDdqoGuh /yumyUd6ZmxZfNi9bAtakDmNFvBaJvGgi9bzjKnC1p87bjsVvWgn8s48K1Z57iEZ2TCZ Y44q2F+ghJWE9+WLVzuc7+1DZjbjJfQ0ISs5HXe0PcQjmnA5yxebQEBmb2H2CHMFKrjF GaFWO6y/txZsxszadZSe0PLIScq2O8xqvON/eYT2kltspJfgwkokKtKRfW7Aqeqk536I m/Cg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=S8H7xSUPEVarLZpa43dCObKi+7oi1VJwbIex+1Rf0Nw=; b=HIW6+J69jJpLbeLWv3f4Wg1yvHLhz+B0ksROk9q9SLNREYCtYDEhFl/6jDhjlzKNI7 bp/8iAFqi+fG+UFtLVCE0XZwiuEjHnLvOTltAYjBIskHSUSYmBQoX+dv1GPbJRCAJhnb R38Piy/Uc/j9l4oL9dLlyVaQFx/uxAXsqXe2143fhuhDkbyCQgdgQqnjbNIfZoE0yAdm soCv63t7Dwa6G8nALjaaTLPKIhXSzkqJ+i5bScczRRPoYc5I2HiakA5VkAeE7Vd/smTB Z2yvj8/Z6bB0WEfmd8TxrKZY7ENBVWfympzr1IS3l3uXNbY+xm1Pyo1xcf9ywKXNmfSQ qXaQ== X-Gm-Message-State: AOAM533GUq4SCGtVEBG65u6W+TqOjnnl4F06VFigCNQu0U5u6OywES8w niIySLDEXM+cqiLHjsbMSC5Rfg== X-Google-Smtp-Source: ABdhPJxzegd6k9rK/lZxr9CJpOXckEeOCNwTwUMcmntOS1qesu0KjDWRGIbIjlbzeYso6Tlu3kN3KA== X-Received: by 2002:a50:f0ce:: with SMTP id a14mr77464edm.131.1590087303023; Thu, 21 May 2020 11:55:03 -0700 (PDT) Received: from localhost (ip-86-49-253-141.net.upcbroadband.cz. [86.49.253.141]) by smtp.gmail.com with ESMTPSA id lr21sm5737720ejb.117.2020.05.21.11.55.01 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 21 May 2020 11:55:01 -0700 (PDT) Date: Thu, 21 May 2020 20:54:59 +0200 From: Tomas Vondra To: Jeff Davis Cc: pgsql-hackers@postgresql.org Subject: Re: Trouble with hashagg spill I/O pattern and costing Message-ID: <20200521185459.wrspycddukcztlgi@development> References: <20200519151202.u2p2gpiawoaznsv2@development> <20200521134122.3rpmdolam4fgc5pv@development> <20200521143040.zgn7ealdasfkmfcb@development> <20200521183405.gq5oaarqs5gtqugt@development> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: <20200521183405.gq5oaarqs5gtqugt@development> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, May 21, 2020 at 08:34:05PM +0200, Tomas Vondra wrote: >On Thu, May 21, 2020 at 11:19:01AM -0700, Jeff Davis wrote: > > ... > >I think we should do the pre-allocation patch too. I haven't tried yet >but I believe the tlist fix alone won't do nearly as good. > I've done some measurements on the smaller (SSD) machine, and the comparison looks like this: sort hash hash+prealloc+tlist hash+tlist -------------------------------------------------------- 4MB 331 478 188 330 128MB 222 434 210 350 The last column is master with the tlist tweak alone - it's better than hashagg on master alone, but it's not nearly as good as with both tlist and prealloc patches. I can't test this on the larger box with SATA temporary tablespace at the moment (other tests are running), but I believe the difference will be even more pronounced there. I don't think we're under a lot of pressure - beta1 is out anyway, so we have time to do proper testing first. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services