> 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).
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 PM Tomas Vondra <[email protected]> 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 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 > >
From 847757c28b7065ea35d1915f787e302295cea15c Mon Sep 17 00:00:00 2001 From: carlosganzerla <[email protected]> Date: Tue, 28 Oct 2025 12:39:05 -0300 Subject: [PATCH v1] doc: added addendum to GEQO GUC documentation Added an addendum which implies that GEQO may not always improve planning performance and by consequence that also may harm performance in some scenarios. The specific scenarios are out of scope here. --- doc/src/sgml/config.sgml | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 0a2a8b49fdb..a89f75230d2 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6190,9 +6190,9 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" <para> The genetic query optimizer (GEQO) is an algorithm that does query - planning using heuristic searching. This 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 + planning using heuristic searching. This usually 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. For more information see <xref linkend="geqo"/>. </para> @@ -6216,8 +6216,9 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" <para> Enables or disables genetic query optimization. This is on by default. It is usually best not to turn it off in - production; the <varname>geqo_threshold</varname> variable provides - more granular control of GEQO. + production. However, keep in mind that GEQO may not always produce + faster plans in every scenario; the <varname>geqo_threshold</varname> + variable provides more granular control of GEQO. </para> </listitem> </varlistentry> -- 2.51.1
