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 1qLgCr-000Pkk-P4 for pgsql-hackers@arkaria.postgresql.org; Tue, 18 Jul 2023 08:37:17 +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 1qLgCo-0065qm-JO for pgsql-hackers@arkaria.postgresql.org; Tue, 18 Jul 2023 08:37:14 +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 1qLgCo-0065qe-8s for pgsql-hackers@lists.postgresql.org; Tue, 18 Jul 2023 08:37:14 +0000 Received: from mail-wr1-f41.google.com ([209.85.221.41]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLgCg-0005wn-5y for pgsql-hackers@postgresql.org; Tue, 18 Jul 2023 08:37:13 +0000 Received: by mail-wr1-f41.google.com with SMTP id ffacd0b85a97d-3141c3a7547so5069659f8f.2 for ; Tue, 18 Jul 2023 01:37:07 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689669426; x=1692261426; 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=oxT04uTgPRKIJOzczY/Ixz8gptFwfrLNhuvBymhnbZA=; b=OaKxM1fxzQSn6mHFVOCvb7AATJualyYFFLnICtM8HM/7VIcYe2P4o8o8wDu+2xPZOe QHtJ0BUZ10wriydn46sBnAim/26u3Ddpj/sh1KK7XBfeOqVPMUqMaa5IBsleEpjvPrCo xOqbTyjzlNlB/q+K2B+y8acnl4rTeb18JtXtdKGphkYKT1U1WCe/OfVwNC/oMOwjnKk1 e0kWfX8mXv1VlqJetJss1VZEN4aC68pJ04wbwA5cxzGWiuaW/IXvSKkjvQRQwzSzlw9y zLKN1F/B9WVP8yQN405lavOcq++xr/9HhzBOPoRympKHUBOGRW6O1s1ZYJxF0Dz8ywXn fLew== X-Gm-Message-State: ABy/qLa9CJD8XE6qoS6/p8OOI7Wj3+81l86e4Iob8c1RQv3c5J18QQOy WNgfJFSV8BHJm8/2USOU6VxnxOJJ3pjsItBxsX0= X-Google-Smtp-Source: APBJJlGm6xFVP8JzlUqkptK876h0TmIofPnypb1cyq3CVCWlzNtNJT+lFcVzXSmSm4LWWIjTHRgyD6o5E7Y7z9zZZ+g= X-Received: by 2002:adf:ff87:0:b0:317:eea:bd10 with SMTP id j7-20020adfff87000000b003170eeabd10mr1572577wrr.29.1689669426183; Tue, 18 Jul 2023 01:37:06 -0700 (PDT) MIME-Version: 1.0 References: <20221221101846.7zsi7lscnm5ediik@alvherre.pgsql> <1350682.1671635927@sss.pgh.pa.us> <4191508.1674157166@sss.pgh.pa.us> <349124.1674185509@sss.pgh.pa.us> <20230207180855.xy5m4puwh5gzd7xy@awork3.anarazel.de> <1274885.1680558101@sss.pgh.pa.us> <9D23D8BB-83EE-451E-95C1-CF42ADB76869@yesql.se> In-Reply-To: From: Thom Brown Date: Tue, 18 Jul 2023 09:36:55 +0100 Message-ID: Subject: Re: generic plans and "initial" pruning To: Amit Langote Cc: Daniel Gustafsson , Tom Lane , Alvaro Herrera , Andres Freund , David Rowley , Jacob Champion , PostgreSQL Hackers , Robert Haas Content-Type: multipart/alternative; boundary="000000000000ef83a30600bed16f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ef83a30600bed16f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, 18 Jul 2023, 08:26 Amit Langote, wrote: > Hi Thom, > > On Tue, Jul 18, 2023 at 1:33=E2=80=AFAM Thom Brown wrote= : > > On Thu, 13 Jul 2023 at 13:59, Amit Langote > wrote: > > > In an absolutely brown-paper-bag moment, I realized that I had not > > > updated src/backend/executor/README to reflect the changes to the > > > executor's control flow that this patch makes. That is, after > > > scrapping the old design back in January whose details *were* > > > reflected in the patches before that redesign. > > > > > > Anyway, the attached fixes that. > > > > > > Tom, do you think you have bandwidth in the near future to give this > > > another look? I think I've addressed the comments that you had given > > > back in April, though as mentioned in the previous message, there may > > > still be some funny-looking aspects still remaining. In any case, I > > > have no intention of pressing ahead with the patch without another > > > committer having had a chance to sign off on it. > > > > I've only just started taking a look at this, and my first test drive > > yields very impressive results: > > > > 8192 partitions (3 runs, 10000 rows) > > Head 391.294989 382.622481 379.252236 > > Patched 13088.145995 13406.135531 13431.828051 > > Just to be sure, did you use pgbench --Mprepared with plan_cache_mode > =3D force_generic_plan in postgresql.conf? > I did. For full disclosure, I also had max_locks_per_transaction set to 10000. > > > Looking at your changes to README, I would like to suggest rewording > > the following: > > > > +table during planning. This means that inheritance child tables, whic= h > are > > +added to the query's range table during planning, if they are present > in a > > +cached plan tree would not have been locked. > > > > To: > > > > This means that inheritance child tables present in a cached plan > > tree, which are added to the query's range table during planning, > > would not have been locked. > > > > Also, further down: > > > > s/intiatialize/initialize/ > > > > I'll carry on taking a closer look and see if I can break it. > > Thanks for looking. I've fixed these issues in the attached updated > patch. I've also changed the position of a newly added paragraph in > src/backend/executor/README so that it doesn't break the flow of the > existing text. > Thanks. Thom > --000000000000ef83a30600bed16f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, 18 Jul 2023, 08:26 Amit Langote, <amitlangote09@gmail.com> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px= #ccc solid;padding-left:1ex">Hi Thom,

On Tue, Jul 18, 2023 at 1:33=E2=80=AFAM Thom Brown <thom@linux.com> w= rote:
> On Thu, 13 Jul 2023 at 13:59, Amit Langote <amitlangote09@gmai= l.com> wrote:
> > In an absolutely brown-paper-bag moment, I realized that I had no= t
> > updated src/backend/executor/README to reflect the changes to the=
> > executor's control flow that this patch makes.=C2=A0 =C2=A0Th= at is, after
> > scrapping the old design back in January whose details *were*
> > reflected in the patches before that redesign.
> >
> > Anyway, the attached fixes that.
> >
> > Tom, do you think you have bandwidth in the near future to give t= his
> > another look?=C2=A0 I think I've addressed the comments that = you had given
> > back in April, though as mentioned in the previous message, there= may
> > still be some funny-looking aspects still remaining.=C2=A0 In any= case, I
> > have no intention of pressing ahead with the patch without anothe= r
> > committer having had a chance to sign off on it.
>
> I've only just started taking a look at this, and my first test dr= ive
> yields very impressive results:
>
> 8192 partitions (3 runs, 10000 rows)
> Head 391.294989 382.622481 379.252236
> Patched 13088.145995 13406.135531 13431.828051

Just to be sure, did you use pgbench --Mprepared with plan_cache_mode
=3D force_generic_plan in postgresql.conf?

I did.
For full disclosure, I also had max_locks_per_tran= saction set to 10000.

> Looking at your changes to README, I would like to suggest rewording > the following:
>
> +table during planning.=C2=A0 This means that inheritance child tables= , which are
> +added to the query's range table during planning, if they are pre= sent in a
> +cached plan tree would not have been locked.
>
> To:
>
> This means that inheritance child tables present in a cached plan
> tree, which are added to the query's range table during planning,<= br> > would not have been locked.
>
> Also, further down:
>
> s/intiatialize/initialize/
>
> I'll carry on taking a closer look and see if I can break it.

Thanks for looking.=C2=A0 I've fixed these issues in the attached updat= ed
patch.=C2=A0 I've also changed the position of a newly added paragraph = in
src/backend/executor/README so that it doesn't break the flow of the existing text.

Thanks.

Th= om
--000000000000ef83a30600bed16f--