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 1ppWYf-0005fw-7b for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Apr 2023 15:50:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppWYe-0004Pa-4C for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Apr 2023 15:50:52 +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 1ppWYd-0004PR-N6 for pgsql-hackers@lists.postgresql.org; Thu, 20 Apr 2023 15:50:51 +0000 Received: from mail-il1-x12f.google.com ([2607:f8b0:4864:20::12f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ppWYa-000tvP-Gb for pgsql-hackers@postgresql.org; Thu, 20 Apr 2023 15:50:50 +0000 Received: by mail-il1-x12f.google.com with SMTP id e9e14a558f8ab-32951864ad9so2653605ab.0 for ; Thu, 20 Apr 2023 08:50:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20221208.gappssmtp.com; s=20221208; t=1682005847; x=1684597847; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:from:to :cc:subject:date:message-id:reply-to; bh=TDYQl21g5V++Tw8+/PtagMRiAjGtWnuPEMnSriX+dMY=; b=kb9cB/k/IZ0RDEeo7xjbLnmeA8Gny5mUkzzG60q1BOLigYGg1R0HCvqLSU4XR1FzB9 2btEDz7mEt0Q/r9uwmqsRsHo33Sbg9JhqD4qKQ/eaTpPJraaFiziNFRJNhu2qIPUuqPn sqCp7A5GLgWwBnP96GjdJvgRGWR6jhtIG+QBudz0ZzaLh1gVnucK6zZpzPtOPVrPMatQ 4zXe8DvPOueoMFrFMwrDuhVTyJ9kTRthdezvqQFMdAz32nTyZvmZ0SRgm71UAsXE0/Xz kxC0nSuS5JHJrq9sTbC/6Zh1CkdQvUVXwmRSc5KEineHioY37tYzsrN1kVb2Hg79ySl7 dFHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1682005847; x=1684597847; h=in-reply-to:content-transfer-encoding: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=TDYQl21g5V++Tw8+/PtagMRiAjGtWnuPEMnSriX+dMY=; b=dDVQxn9EFwq+WDyQFQPwd51P9vI0QWBKgiNWAg0ixKEPusuT15rk555schA3072qdr RxVYT17iIm1V0bQrLqD9J34BEIr7E3Q5icGiJpnmsSt7twfdbz0L3wGdMkeNA3BTvsUV PoLCT2IEqHng4TYeFGc5pBRTuBQp58gfY+ibq3SQ8swmXmMRUPq25Tf0EBj6gfU4XDM7 ASLn5K7bNOdj7/K4HCaX9RxK/iFsq2qWhsCws4pip/bzAYPoYR4+cB/ZO5yGxkQA6KlW dqGBYs2VAgSsl4y1CKfISHTqLHA3BvOtGKZktEjjPWLm9y2ytqZwZEaQMSJpe5tGnU/F ZTmw== X-Gm-Message-State: AAQBX9fer2E+Kz8RwH2jCwfUEM4RpvJmIZl2PmIe5itHSty3lmE4E5la eqEgPsR4dDrvg99EplFJsoiDEA== X-Google-Smtp-Source: AKy350ZgPBowLZQ1TNVQJQaW0omBkOtOfYD5F8MSK6tf0YTpIiuLy9UtUZTkytM8avGorL7IaMzNOA== X-Received: by 2002:a92:d344:0:b0:32b:434a:6be3 with SMTP id a4-20020a92d344000000b0032b434a6be3mr1620435ilh.1.1682005847444; Thu, 20 Apr 2023 08:50:47 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id y9-20020a05663824c900b0040f9e66095fsm560948jat.134.2023.04.20.08.50.46 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 20 Apr 2023 08:50:46 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 5D99B80106F; Thu, 20 Apr 2023 10:50:45 -0500 (CDT) Date: Thu, 20 Apr 2023 10:50:45 -0500 From: Justin Pryzby To: Melanie Plageman Cc: Andres Freund , 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=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, Apr 19, 2023 at 08:47:07PM -0400, Melanie Plageman wrote: > On Wed, Apr 19, 2023 at 8:41 PM Justin Pryzby wrote: > > > > 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. > > While I don't know about other use cases, I would have used that here. > Do you still have that patch laying around? I'd be interested to at > least review it. https://commitfest.postgresql.org/41/3409/