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 1vDlrJ-00FZ4s-CY for pgsql-performance@arkaria.postgresql.org; Tue, 28 Oct 2025 15:43:41 +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 1vDlrI-00DnhX-55 for pgsql-performance@arkaria.postgresql.org; Tue, 28 Oct 2025 15:43:39 +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 1vDlrH-00DnhO-7M for pgsql-performance@lists.postgresql.org; Tue, 28 Oct 2025 15:43:38 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vDlrD-004EG3-0N for pgsql-performance@postgresql.org; Tue, 28 Oct 2025 15:43:36 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-b6d5e04e0d3so230843466b.2 for ; Tue, 28 Oct 2025 08:43:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alude.com.br; s=google; t=1761666212; x=1762271012; 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=C8nox03hE+OvpU1VTuXGu037wnTuhVw+6sLyo7Ya8Ow=; b=Abg+9JdhEcpsE+MO1ab8itemEJ7zDUhPaiSLvr+2OV1Kl3q7s9xHsPCgMwFUUd/FDN T8NeKr44jKUuLP7C4D05YyoOVukv9l2nQym4vgvjJwchvTuR84RgWcMw5iFtzpWG++r2 B3HJ+AtqGpX7GOjs9qhTg5wzl1BbFW+VMD9vJXPPMkHVPwQyHD3nKn/eDD9ohM9lJ/Ac Rkrmf1IO1SHfh0Yr+I7HxrrwJdMEsKrmuHpYEklukX1q98cC9kXAEuDR50Ks7xorEALy C2pngadVTxgmj56g8Z/PK3vZFxQKDdJlN/cjvMyeY1k+eIap0Fw+Tu7AvieNCsbkNzqJ 8jmg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761666212; x=1762271012; 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=C8nox03hE+OvpU1VTuXGu037wnTuhVw+6sLyo7Ya8Ow=; b=eDztIw6JqvMlVVER4ivIcxUs8YSjflr4ct8nns6zuvDlATsHgeeHog6iO8p4FaboIs EzFuKk7slQUzG60+lIAP1ZFVZ/ebL+4i/pvGa7ZvQbjlnyen7Eoc221yZHiS3tFI1ojf Sq/X+A/yAx4o4hyO15skMf6+EACCsAPB5yyVueqFPIllYa4RQBCYU18uqXMggGhDdFUg Kgya2GEzjJo9K2TFVj2yiOW6sYvYg1fF52VE2JbADvTs06Mnwk9k9uW5MIdxElnVpjMU 0i+55bLl5L9uuNquuAdZqBlXRduRffTyfif+eBtJL9Hiz6jiGbaFQVDtIEfRmVCERM9K 1p2w== X-Gm-Message-State: AOJu0Yw9NQsJBPQoSjSBw2jgmS8Hr4I13RR5nGBAmbEFVeP9ASk2s5PX TbclqQPXq4R3h4tncuDDlQHPs4ET2usqzcK+OCzghOPnahoK+1SytIyHWi22KZnR5DMgJ6dQSJs uhKhX3LN+XcdFNYiqQkeeJr58HStoXSkJO/d0918nsg== X-Gm-Gg: ASbGncsvskY4BIh6UICWM7dWI36wEcgaNDOgbQWlpzTW7ucu4+gQ2BWXhMtWHSbLSK8 3ld9GpvPTZDkbCWBjYwQt46MP9+VBeZRfUsVPJ7u7HcrpgCWeMwQSGkP4gp2vQhgfuWpc3z63Lp QN8tPFDnPCDIOIeXVR/QCvHSevqR2r/icWPAE4Uweer8qRXqbyI4EZNqwFkhhB8jCPmsSPIksLC 7AV15eS7IPCxzt5cRSo0QvUsG1h6PFJ8zXkIhbaUSnn0iOMFu7HJs5XVoKptSTlxZqi7WW0o9hd 1wKS49cynfCitx/vEQ== X-Google-Smtp-Source: AGHT+IGuLYvJawlCVDSXN+0W/vzJ73J1/D6qT6ravLs81fBgVXMva2Hb4udW70njvfyz73TBcxGFXNs+IXRf5c5OZs4= X-Received: by 2002:a17:906:f597:b0:b40:e687:c2c with SMTP id a640c23a62f3a-b70328db795mr1388866b.37.1761666211867; Tue, 28 Oct 2025 08:43:31 -0700 (PDT) MIME-Version: 1.0 References: <3148f6f9-2429-4ee0-9b1e-860aa1578d4f@vondra.me> In-Reply-To: <3148f6f9-2429-4ee0-9b1e-860aa1578d4f@vondra.me> From: Carlo Sganzerla Date: Tue, 28 Oct 2025 12:43:19 -0300 X-Gm-Features: AWmQ_bmfMJY6ErOOL6SpqdTCAAOHA_KQVCa8dhGSlWYgcTNI-h1bgbgBrZJmVcI 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/mixed; boundary="000000000000c5c336064239e0dc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c5c336064239e0dc Content-Type: multipart/alternative; boundary="000000000000c5c336064239e0da" --000000000000c5c336064239e0da Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > Another question is whether the difference is in planning or execution. > I'd expect geqo=3Don makes planning faster and execution slower, but mayb= e > that's not true for your test. It shouldn't be difficult to verify using > 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 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 have 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 point > at specific issues. But I've heard from a couple experienced developers > 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. Regards, Carlo On Mon, Oct 27, 2025 at 10:24=E2=80=AFPM Tomas Vondra wro= te: > On 10/27/25 19:17, Carlo Sganzerla wrote: > > > > I assume that the reason why the hierarchical "tree join" is much faste= r > > is due to the dependencies among tables, so the standard join search ha= s > > a much narrower range of possible query paths compared to the OLTP Star > > Join case. What surprised me, however, is that when GEQO is turned on, > > the TPS falls dramatically. Given that the documentation states that > > GEQO "... reduces planning time for complex queries (those joining many > > relations), at the cost of producing plans that are sometimes inferior > > to those found by the normal exhaustive-search algorithm", it made me > > wonder what could be the cause of this much slower planning. I'm not > > really familiar with genetic algorithms, so perhaps I might be missing > > something, but is this kind of planning performance hit normal when GEQ= O > > is on? I was hoping someone could help us on this topic. > > I'm not particularly familiar with the GEQO internals, so I can't point > at specific issues. But I've heard from a couple experienced developers > that they consider GEQO ineffective / not the right approach. > > However, I don't think you need detailed knowledge of the GEQO internals > to explain the observed behavior. > > The regular (non-GEQO) planning explores more or less all possible join > orders - we don't construct all of them thanks to dynamic programming, > but we only skip orders that we evaluate as not interesting. It's 100% > true, due to join_collapse_limit (which splits the problem into smaller > problems, and limits which part of the limit space we really search). > > The idea of GEQO is that it reduces the search space even more, and it > explores only a very small fraction of join orders. The idea was to do > that in a smart way to still produce "quality" join orders, but the > experience is it's not reliable. > > If shouldn't be difficult to demonstrate this using EXPLAIN. If you look > at the plans with/without geqo, I'd bet the geqo=3Don costs will have muc= h > higher cost (which proves the geqo fails to find many "good" plans). Of > course, the execution might still be fast. > > Another question is whether the difference is in planning or execution. > I'd expect geqo=3Don makes planning faster and execution slower, but mayb= e > that's not true for your test. It shouldn't be difficult to verify using > pg_stat_statements (which tracks both plan and exec time). > > > regards > > -- > Tomas Vondra > > --000000000000c5c336064239e0da Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> Another question is whether the difference is in plan= ning or execution.
> I'd expect geqo=3Don makes planning faster a= nd execution slower, but maybe
> that's not true for your test. I= t shouldn't be difficult to verify using
> pg_stat_statements (wh= ich 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 bec= ause of extensive use of prepared statements, so=C2=A0that in theory also r= educes the load of not enabling GEQO. However, we tested a bit without prep= ared statements and had similar results as we did on the test.

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 have to= document generically enough so the information makes sense to most reasona= ble workloads, so documenting every exception is not a good idea, but on th= is case I feel that the docs gave too much the impression that GEQO *always= * improves planning performance. I was thinking of adding a little "ad= dendum". I've attached a patch for your consideration.

> I'm not particularly familiar with the GEQO = internals, so I can't point
> at specific issues. But I've he= ard from a couple experienced developers
> that they consider GEQO in= effective / not the right approach.

In my view, this "addendum&= quot; also leaves some room to these other considerations without compromis= ing readability.

I'd like to hear your thoughts on that.

= Regards,
Carlo

On Mon, Oct 27, 2025 at 10:24=E2=80=AFPM Tomas Vondra = <tomas@vondra.me> wrote:
On = 10/27/25 19:17, Carlo Sganzerla wrote:
>
> I assume that the reason why the hierarchical "tree join" is= much faster
> is due to the dependencies among tables, so the standard join search h= as
> a much narrower range of possible query paths compared to the OLTP Sta= r
> Join case. What surprised me, however, is that when GEQO is turned on,=
> the TPS falls dramatically. Given that the documentation states that > GEQO "... reduces planning time for complex queries (those joinin= g many
> relations), at the cost of producing plans that are sometimes inferior=
> to those found by the normal exhaustive-search algorithm", it mad= e me
> wonder what could be the cause of this much slower planning. I'm n= ot
> really familiar with genetic algorithms, so perhaps I might be missing=
> something, but is this kind of planning performance hit normal when GE= QO
> is on? I was hoping someone could help us on this topic.

I'm not particularly familiar with the GEQO internals, so I can't p= oint
at specific issues. But I've heard from a couple experienced developers=
that they consider GEQO ineffective / not the right approach.

However, I don't think you need detailed knowledge of the GEQO internal= s
to explain the observed behavior.

The regular (non-GEQO) planning explores more or less all possible join
orders - we don't construct all of them thanks to dynamic programming,<= br> but we only skip orders that we evaluate as not interesting. It's 100%<= br> true, due to join_collapse_limit (which splits the problem into smaller
problems, and limits which part of the limit space we really search).

The idea of GEQO is that it reduces the search space even more, and it
explores only a very small fraction of join orders. The idea was to do
that in a smart way to still produce "quality" join orders, but t= he
experience is it's not reliable.

If shouldn't be difficult to demonstrate this using EXPLAIN. If you loo= k
at the plans with/without geqo, I'd bet the geqo=3Don costs will have m= uch
higher cost (which proves the geqo fails to find many "good" plan= s). Of
course, the execution might still be fast.

Another question is whether the difference is in planning or execution.
I'd expect geqo=3Don makes planning faster and execution slower, but ma= ybe
that's not true for your test. It shouldn't be difficult to verify = using
pg_stat_statements (which tracks both plan and exec time).


regards

--
Tomas Vondra

--000000000000c5c336064239e0da-- --000000000000c5c336064239e0dc Content-Type: text/x-patch; charset="US-ASCII"; name="v1-0001-doc-added-addendum-to-GEQO-GUC-documentation.patch" Content-Disposition: attachment; filename="v1-0001-doc-added-addendum-to-GEQO-GUC-documentation.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_mhaqifq30 RnJvbSA4NDc3NTdjMjhiNzA2NWVhMzVkMTkxNWY3ODdlMzAyMjk1Y2VhMTVjIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBjYXJsb3NnYW56ZXJsYSA8Y2FybG9zZ2FuenJsQGhvdG1haWwu Y29tPgpEYXRlOiBUdWUsIDI4IE9jdCAyMDI1IDEyOjM5OjA1IC0wMzAwClN1YmplY3Q6IFtQQVRD SCB2MV0gZG9jOiBhZGRlZCBhZGRlbmR1bSB0byBHRVFPIEdVQyBkb2N1bWVudGF0aW9uCgpBZGRl ZCBhbiBhZGRlbmR1bSB3aGljaCBpbXBsaWVzIHRoYXQgR0VRTyBtYXkgbm90IGFsd2F5cyBpbXBy b3ZlCnBsYW5uaW5nIHBlcmZvcm1hbmNlIGFuZCBieSBjb25zZXF1ZW5jZSB0aGF0IGFsc28gbWF5 IGhhcm0gcGVyZm9ybWFuY2UKaW4gc29tZSBzY2VuYXJpb3MuIFRoZSBzcGVjaWZpYyBzY2VuYXJp b3MgYXJlIG91dCBvZiBzY29wZSBoZXJlLgotLS0KIGRvYy9zcmMvc2dtbC9jb25maWcuc2dtbCB8 IDExICsrKysrKy0tLS0tCiAxIGZpbGUgY2hhbmdlZCwgNiBpbnNlcnRpb25zKCspLCA1IGRlbGV0 aW9ucygtKQoKZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9jb25maWcuc2dtbCBiL2RvYy9zcmMv c2dtbC9jb25maWcuc2dtbAppbmRleCAwYTJhOGI0OWZkYi4uYTg5Zjc1MjMwZDIgMTAwNjQ0Ci0t LSBhL2RvYy9zcmMvc2dtbC9jb25maWcuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvY29uZmlnLnNn bWwKQEAgLTYxOTAsOSArNjE5MCw5IEBAIEFOWSA8cmVwbGFjZWFibGUgY2xhc3M9InBhcmFtZXRl ciI+bnVtX3N5bmM8L3JlcGxhY2VhYmxlPiAoIDxyZXBsYWNlYWJsZSBjbGFzcz0iCiAKICAgICAg PHBhcmE+CiAgICAgICBUaGUgZ2VuZXRpYyBxdWVyeSBvcHRpbWl6ZXIgKEdFUU8pIGlzIGFuIGFs Z29yaXRobSB0aGF0IGRvZXMgcXVlcnkKLSAgICAgIHBsYW5uaW5nIHVzaW5nIGhldXJpc3RpYyBz ZWFyY2hpbmcuICBUaGlzIHJlZHVjZXMgcGxhbm5pbmcgdGltZSBmb3IKLSAgICAgIGNvbXBsZXgg cXVlcmllcyAodGhvc2Ugam9pbmluZyBtYW55IHJlbGF0aW9ucyksIGF0IHRoZSBjb3N0IG9mIHBy b2R1Y2luZwotICAgICAgcGxhbnMgdGhhdCBhcmUgc29tZXRpbWVzIGluZmVyaW9yIHRvIHRob3Nl IGZvdW5kIGJ5IHRoZSBub3JtYWwKKyAgICAgIHBsYW5uaW5nIHVzaW5nIGhldXJpc3RpYyBzZWFy Y2hpbmcuIFRoaXMgdXN1YWxseSByZWR1Y2VzIHBsYW5uaW5nIHRpbWUKKyAgICAgIGZvciBjb21w bGV4IHF1ZXJpZXMgKHRob3NlIGpvaW5pbmcgbWFueSByZWxhdGlvbnMpLCBhdCB0aGUgY29zdCBv ZgorICAgICAgcHJvZHVjaW5nIHBsYW5zIHRoYXQgYXJlIHNvbWV0aW1lcyBpbmZlcmlvciB0byB0 aG9zZSBmb3VuZCBieSB0aGUgbm9ybWFsCiAgICAgICBleGhhdXN0aXZlLXNlYXJjaCBhbGdvcml0 aG0uCiAgICAgICBGb3IgbW9yZSBpbmZvcm1hdGlvbiBzZWUgPHhyZWYgbGlua2VuZD0iZ2VxbyIv Pi4KICAgICAgPC9wYXJhPgpAQCAtNjIxNiw4ICs2MjE2LDkgQEAgQU5ZIDxyZXBsYWNlYWJsZSBj bGFzcz0icGFyYW1ldGVyIj5udW1fc3luYzwvcmVwbGFjZWFibGU+ICggPHJlcGxhY2VhYmxlIGNs YXNzPSIKICAgICAgICA8cGFyYT4KICAgICAgICAgRW5hYmxlcyBvciBkaXNhYmxlcyBnZW5ldGlj IHF1ZXJ5IG9wdGltaXphdGlvbi4KICAgICAgICAgVGhpcyBpcyBvbiBieSBkZWZhdWx0LiAgSXQg aXMgdXN1YWxseSBiZXN0IG5vdCB0byB0dXJuIGl0IG9mZiBpbgotICAgICAgICBwcm9kdWN0aW9u OyB0aGUgPHZhcm5hbWU+Z2Vxb190aHJlc2hvbGQ8L3Zhcm5hbWU+IHZhcmlhYmxlIHByb3ZpZGVz Ci0gICAgICAgIG1vcmUgZ3JhbnVsYXIgY29udHJvbCBvZiBHRVFPLgorICAgICAgICBwcm9kdWN0 aW9uLiBIb3dldmVyLCBrZWVwIGluIG1pbmQgdGhhdCBHRVFPIG1heSBub3QgYWx3YXlzIHByb2R1 Y2UKKyAgICAgICAgZmFzdGVyIHBsYW5zIGluIGV2ZXJ5IHNjZW5hcmlvOyB0aGUgPHZhcm5hbWU+ Z2Vxb190aHJlc2hvbGQ8L3Zhcm5hbWU+CisgICAgICAgIHZhcmlhYmxlIHByb3ZpZGVzIG1vcmUg Z3JhbnVsYXIgY29udHJvbCBvZiBHRVFPLgogICAgICAgIDwvcGFyYT4KICAgICAgIDwvbGlzdGl0 ZW0+CiAgICAgIDwvdmFybGlzdGVudHJ5PgotLSAKMi41MS4xCgo= --000000000000c5c336064239e0dc--