public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: Carlo Sganzerla <[email protected]>
To: [email protected]
Cc: Rafael Almeida <[email protected]>
Cc: Leandro Noman <[email protected]>
Subject: Re: GEQO plans much slower than standard join plans
Date: Tue, 28 Oct 2025 02:24:28 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com>
References: <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com>

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 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.

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=on costs will have much
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=on makes planning faster and execution slower, but maybe
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

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], [email protected]
  Subject: Re: GEQO plans much slower than standard join plans
  In-Reply-To: <[email protected]>

* 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