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

Wed, 10 Feb 1999 20:25:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
New results for GEQO threshold