Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWDhs-002VUQ-Ev for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Aug 2023 10:24:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qWDhr-005m9j-5K for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Aug 2023 10:24:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWDhq-005m9K-QH for pgsql-hackers@lists.postgresql.org; Wed, 16 Aug 2023 10:24:50 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qWDhn-000SKp-B9 for pgsql-hackers@lists.postgresql.org; Wed, 16 Aug 2023 10:24:50 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-58c531d9a4aso28048537b3.1 for ; Wed, 16 Aug 2023 03:24:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1692181485; x=1692786285; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OfUOAcBg9ku0J4blGKAWg0JzRPbUpzPtZbu+Tcy+HMQ=; b=Ej4VO0KVpQa6/VBjU6XY7k/3jrhJCy3ArDt1WLMVejJotcz2TKPGiG5atB8N9RP/2/ HCuUOiuZKbhK1gZMxKMHG+7B9uiyVTOxG3vpfYzfgoC5POosfkJSXZEL8aalc7x9CfTs 1NnURQ0I2groUurbXZyZwnX3GO+kFH9RSqvPSCQJiehDMzN+xzxrEBVNgfioiYkmQTNu R+3+OX1fRpp0246Aw/CBCPOt0c9z4wNlmz531nD4yTTlaCELCNZRZPlg4pPnCVyXry5H majTIh78bilCkxNnl5roZQseAPwTzhHHoaDeWNAFtjFLkxA4SzhDO8VcvFgnZHvPWhBr 866A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692181485; x=1692786285; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=OfUOAcBg9ku0J4blGKAWg0JzRPbUpzPtZbu+Tcy+HMQ=; b=MWSplMlEZDMN6bybRKDF3557GOqAGBnBwypMQaFB/l/orfFtBqzomDbcbnlfsKZl7K JDTFfkZmPS0Px2+tBzVq85z6pT6lEMGGXnfuPp99KZZ9O7r/HHUZV2fx/hcx6wOLe9QB 1phyoDcSgfIQOOZywCHw/KWOSktSjwpJqlNNbaQ6m4gsumme6I4zwlUGhRZkHHJF5Y38 bPrn/biukK+o154ZJ/xu8J09abByX+Ju/F6CNOCJjtXXJQ9Lrn2e0jnsq/oAakcQDzS9 4WW9PuQGhMkuhfRRjzpqPdaNWBA1Q8wsV1HYu/dsKK7khTkPal8xPFgPOtBGZCwmPHIy 0tqg== X-Gm-Message-State: AOJu0Yxz/nWWjEqJzccQIH0DDoCS6Db/xghR1stPluAbZfaA0G940kwn JxB1uPs3NoXM8jD0XUw4CM5ahoqX3fy4SsjPYxPAgQ== X-Google-Smtp-Source: AGHT+IH5BZWwO3RTcm0tDq5QPG0qqqRo80bYXA2Hqyuw23WufPYJ/5PEhTGB9gm0Uzsk0FRl/k9HLUU2KulVXSdgnnw= X-Received: by 2002:a81:73d4:0:b0:58c:5598:be97 with SMTP id o203-20020a8173d4000000b0058c5598be97mr1736286ywc.15.1692181484923; Wed, 16 Aug 2023 03:24:44 -0700 (PDT) MIME-Version: 1.0 References: <83d80853-a45c-d85c-68eb-59acfe7fb5fb@gmail.com> <01248493-182b-0f92-f2f2-ff28dc4b2e83@gmail.com> <2040c902-32b1-46c8-0b26-d3f43a16b9bb@gmail.com> <12f6ef19-e06e-8ea9-ad98-3fd92afb7f0b@gmail.com> <8d0b24ce-f8ba-cd63-82ae-be853b4067e8@gmail.com> <4ef7f947-526b-c4ec-f7a0-8b4586e693e4@gmail.com> <8599211e-1ea9-8827-4b9b-adeb865656bd@gmail.com> <5314d2fc-76b6-c4ea-c083-1ef11411e5be@gmail.com> In-Reply-To: From: John Naylor Date: Wed, 16 Aug 2023 17:24:33 +0700 Message-ID: Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal order To: David Rowley Cc: pghackers Content-Type: multipart/alternative; boundary="0000000000004e03b9060307b44f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004e03b9060307b44f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jul 3, 2023 at 5:15=E2=80=AFPM David Rowley = wrote: > > On Fri, 30 Jun 2023 at 18:45, John Naylor wrote: > I looked over your patch and don't see anything to report aside from > the unfinished/undecided part around the tiebreak function for > tuplesort_begin_index_hash(). I went ahead and added a degenerate function, just for consistency -- might also head off possible alarms from code analysis tools. > I also ran the benchmark script [1] with the patch from [2] and > calculated the speedup with [2] with and without your v3 patch. I've > attached two graphs with the benchmark results. Any value >100% > indicates that performing the sort for the ORDER BY at the same time > as the WindowAgg improves performance, whereas anything < 100% > indicates a regression. The bars in blue show the results without > your v3 patch and the red bars show the results with your v3 patch. > Looking at the remaining regressions it does not really feel like > we've found the culprit for the regressions. Certainly, v3 helps, but > I just don't think it's to the level we'd need to make the window sort > changes a good idea. > > I'm not sure exactly how best to proceed here. I think the tiebreak > stuff is worth doing regardless, so maybe that can just go in to > eliminate that as a factor and we or I can continue to see what else > is to blame. Thanks for testing again. Sounds good, I removed a now-invalidated comment, pgindent'd, and pushed. -- John Naylor EDB: http://www.enterprisedb.com --0000000000004e03b9060307b44f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Mon, Jul 3, 2023 at 5:15=E2=80=AFPM David Rowley &l= t;dgrowleyml@gmail.com> wrot= e:
>
> On Fri, 30 Jun 2023 at 18:45, John Naylor <john.naylor@enterprisedb.com> = wrote:

> I looked over your patch and don't see anything= to report aside from
> the unfinished/undecided part around the tieb= reak function for
> tuplesort_begin_index_hash().

I went ahead= and added a degenerate function, just for consistency -- might also head o= ff possible alarms from code analysis tools.=C2=A0

> I also ran t= he benchmark script [1] with the patch from [2] and
> calculated the = speedup with [2] with and without your v3 patch. I've
> attached = two graphs with the benchmark results. Any value >100%
> indicates= that performing the sort for the ORDER BY at the same time
> as the = WindowAgg improves performance, whereas anything < 100%
> indicate= s a regression.=C2=A0 The bars in blue show the results without
> you= r v3 patch and the red bars show the results with your v3 patch.
> Lo= oking at the remaining regressions it does not really feel like
> we&= #39;ve found the culprit for the regressions.=C2=A0 Certainly, v3 helps, bu= t
> I just don't think it's to the level we'd need to mak= e the window sort
> changes a good idea.
>
> I'm not = sure exactly how best to proceed here. I think the tiebreak
> stuff i= s worth doing regardless, so maybe that can just go in to
> eliminate= that as a factor and we or I can continue to see what else
> is to b= lame.

Thanks for testing again. Sounds good, I removed a now-invalid= ated comment, pgindent'd, and pushed.
--0000000000004e03b9060307b44f--