After Bruce's fine piece of detective work in finding a bogus keylist
comparison routine, the Postgres optimizer runs a *lot* faster than
before.
>> We have to bump the default value of GEQO threshold up again...
>> it's way too low now...
> Yes. I need to know what value to set it at. Do you have some way
> to test that.
I ran some variants of Charles Hornberger's multiway join that started
the whole discussion. Run times (with profiling on, but that shouldn't
affect the ratios much) now look like
GEQO off # Indexes available
# Tables 0 12 13 14 15 16
7 1.6 2.0
8 3.6 4.5 4.3
9 10.7 12.3 12.3
10 51.2 55.0 54.2
11 224.4 227.6 213.9
(For reference, the comparable run time for the 7t/12i case was 2630 sec
before Bruce fixed it! It's not every day that you see a 1300:1 speedup
from changing a couple lines of code...)
As you can see, the number of indexes is no longer a significant factor
in the optimizer's runtime. I therefore recommend that we revert the
GEQO threshold computation back to the way it was: just use the number
of tables involved. Simple, quick, easy to understand.
The next question is what the default GEQO threshold value ought to be.
I ran the same tests with and without GEQO; with GEQO on, the runtimes
look like
GEQO on # Indexes available
# Tables 0 12 13 14 15 16
7 9.4 12.3
8 17.8 22.8 23.1
9 45.9 61.9 59.6
10 58.5 74.9 72.9
11 71.6 79.3 77.9
So, assuming this is a reasonably representative case, it looks like
GEQO should kick in at a threshold of 11 tables.
regards, tom lane