public inbox for [email protected]
help / color / mirror / Atom feedFrom: Carlo Sganzerla <[email protected]>
To: [email protected]
Cc: Rafael Almeida <[email protected]>
Cc: Leandro Noman <[email protected]>
Subject: GEQO plans much slower than standard join plans
Date: Mon, 27 Oct 2025 15:17:23 -0300
Message-ID: <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com> (raw)
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
Attachments:
[application/sql] treejoin-10-dims.sql (437B, ../CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com/3-treejoin-10-dims.sql)
download
[application/sql] treejoin-14-dims.sql (604B, ../CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com/4-treejoin-14-dims.sql)
download
[application/sql] treejoin-create.sql (3.0K, ../CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com/5-treejoin-create.sql)
download
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: GEQO plans much slower than standard join plans
In-Reply-To: <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox