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 1ppIMu-0003WQ-Bc for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Apr 2023 00:41:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppIMt-0005z5-87 for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Apr 2023 00:41:47 +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 1ppIMs-0005yw-Q5 for pgsql-hackers@lists.postgresql.org; Thu, 20 Apr 2023 00:41:46 +0000 Received: from mail-io1-xd30.google.com ([2607:f8b0:4864:20::d30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ppIMl-000mie-4K for pgsql-hackers@postgresql.org; Thu, 20 Apr 2023 00:41:45 +0000 Received: by mail-io1-xd30.google.com with SMTP id ca18e2360f4ac-7606d44dbb5so50050239f.1 for ; Wed, 19 Apr 2023 17:41:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20221208.gappssmtp.com; s=20221208; t=1681951297; x=1684543297; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=SQ/oY7K+PcToVkHmNH/qnIXtC518japeFe/2FI5A6kI=; b=BvRdtXI0WKq4q320TCXbMZS3ZrQfKbLUeHt4MPf3hD2gb3kcDGh9OcrvVOxHVPq6Sm fAgDWJdDrbjrZeaWwBVMfRXC29s3cEWzvW2OpVtYwOjLeRP4SHh3PK8AKd6GHrTz+9CI HMYoeR/auUpu0+PNqm4IpdevuuUHDQJoysH/dhmy24MBjwAPq/eA+xjz+BaAr3mSfFYv hadkjPaIZKkoWdcc0gxFwEFOwGRs1LXoeCDfHaXk/wFW6tWZMIC3zIPaFIAtrw19pEnm PSfiQoPylbmOmiHXd2KgEf5dlgxJGKFfhPwambm4sIInSKe5SqRqh3w4JFX+A4mRtzDH ppZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681951297; x=1684543297; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=SQ/oY7K+PcToVkHmNH/qnIXtC518japeFe/2FI5A6kI=; b=RAZGXOAnyJQnK30z/9vo7uNYxd357oA0xOZd+yP6ZhfOyg8rps3cqwHURYhjrqoEiz sfghxc46eskXGFYfwMsvbSyUA0ve1644voT9GeCuiOTc+7s7odBg2jN71+JI64usEYZI aMq+d2QvwNPdox00BF7M3c+J9BzE8hPzwkx6arW3ap2RtP2zhgvPWIGtcS+2xbuPShef IwnR+QJF5nnKBzFB9Uz+XOwpP88w38WiTnk/3iQ8kavDG3jyUUcMvK2UdTVK3mvzJPNK Ad2cxyLpHsI6x+JlxMjmz8BNLedzamzU223idbvVdGjIrFVIZgWUCtgB2hFrKTJImCLE vCGw== X-Gm-Message-State: AAQBX9enb7LkXdEpLkAWB7/d7WSIVsjH3fy76DJnRSJDIe82r7sQmBOG tNH4x/Sghe2FYLsvjpzKq5/6qQ== X-Google-Smtp-Source: AKy350b/+7eIOTLpf7DnLaV1EulkojdKzh24aAeGG/bScOIyOuCO4e4rcuUXGN+phS75YcM8c+Llrg== X-Received: by 2002:a6b:e91a:0:b0:760:b712:96d0 with SMTP id u26-20020a6be91a000000b00760b71296d0mr62826iof.8.1681951297537; Wed, 19 Apr 2023 17:41:37 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id x5-20020a5eda05000000b00740710c0a65sm62080ioj.47.2023.04.19.17.41.36 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 19 Apr 2023 17:41:36 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 654E980092B; Wed, 19 Apr 2023 19:41:35 -0500 (CDT) Date: Wed, 19 Apr 2023 19:41:35 -0500 From: Justin Pryzby To: Andres Freund Cc: Melanie Plageman , Thomas Munro , Richard Guo , pgsql-hackers@postgresql.org Subject: Re: Wrong results from Parallel Hash Full Join Message-ID: References: <20230412181452.cyfduw2ka2atqmii@awork3.anarazel.de> <20230419151704.xkkobswffutslj34@liskov> <20230419192051.xkgyj76akczpbjbs@awork3.anarazel.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20230419192051.xkgyj76akczpbjbs@awork3.anarazel.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, Apr 19, 2023 at 12:20:51PM -0700, Andres Freund wrote: > On 2023-04-19 12:16:24 -0500, Justin Pryzby wrote: > > On Wed, Apr 19, 2023 at 11:17:04AM -0400, Melanie Plageman wrote: > > > Ultimately this is probably fine. If we wanted to modify one of the > > > existing tests to cover the multi-batch case, changing the select > > > count(*) to a select * would do the trick. I imagine we wouldn't want to > > > do this because of the excessive output this would produce. I wondered > > > if there was a pattern in the tests for getting around this. > > > > You could use explain (ANALYZE). But the output is machine-dependant in > > various ways (which is why the tests use "explain analyze so rarely). > > I think with sufficient options it's not machine specific. It *can* be machine specific depending on the node type.. In particular, for parallel workers, it shows "Workers Launched: ..", which can vary even across executions on the same machine. And don't forget about "loops=". Plus: src/backend/commands/explain.c: "Buckets: %d Batches: %d Memory Usage: %ldkB\n", > We have a bunch of > EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) .. > in our tests. There's 81 uses of "timing off", out of a total of ~1600 explains. Most of them are in partition_prune.sql. explain analyze is barely used. I sent a patch to elide the machine-specific parts, which would make it easier to use. But there was no interest. -- Justin