MIME-Version: 1.0
From: Carlo Sganzerla <carlo@alude.com.br>
Date: Mon, 27 Oct 2025 15:17:23 -0300
Message-ID: 
 <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com>
Subject: GEQO plans much slower than standard join plans
To: pgsql-performance@postgresql.org
Cc: Rafael Almeida <rafael@alude.com.br>, Leandro Noman <leandro@alude.com.br>
Content-Type: multipart/mixed; boundary="000000000000eaf03a064227e9cb"
Archived-At: 
 <https://www.postgresql.org/message-id/CACWDQNX%2BmFG35dL%2BCP3BeXRjozJr8HKe3O%3D_ZPwSTaGQOHoizA%40mail.gmail.com>
Precedence: bulk

--000000000000eaf03a064227e9cb
Content-Type: multipart/alternative; boundary="000000000000eaf039064227e9c9"

--000000000000eaf039064227e9c9
Content-Type: text/plain; charset="UTF-8"

Hello experts,

Recently, we've been encountering poor query plans on our database workload.
We've successfully diagnosed the cause as reaching the default limit of 8
for join_collapse_limit.

We've already dealt with the situation successfully. However, I've been
discussing with some colleagues the possibility of raising the
join_collapse_limit parameter value (and from_collapse_limit with it) in
our production environment. I was trying to determine what would be a
sensible new value when I stumbled upon this thread on OLTP Star Joins:
https://www.postgresql.org/message-id/1ea167aa-457d-422a-8422-b025bb660ef3%40vondra.me

Our data model is hierarchical, so we rather have child tables instead of
the dimension tables mentioned above. I created a similar script as the one
found on the OLTP Star Join thread to test the effects of different values
of join_collapse_limit locally on an hierarchical model which is more
similar to ours. I ran pg_bench with 30 threads and 30 clients for 5
seconds. The following results were obtained on my machine (AMD Ryzen 7
5700U with 8 cores and 32 GB RAM, PostgreSQL 17.5 (Debian 17.5-1.pgdg120+1)
on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit):

OLTP starjoin (10 dimensions)
- join_collapse_limit = 1: 6.4k TPS
- join_collapse_limit = 8: 2.8k TPS
- join_collapse_limit = 11: 400 TPS

Tree join (10 levels)
- join_collapse_limit = 1:  7.4k TPS
- join_collapse_limit = 8: 4.7k TPS
- join_collapse_limit = 11:  3.9k TPS

Tree join (14 levels)
- join_collapse_limit = 1:  5.1k TPS
- join_collapse_limit = 8:  3.4k TPS
- join_collapse_limit = 11:  3k TPS
- join_collapse_limit = 14, geqo = off:  2.2k TPS
- join_collapse_limit = 14, geqo = on: 200 TPS

Note: geqo_threshold was unchanged from the default (12)

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 has 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 GEQO is on? I was hoping
someone could help us on this topic.

Regards,
Carlo

--000000000000eaf039064227e9c9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello experts,<br><br><span class=3D"gmail-">Recently,</sp=
an><span class=3D"gmail-"> we&#39;ve been encountering poor query plans on =
our database workload.</span><span class=3D"gmail-"> We&#39;ve successfully=
 diagnosed the cause as reaching the default limit of 8 for </span><span cl=
ass=3D"gmail-">join_collapse_limit</span><span class=3D"gmail-">.</span><br=
><br>We&#39;ve already dealt with the situation successfully. However, I=
9;ve been discussing with some colleagues the possibility of raising the jo=
in_collapse_limit parameter value (and from_collapse_limit with it) in our =
production environment. I was trying to determine what would be a sensible =
new value when I stumbled upon this thread on OLTP Star Joins:=C2=A0<a href=
=3D"https://www.postgresql.org/message-id/1ea167aa-457d-422a-8422-b025bb660=
ef3%40vondra.me">https://www.postgresql.org/message-id/1ea167aa-457d-422a-8=
422-b025bb660ef3%40vondra.me</a><br><br>Our data model is hierarchical, so =
we rather have child tables instead of the dimension tables mentioned above=
. I created a similar script as the one found on the OLTP Star Join thread =
to test the effects of different values of join_collapse_limit locally on a=
n hierarchical model which is more similar to ours. I ran pg_bench with 30 =
threads and 30 clients for 5 seconds. The following results were obtained o=
n my machine (AMD Ryzen 7 5700U with 8 cores and 32 GB RAM, PostgreSQL 17.5=
 (Debian 17.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian =
12.2.0-14) 12.2.0, 64-bit):<br><br>OLTP starjoin (10 dimensions)<br>- join_=
collapse_limit =3D 1: 6.4k TPS<br>- join_collapse_limit =3D 8: 2.8k TPS<br>=
- join_collapse_limit =3D 11: 400 TPS<br><br>Tree join (10 levels)<br>- joi=
n_collapse_limit =3D 1: =C2=A07.4k TPS<br>- join_collapse_limit =3D 8: 4.7k=
 TPS<br>- join_collapse_limit =3D 11: =C2=A03.9k TPS<br><br>Tree join (14 l=
evels)<br>- join_collapse_limit =3D 1: =C2=A05.1k TPS<br>- join_collapse_li=
mit =3D 8: =C2=A03.4k TPS<br>- join_collapse_limit =3D 11: =C2=A03k TPS<br>=
- join_collapse_limit =3D 14, geqo =3D off: =C2=A02.2k TPS<br>- join_collap=
se_limit =3D 14, geqo =3D on: 200 TPS<br><br>Note: geqo_threshold was uncha=
nged from the default (12)<br><br>I assume that the reason why the hierarch=
ical &quot;tree join&quot; is much faster is due to the dependencies among =
tables, so the standard join search has a much narrower range of possible q=
uery 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 &quot;... reduces planning time for complex=
 queries (those joining many relations), at the cost of producing plans tha=
t are sometimes inferior to those found by the normal exhaustive-search alg=
orithm&quot;, it made me wonder what could be the cause of this much slower=
 planning. I&#39;m not really familiar with genetic algorithms, so perhaps =
I might be missing something, but is this kind of planning performance hit =
normal when GEQO is on? I was hoping someone could help us on this topic.<b=
r><br>Regards,<br>Carlo<br></div>

--000000000000eaf039064227e9c9--

--000000000000eaf03a064227e9cb
Content-Type: application/sql; name="treejoin-10-dims.sql"
Content-Disposition: attachment; filename="treejoin-10-dims.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mh9ga5p81>
X-Attachment-Id: f_mh9ga5p81

XHNldCBpZCByYW5kb20oMSwxMDAwMCkKCnNldCBqb2luX2NvbGxhcHNlX2xpbWl0ID0gMTE7Cgpl
eHBsYWluIHNlbGVjdCAqIGZyb20gZgogICAgam9pbiBjMCBvbiAoYzAuaWQgPSBmLmMwX2lkKQog
ICAgam9pbiBjMSBvbiAoYzEuaWQgPSBjMC5jMV9pZCkKICAgIGpvaW4gYzIgb24gKGMyLmlkID0g
YzEuYzJfaWQpCiAgICBqb2luIGMzIG9uIChjMy5pZCA9IGMyLmMzX2lkKQogICAgam9pbiBjNCBv
biAoYzQuaWQgPSBjMy5jNF9pZCkKICAgIGpvaW4gYzUgb24gKGM1LmlkID0gYzQuYzVfaWQpCiAg
ICBqb2luIGM2IG9uIChjNi5pZCA9IGM1LmM2X2lkKQogICAgam9pbiBjNyBvbiAoYzcuaWQgPSBj
Ni5jN19pZCkKICAgIGpvaW4gYzggb24gKGM4LmlkID0gYzcuYzhfaWQpCiAgICBqb2luIGM5IG9u
IChjOS5pZCA9IGM4LmM5X2lkKQp3aGVyZSBmLmlkID0gOmlkOwo=
--000000000000eaf03a064227e9cb
Content-Type: application/sql; name="treejoin-14-dims.sql"
Content-Disposition: attachment; filename="treejoin-14-dims.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mh9ga5ow0>
X-Attachment-Id: f_mh9ga5ow0

XHNldCBpZCByYW5kb20oMSwxMDAwMCkKCnNldCBqb2luX2NvbGxhcHNlX2xpbWl0ID0gMTQ7CnNl
dCBnZXFvID0gb247CgpleHBsYWluIHNlbGVjdCAqIGZyb20gZgogICAgam9pbiBjMCBvbiAoYzAu
aWQgPSBmLmMwX2lkKQogICAgam9pbiBjMSBvbiAoYzEuaWQgPSBjMC5jMV9pZCkKICAgIGpvaW4g
YzIgb24gKGMyLmlkID0gYzEuYzJfaWQpCiAgICBqb2luIGMzIG9uIChjMy5pZCA9IGMyLmMzX2lk
KQogICAgam9pbiBjNCBvbiAoYzQuaWQgPSBjMy5jNF9pZCkKICAgIGpvaW4gYzUgb24gKGM1Lmlk
ID0gYzQuYzVfaWQpCiAgICBqb2luIGM2IG9uIChjNi5pZCA9IGM1LmM2X2lkKQogICAgam9pbiBj
NyBvbiAoYzcuaWQgPSBjNi5jN19pZCkKICAgIGpvaW4gYzggb24gKGM4LmlkID0gYzcuYzhfaWQp
CiAgICBqb2luIGM5IG9uIChjOS5pZCA9IGM4LmM5X2lkKQogICAgam9pbiBjMTAgb24gKGMxMC5p
ZCA9IGM5LmMxMF9pZCkKICAgIGpvaW4gYzExIG9uIChjMTEuaWQgPSBjMTAuYzExX2lkKQogICAg
am9pbiBjMTIgb24gKGMxMi5pZCA9IGMxMS5jMTJfaWQpCiAgICBqb2luIGMxMyBvbiAoYzEzLmlk
ID0gYzEyLmMxM19pZCkKd2hlcmUgZi5pZCA9IDppZDsKCg==
--000000000000eaf03a064227e9cb
Content-Type: application/sql; name="treejoin-create.sql"
Content-Disposition: attachment; filename="treejoin-create.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mh9gmg0z2>
X-Attachment-Id: f_mh9gmg0z2

ZHJvcCB0YWJsZSBpZiBleGlzdHMgZjsKZHJvcCB0YWJsZSBpZiBleGlzdHMgYzA7CmRyb3AgdGFi
bGUgaWYgZXhpc3RzIGMxOwpkcm9wIHRhYmxlIGlmIGV4aXN0cyBjMjsKZHJvcCB0YWJsZSBpZiBl
eGlzdHMgYzM7CmRyb3AgdGFibGUgaWYgZXhpc3RzIGM0Owpkcm9wIHRhYmxlIGlmIGV4aXN0cyBj
NTsKZHJvcCB0YWJsZSBpZiBleGlzdHMgYzY7CmRyb3AgdGFibGUgaWYgZXhpc3RzIGM3Owpkcm9w
IHRhYmxlIGlmIGV4aXN0cyBjODsKZHJvcCB0YWJsZSBpZiBleGlzdHMgYzk7CmRyb3AgdGFibGUg
aWYgZXhpc3RzIGMxMDsKZHJvcCB0YWJsZSBpZiBleGlzdHMgYzExOwpkcm9wIHRhYmxlIGlmIGV4
aXN0cyBjMTI7CmRyb3AgdGFibGUgaWYgZXhpc3RzIGMxMzsKCmNyZWF0ZSB0YWJsZSBjMTMgKGlk
IGludCBwcmltYXJ5IGtleSwgdmFsIHRleHQpOwpjcmVhdGUgdGFibGUgYzEyIChpZCBpbnQgcHJp
bWFyeSBrZXksIHZhbCB0ZXh0LCBjMTNfaWQgaW50IHJlZmVyZW5jZXMgYzEzKGlkKSk7CmNyZWF0
ZSB0YWJsZSBjMTEgKGlkIGludCBwcmltYXJ5IGtleSwgdmFsIHRleHQsIGMxMl9pZCBpbnQgcmVm
ZXJlbmNlcyBjMTIoaWQpKTsKY3JlYXRlIHRhYmxlIGMxMCAoaWQgaW50IHByaW1hcnkga2V5LCB2
YWwgdGV4dCwgYzExX2lkIGludCByZWZlcmVuY2VzIGMxMShpZCkpOwpjcmVhdGUgdGFibGUgYzkg
KGlkIGludCBwcmltYXJ5IGtleSwgdmFsIHRleHQsIGMxMF9pZCBpbnQgcmVmZXJlbmNlcyBjMTAo
aWQpKTsKY3JlYXRlIHRhYmxlIGM4IChpZCBpbnQgcHJpbWFyeSBrZXksIHZhbCB0ZXh0LCBjOV9p
ZCBpbnQgcmVmZXJlbmNlcyBjOShpZCkpOwpjcmVhdGUgdGFibGUgYzcgKGlkIGludCBwcmltYXJ5
IGtleSwgdmFsIHRleHQsIGM4X2lkIGludCByZWZlcmVuY2VzIGM4KGlkKSk7CmNyZWF0ZSB0YWJs
ZSBjNiAoaWQgaW50IHByaW1hcnkga2V5LCB2YWwgdGV4dCwgYzdfaWQgaW50IHJlZmVyZW5jZXMg
YzcoaWQpKTsKY3JlYXRlIHRhYmxlIGM1IChpZCBpbnQgcHJpbWFyeSBrZXksIHZhbCB0ZXh0LCBj
Nl9pZCBpbnQgcmVmZXJlbmNlcyBjNihpZCkpOwpjcmVhdGUgdGFibGUgYzQgKGlkIGludCBwcmlt
YXJ5IGtleSwgdmFsIHRleHQsIGM1X2lkIGludCByZWZlcmVuY2VzIGM1KGlkKSk7CmNyZWF0ZSB0
YWJsZSBjMyAoaWQgaW50IHByaW1hcnkga2V5LCB2YWwgdGV4dCwgYzRfaWQgaW50IHJlZmVyZW5j
ZXMgYzQoaWQpKTsKY3JlYXRlIHRhYmxlIGMyIChpZCBpbnQgcHJpbWFyeSBrZXksIHZhbCB0ZXh0
LCBjM19pZCBpbnQgcmVmZXJlbmNlcyBjMyhpZCkpOwpjcmVhdGUgdGFibGUgYzEgKGlkIGludCBw
cmltYXJ5IGtleSwgdmFsIHRleHQsIGMyX2lkIGludCByZWZlcmVuY2VzIGMyKGlkKSk7CmNyZWF0
ZSB0YWJsZSBjMCAoaWQgaW50IHByaW1hcnkga2V5LCB2YWwgdGV4dCwgYzFfaWQgaW50IHJlZmVy
ZW5jZXMgYzEoaWQpKTsKCmNyZWF0ZSB0YWJsZSBmIChpZCBpbnQgcHJpbWFyeSBrZXksICB2YWwg
dGV4dCwgYzBfaWQgaW50IHJlZmVyZW5jZXMgYzAoaWQpKTsKCgppbnNlcnQgaW50byBjMTMgc2Vs
ZWN0IGksIG1kNShpOjp0ZXh0KSBmcm9tIGdlbmVyYXRlX3NlcmllcygxLDEwMDApIHMoaSk7Cmlu
c2VydCBpbnRvIGMxMiBzZWxlY3QgaSwgbWQ1KGk6OnRleHQpLCBmbG9vcihyYW5kb20oKSAqIDEw
MDApOjppbnQgKyAxIGZyb20gZ2VuZXJhdGVfc2VyaWVzKDEsMTAwMCkgcyhpKTsKaW5zZXJ0IGlu
dG8gYzExIHNlbGVjdCBpLCBtZDUoaTo6dGV4dCksIGZsb29yKHJhbmRvbSgpICogMTAwMCk6Omlu
dCArIDEgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwKSBzKGkpOwppbnNlcnQgaW50byBjMTAg
c2VsZWN0IGksIG1kNShpOjp0ZXh0KSwgZmxvb3IocmFuZG9tKCkgKiAxMDAwKTo6aW50ICsgMSBm
cm9tIGdlbmVyYXRlX3NlcmllcygxLDEwMDApIHMoaSk7Cmluc2VydCBpbnRvIGM5IHNlbGVjdCBp
LCBtZDUoaTo6dGV4dCksIGZsb29yKHJhbmRvbSgpICogMTAwMCk6OmludCArIDEgZnJvbSBnZW5l
cmF0ZV9zZXJpZXMoMSwxMDAwKSBzKGkpOwppbnNlcnQgaW50byBjOCBzZWxlY3QgaSwgbWQ1KGk6
OnRleHQpLCBmbG9vcihyYW5kb20oKSAqIDEwMDApOjppbnQgKyAxIGZyb20gZ2VuZXJhdGVfc2Vy
aWVzKDEsMTAwMCkgcyhpKTsKaW5zZXJ0IGludG8gYzcgc2VsZWN0IGksIG1kNShpOjp0ZXh0KSwg
Zmxvb3IocmFuZG9tKCkgKiAxMDAwKTo6aW50ICsgMSBmcm9tIGdlbmVyYXRlX3NlcmllcygxLDEw
MDApIHMoaSk7Cmluc2VydCBpbnRvIGM2IHNlbGVjdCBpLCBtZDUoaTo6dGV4dCksIGZsb29yKHJh
bmRvbSgpICogMTAwMCk6OmludCArIDEgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwKSBzKGkp
OwppbnNlcnQgaW50byBjNSBzZWxlY3QgaSwgbWQ1KGk6OnRleHQpLCBmbG9vcihyYW5kb20oKSAq
IDEwMDApOjppbnQgKyAxIGZyb20gZ2VuZXJhdGVfc2VyaWVzKDEsMTAwMCkgcyhpKTsKaW5zZXJ0
IGludG8gYzQgc2VsZWN0IGksIG1kNShpOjp0ZXh0KSwgZmxvb3IocmFuZG9tKCkgKiAxMDAwKTo6
aW50ICsgMSBmcm9tIGdlbmVyYXRlX3NlcmllcygxLDEwMDApIHMoaSk7Cmluc2VydCBpbnRvIGMz
IHNlbGVjdCBpLCBtZDUoaTo6dGV4dCksIGZsb29yKHJhbmRvbSgpICogMTAwMCk6OmludCArIDEg
ZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwKSBzKGkpOwppbnNlcnQgaW50byBjMiBzZWxlY3Qg
aSwgbWQ1KGk6OnRleHQpLCBmbG9vcihyYW5kb20oKSAqIDEwMDApOjppbnQgKyAxIGZyb20gZ2Vu
ZXJhdGVfc2VyaWVzKDEsMTAwMCkgcyhpKTsKaW5zZXJ0IGludG8gYzEgc2VsZWN0IGksIG1kNShp
Ojp0ZXh0KSwgZmxvb3IocmFuZG9tKCkgKiAxMDAwKTo6aW50ICsgMSBmcm9tIGdlbmVyYXRlX3Nl
cmllcygxLDEwMDApIHMoaSk7Cmluc2VydCBpbnRvIGMwIHNlbGVjdCBpLCBtZDUoaTo6dGV4dCks
IGZsb29yKHJhbmRvbSgpICogMTAwMCk6OmludCArIDEgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwx
MDAwMCkgcyhpKTsKCmluc2VydCBpbnRvIGYKc2VsZWN0CiAgICBpLAogICAgbWQ1KGk6OnRleHQp
LAogICAgIGZsb29yKHJhbmRvbSgpICogMTAwMDApOjppbnQgKyAxCmZyb20gZ2VuZXJhdGVfc2Vy
aWVzKDEsMTAwMDApIHMoaSk7Cgp2YWN1dW0gYW5hbHl6ZTsK
--000000000000eaf03a064227e9cb--