Re: GEQO plans much slower than standard join plans

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Carlo Sganzerla <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: [email protected]
Cc: Rafael Almeida <[email protected]>
Cc: Leandro Noman <[email protected]>
Subject: Re: GEQO plans much slower than standard join plans
Date: Wed, 29 Oct 2025 11:57:08 -0300
Message-ID: <CACWDQNULuzwQ43VGop5f7JUTOsVLofpMvLih_8vfoRdCJx6+pw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACWDQNX+mFG35dL+CP3BeXRjozJr8HKe3O=_ZPwSTaGQOHoizA@mail.gmail.com>
	<[email protected]>
	<CACWDQNU5DUCiC63KJhhuwQga6c=y2yXq+0wZd9J59wovxoAJng@mail.gmail.com>
	<[email protected]>

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

Sorry, I should have been clearer. With "better plans" I meant faster
execution times and with "faster plans" I mean faster planning times.

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

Yes, I also found that counterintuitive. By turning geqo = off
(join/from_collapse_limit were 14) and looking at some metrics we obtained
from pg_stat_statements, we found that planning times of the affected
queries (the ones which would make use of GEQO) mostly increased (but not
by much), which is already the documented behavior. However, one particular
query with the same structure like the one on treejoin-14-dims.sql
(attached to the first email) had a _decrease_ on planning and execution
times, so geqo = off for this query yielded faster plans than geqo = on.
This counterintuitive behavior is the reason that I created and shared the
script that tried to reproduce it. I'm not sure whether you've run it or
not, but on every machine I have run so far I had a 10 fold increase on TPS
and a 10 fold decrease on planning times if I turn geqo = off.

I guess what I'm trying to show is that, in very particular cases, GEQO may
harm planning times, that's why I felt a little disclaimer could be useful.
I'm not sure what else I can add here.

Regards,
Carlo


On Tue, Oct 28, 2025 at 11:05 PM Tomas Vondra <[email protected]> wrote:

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

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: <CACWDQNULuzwQ43VGop5f7JUTOsVLofpMvLih_8vfoRdCJx6+pw@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