Message-ID: <f9fd3172-03d4-4306-babe-a7c88c7cb32c@vondra.me>
Date: Wed, 29 Oct 2025 03:05:34 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: GEQO plans much slower than standard join plans
To: Carlo Sganzerla <carlo@alude.com.br>
Cc: pgsql-performance@postgresql.org, Rafael Almeida <rafael@alude.com.br>,
 Leandro Noman <leandro@alude.com.br>
References: 
 <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com>
 <3148f6f9-2429-4ee0-9b1e-860aa1578d4f@vondra.me>
 <CACWDQNU5DUCiC63KJhhuwQga6c=y2yXq+0wZd9J59wovxoAJng@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: 
 <CACWDQNU5DUCiC63KJhhuwQga6c=y2yXq+0wZd9J59wovxoAJng@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f9fd3172-03d4-4306-babe-a7c88c7cb32c%40vondra.me>
Precedence: bulk

On 10/28/25 16:43, Carlo Sganzerla wrote:
>> 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 entirely sure I follow what tests you did, some of which might
not have been shared on the list. Also, what do you mean by "better
plans, but also faster plans"? What's the difference?

It seems to me you're implying you get faster planning with geqo=off.
That seem counter-intuitive to me, but maybe it can happen. I however
don't see any example demonstrating that in the results you shared (and
I already suggested what data to look at).

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

Dunno. I'm not convinced geqo=on can increase planning time. Or maybe I
don't understand the results you've shared.


regards

-- 
Tomas Vondra