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 1jbpml-0005KP-4N for pgsql-hackers@arkaria.postgresql.org; Thu, 21 May 2020 18:19:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jbpmh-000091-NP for pgsql-hackers@arkaria.postgresql.org; Thu, 21 May 2020 18:19:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jbpmh-00008u-BI for pgsql-hackers@lists.postgresql.org; Thu, 21 May 2020 18:19:11 +0000 Received: from mail-pf1-x435.google.com ([2607:f8b0:4864:20::435]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jbpmd-0002Dd-6j for pgsql-hackers@postgresql.org; Thu, 21 May 2020 18:19:09 +0000 Received: by mail-pf1-x435.google.com with SMTP id v63so3706895pfb.10 for ; Thu, 21 May 2020 11:19:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=j-davis-com.20150623.gappssmtp.com; s=20150623; h=message-id:subject:from:to:date:in-reply-to:references:mime-version :content-transfer-encoding; bh=dTvgq9+SHGJ9gLvFRjTnWjAwmqjW9Bp0g9riEhQp60g=; b=h7UmYwy+92PUQ/ul1SDoBI5vOIze/Zlv5Zz/GxDm4F3Md/TDw5F96FxXLqUtl6IcSR Q2r3MKGti/9Q2/A+ZxxbuGmlAIb2hRV/oa9Sa1VMkOkGYlqHHrQNYW6s/LEXSMjwbo/s wzaFpoYlbB+U2Sm0O0I+qJXN7UHYln7gR1QPhYtxpUaF1QK35oAGsb1CoeAoiL0n0tjJ zAgJS4qKhtwrTBQ73KPI0n6pppOMe20faVo2KdOJDI6GyM7mM6VF+6eB1bP9q//0AT9H Sf0xFp7fL4slsUAY7QkMoyWZzEdfbmouI+X/UUEYpXsJdtJ+DWaMSX9i/Y2d8hNVhVtj 964g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:subject:from:to:date:in-reply-to :references:mime-version:content-transfer-encoding; bh=dTvgq9+SHGJ9gLvFRjTnWjAwmqjW9Bp0g9riEhQp60g=; b=j7pLBaa/OrgCKunFHiuB1CDQK3V7Fj39xcR2Wf6EupKIwqHX3kmppP9Dup0gCfHzXp QniD3RYrOextHAIiKFjM/6p5BHbGi1N0SHsYVHC2F1PklwOaQTUTOw9ulIEcZywXx9br CWb0Eba8i3Yv7j70BYGENjDE/QXn6m1M3X7SM0gm+sPuKukbb9Th+N04kmppGbu0irAB J42SeFx0hCXynL4tLgAYDF9KoXhARp3hCMpvGS1RCEip2xR9ETtLhBSUGfMDmkyprsyG l0HJV5r4rBspiizZKPpi6LKqJZmzB63rJ/TIWWKw/nnsp2p/Vmb4x/Wx67mQsdYarw/g F3LA== X-Gm-Message-State: AOAM532EWwvkL8gkf0++19maAvdfSUsA6BZLx6LMIZK4gIL1nVuHmJvI 2VXCzH4bTSoGnx0UxCiGeeU2Rg== X-Google-Smtp-Source: ABdhPJzAcm00g1qgoK/N+IEF/E6rsMSL0SM/yKZi+w1AaEFrqpL/aAvIXqenGDXDVem4nuw+eFEqzg== X-Received: by 2002:a62:1512:: with SMTP id 18mr2367pfv.326.1590085145525; Thu, 21 May 2020 11:19:05 -0700 (PDT) Received: from jdavis.lan (c-73-231-146-4.hsd1.ca.comcast.net. [73.231.146.4]) by smtp.gmail.com with ESMTPSA id t8sm4713043pgn.81.2020.05.21.11.19.03 (version=TLS1_2 cipher=ECDHE-ECDSA-CHACHA20-POLY1305 bits=256/256); Thu, 21 May 2020 11:19:04 -0700 (PDT) Message-ID: Subject: Re: Trouble with hashagg spill I/O pattern and costing From: Jeff Davis To: Tomas Vondra , pgsql-hackers@postgresql.org Date: Thu, 21 May 2020 11:19:01 -0700 In-Reply-To: <20200521143040.zgn7ealdasfkmfcb@development> References: <20200519151202.u2p2gpiawoaznsv2@development> <20200521134122.3rpmdolam4fgc5pv@development> <20200521143040.zgn7ealdasfkmfcb@development> Content-Type: text/plain; charset="UTF-8" X-Mailer: Evolution 3.28.5-0ubuntu0.18.04.2 Mime-Version: 1.0 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, 2020-05-21 at 16:30 +0200, Tomas Vondra wrote: > OK, it seems the attached trivial fix (simply changing CP_LABEL_TLIST > to > CP_SMALL_TLIST) addresses this for me. Great! There were a couple plan changes where it introduced a Subquery Scan. I'm not sure that I understand why it's doing that, can you verify that it is a reasonable thing to do? Aside from that, feel free to commit it. Regards, Jeff Davis