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 1vE7c6-005j5h-JK for pgsql-performance@arkaria.postgresql.org; Wed, 29 Oct 2025 14:57:26 +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 1vE7c5-001aFk-G7 for pgsql-performance@arkaria.postgresql.org; Wed, 29 Oct 2025 14:57:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vE7c5-001aFb-0F for pgsql-performance@lists.postgresql.org; Wed, 29 Oct 2025 14:57:24 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vE7c1-004Oyy-1k for pgsql-performance@postgresql.org; Wed, 29 Oct 2025 14:57:23 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-b6d5b756284so1585482866b.1 for ; Wed, 29 Oct 2025 07:57:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alude.com.br; s=google; t=1761749839; x=1762354639; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Z0vpNAvbW3mt4rvuN9bCcKF0edzwrHdwC3oJO2sHlaE=; b=nE0kk1IJyUloDqmeX8Or7OeF3dKY7dhqpY7f8vR8ETTy7+XVElq172GvxpDXS+MA53 njxKGaxeJIFGQBSgoMUghzlwjJGnZ7JEZru6fSlpTJgvgcfQv2KuCgKME+hBpXNwTOZP U6Qpd+9M4vY0SF/4fh2ju1Y1mtyZJ1cLQDMMDWayeUXp6Hb3qv5m5s/x9QlAu4amHJBY vXTkV36q5vvpcNKw1fS6PWBiSM9B0xx3VslSYMF4stH/z3UmtV0nO+AYRkThbWT4wMhw lTB2pk0yrZ32hUsx9T+uGXpelOs8QaD2YCjAO6z0sNCPL/Ld8nJBFpIJVVJcmnUYXmGv VwKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761749839; x=1762354639; 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=Z0vpNAvbW3mt4rvuN9bCcKF0edzwrHdwC3oJO2sHlaE=; b=WNhlBjJGT7eylanMUdeWgVNU2PuE5TzPK0kh7IubaeF71NNPVoh1e5U5rzIXtCFKaN 4kzz5mFpuUaMuWhBLLURmj2wtaFBTL2p/u6LUriX0xtor+9dOdAPxKF+xOhWe7zqExy6 0976qLLFvT8cMpDbiXG0oTeA6p+K2bP2Pmc6LPkvJVK6VlWfpcf+0oIF5hUOKrR11IJM W1H6lzE8BfEFtqoyCxUtJN6ABjt93OtfIlL8PuToR5hSvzuGDCxdnpC69SXxEwh8MJKl k2/D9AOLcIenDs3WuSH6wqJG7KebpFsxBhi+N+0YUzCkOWQlOFa5c7Ma35i8u1UPPvHW aGjw== X-Gm-Message-State: AOJu0YxjMC1sayrLFNsbx5eGKS/ndpBEvUlnj1PIfNmseeuADmEyduQm njN44fymIimDBYeVuN7W5rTu1PPyEj5M8PQv72Ioz5q18wdMRCqzZjuzFekO3GtqfTEER3g7zBD 19sCwD0q2Zf7M0vrRfrjhoY1RVwZJVMX6Br9CBgW8oA== X-Gm-Gg: ASbGncsiVLoIFG7gjNWvMjYQRjQDkNll7ioaoTVJXiQBULjOEusVGze5fGyeLZUC87T PwolVkwH74URdCuJR7MXOfDzmSAolKdufFFP6RwWyIPoWAUp7Jq7+PgNhsljlRTTGIVZfP2IuxE 1iogKP7OcQITXw3jYDBKlBHpu2AdbUqtNn+o0RMrOon/PcNJRi+YImKuuFnEhydyAclAl27494l UtBesjVW1f3T/sXryTCCFPcdMfrQnp4SOfNEDnI/dihmcWwo3/PoBE79g5mk56hdv9KBdtpwqcf RL4rdhmlKQMEbtYvVA== X-Google-Smtp-Source: AGHT+IFG5Dze3yGMR9np0CWGEVJLTSD1HubT1PZbhU+b1DIMZDxF3p1SEZsO9lICyXAtecSDxr9mptcoFjBuoR7dI10= X-Received: by 2002:a17:907:3e8b:b0:b3c:896:abdc with SMTP id a640c23a62f3a-b703d59a5e9mr313473666b.60.1761749839294; Wed, 29 Oct 2025 07:57:19 -0700 (PDT) MIME-Version: 1.0 References: <3148f6f9-2429-4ee0-9b1e-860aa1578d4f@vondra.me> In-Reply-To: From: Carlo Sganzerla Date: Wed, 29 Oct 2025 11:57:08 -0300 X-Gm-Features: AWmQ_bliuxOeh6AdLfgy_GA0eFR0lEU-VJHXyyxzj_vePHc0WO4Gug9zEPnsYa0 Message-ID: Subject: Re: GEQO plans much slower than standard join plans To: Tomas Vondra Cc: pgsql-performance@postgresql.org, Rafael Almeida , Leandro Noman Content-Type: multipart/alternative; boundary="0000000000005abee506424d5995" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005abee506424d5995 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > I'm not entirely sure I follow what tests you did, some of which might > not have been shared on the list. Also, what do you mean by "better > plans, but also faster plans"? What's the difference? Sorry, I should have been clearer. With "better plans" I meant faster execution times and with "faster plans" I mean faster planning times. > It seems to me you're implying you get faster planning with geqo=3Doff. > That seem counter-intuitive to me, but maybe it can happen. I however > don't see any example demonstrating that in the results you shared (and > I already suggested what data to look at). Yes, I also found that counterintuitive. By turning geqo =3D off (join/from_collapse_limit were 14) and looking at some metrics we obtained from pg_stat_statements, we found that planning times of the affected queries (the ones which would make use of GEQO) mostly increased (but not by much), which is already the documented behavior. However, one particular query with the same structure like the one on treejoin-14-dims.sql (attached to the first email) had a _decrease_ on planning and execution times, so geqo =3D off for this query yielded faster plans than geqo =3D on= . This counterintuitive behavior is the reason that I created and shared the script that tried to reproduce it. I'm not sure whether you've run it or not, but on every machine I have run so far I had a 10 fold increase on TPS and a 10 fold decrease on planning times if I turn geqo =3D off. I guess what I'm trying to show is that, in very particular cases, GEQO may harm planning times, that's why I felt a little disclaimer could be useful. I'm not sure what else I can add here. Regards, Carlo On Tue, Oct 28, 2025 at 11:05=E2=80=AFPM Tomas Vondra wro= te: > On 10/28/25 16:43, Carlo Sganzerla wrote: > >> Another question is whether the difference is in planning or execution= . > >> I'd expect geqo=3Don makes planning faster and execution slower, but m= aybe > >> that's not true for your test. It shouldn't be difficult to verify usi= ng > >> pg_stat_statements (which tracks both plan and exec time). > > > > We started experimenting and we already see some results that point out > > that we have not only better plans, but also faster plans. Our overall > > plan load was already somewhat low because of extensive use of prepared > > statements, so that in theory also reduces the load of not enabling > > GEQO. However, we tested a bit without prepared statements and had > > similar results as we did on the test. > > > > I'm not entirely sure I follow what tests you did, some of which might > not have been shared on the list. Also, what do you mean by "better > plans, but also faster plans"? What's the difference? > > It seems to me you're implying you get faster planning with geqo=3Doff. > That seem counter-intuitive to me, but maybe it can happen. I however > don't see any example demonstrating that in the results you shared (and > I already suggested what data to look at). > > > I'm not sure if I'm overstepping here, but wouldn't it be worthwhile to > > add a little disclaimer on the docs regarding such cases? I guess that > > the hard thing about elaborating database documentation is that you hav= e > > to document generically enough so the information makes sense to most > > reasonable workloads, so documenting every exception is not a good idea= , > > but on this case I feel that the docs gave too much the impression that > > GEQO *always* improves planning performance. I was thinking of adding a > > little "addendum". I've attached a patch for your consideration. > > > >> I'm not particularly familiar with the GEQO internals, so I can't poin= t > >> at specific issues. But I've heard from a couple experienced developer= s > >> that they consider GEQO ineffective / not the right approach. > > > > In my view, this "addendum" also leaves some room to these other > > considerations without compromising readability. > > > > I'd like to hear your thoughts on that. > > > > Dunno. I'm not convinced geqo=3Don can increase planning time. Or maybe I > don't understand the results you've shared. > > > regards > > -- > Tomas Vondra > > --0000000000005abee506424d5995 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> I'm not entirely sure I follow what tests you did= , some of which might
> not have been shared on the list. Also, what = do you mean by "better
> plans, but also faster plans"? Wha= t's the difference?

Sorry, I should have been clearer. With &quo= t;better plans" I meant faster execution times and with "faster p= lans" I mean faster planning times.

> It seems to me you'= ;re implying you get faster planning with geqo=3Doff.
> That seem cou= nter-intuitive to me, but maybe it can happen. I however
> don't = see any example demonstrating that in the results you shared (and
> I= already suggested what data to look at).

Yes, I also found that cou= nterintuitive. By turning geqo =3D off (join/from_collapse_limit were 14) a= nd looking at some metrics we obtained from pg_stat_statements, we found th= at planning times of the affected queries (the ones which would make use of= GEQO) mostly increased (but not by much), which is already the documented = behavior. However, one particular query with=C2=A0the same structure like t= he one on treejoin-14-dims.sql (attached to the first email) had a _decreas= e_ on planning and execution times, so geqo =3D off for this query yielded = faster plans than geqo =3D on. This counterintuitive behavior is the reason= that I created and shared the script that tried to reproduce it. I'm n= ot sure whether you've run it or not, but on every machine I have run s= o far I had a 10 fold increase on TPS and a 10 fold decrease on planning ti= mes if I turn geqo =3D off.

I guess what I'm trying to show is = that, in very particular cases, GEQO may harm planning times, that's wh= y I felt a little disclaimer could be useful. I'm not sure what else I = can add here.

Regards,
Carlo=C2=A0=C2=A0


<= div class=3D"gmail_quote gmail_quote_container">
On Tue, Oct 28, 2025 at 11:05=E2=80=AFPM Tomas Vondra <tomas@vondra.me> wrote:
On 10/28/25 16:43, Carlo Sganze= rla wrote:
>> Another question is whether the difference is in planning or execu= tion.
>> I'd expect geqo=3Don makes planning faster and execution slowe= r, but maybe
>> that's not true for your test. It shouldn't be difficult t= o verify using
>> pg_stat_statements (which tracks both plan and exec time).
>
> We started experimenting and we already see some results that point ou= t
> that we have not only better plans, but also faster plans. Our overall=
> plan load was already somewhat low because of extensive use of prepare= d
> statements, so=C2=A0that in theory also reduces the load of not enabli= ng
> GEQO. However, we tested a bit without prepared statements and had
> similar results as we did on the test.
>

I'm not entirely sure I follow what tests you did, some of which might<= br> not have been shared on the list. Also, what do you mean by "better plans, but also faster plans"? What's the difference?

It seems to me you're implying you get faster planning with geqo=3Doff.=
That seem counter-intuitive to me, but maybe it can happen. I however
don't see any example demonstrating that in the results you shared (and=
I already suggested what data to look at).

> I'm not sure if I'm overstepping here, but wouldn't it be = worthwhile to
> add a little disclaimer on the docs regarding such cases? I guess that=
> the hard thing about elaborating database documentation is that you ha= ve
> to document generically enough so the information makes sense to most<= br> > reasonable workloads, so documenting every exception is not a good ide= a,
> but on this case I feel that the docs gave too much the impression tha= t
> GEQO *always* improves planning performance. I was thinking of adding = a
> little "addendum". I've attached a patch for your consid= eration.
>
>> I'm not particularly familiar with the GEQO internals, so I ca= n't point
>> at specific issues. But I've heard from a couple experienced d= evelopers
>> that they consider GEQO ineffective / not the right approach.
>
> In my view, this "addendum" also leaves some room to these o= ther
> considerations without compromising readability.
>
> I'd like to hear your thoughts on that.
>

Dunno. I'm not convinced geqo=3Don can increase planning time. Or maybe= I
don't understand the results you've shared.


regards

--
Tomas Vondra

--0000000000005abee506424d5995--