Review remove {join,from}_collapse_limit, add enable_join_ordering

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-hackers(at)postgresql(dot)org, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Subject: Review remove {join,from}_collapse_limit, add enable_join_ordering
Date: 2009-07-16 08:48:45
Message-ID: 200907161048.45311.andres@anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Robert, Hi all,

The patch applies cleanly and works as intended - no surprise here. After the
changes the documentation is at least as easy to understand as before and the
code changes look sensible
Also not surprisingly that's not the area I expected problems I guess ;-)

For performance testing I replayed query logs from sites I easily could get my
hands on (3 different, halfway interesting ones). I found no relevant
differences on the first site which is sensible because
{from,join}_collapse_limit wasn't reached anyway.
More interesting are the queries from the two sites having reporting queries:

On the first, simpler, schema I found on average 30% plan time increase and 40%
execution time decrease. Most of the queries stayed the same, only a few
changed radically (in both directions). No big differences between geqo=on/off.

The queries on the second reporting schema unfortunately are different. Its the
one were I copied the crazy example I attached in the original thread.
With geqo=off a good part of the queries used daily use too much memory to plan
sensibly and geqo=on outright fails with:
"Error: Failed to make a valid plan"
on some.
I stopped trying to make performance measurements there.
Noticeable even some plans which were plannable in reasonable time before now
are problematic with enable_join_ordering=false!

I agree that those queries are crazy, but I am not sure how many of those are
out there...

So, while I think the changes are principally a good idea, as
{from,join}_collapse_limit are a bit confusing options, I personally! do not
think geqo is ready for it today, especially as the benefit is relatively
small.

If I am the only one having access to such complicated queries its fine - I am
working on the sites query generation/schema anyway.

Could perhaps some other people having complicated queries check how they work
out with those changes? It should be enough to check with a very big
{join,from}_collapse_limit? Kevin?

I have also to admit that I somewhat like the current behaviour in theory.
Currently you can have a view with hand-optimized JOIN order which will not
get inlined and/or reordered use it together with something unoptimized and
the unoptimized part will be reordered in many cases...

I found it somewhat hard to review a patch were my meaning was biased from
beginning. As Tom listed himself listed himself as a reviewer I will happiliy
(err?) concede to his and your judgement.

Andres

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2009-07-16 09:00:07 Re: Synch Rep for CommitFest 2009-07
Previous Message Dimitri Fontaine 2009-07-16 08:29:04 Re: [GENERAL] pg_migrator not setting values of sequences?