From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | New results for GEQO threshold |
Date: | 1999-02-11 01:25:56 |
Message-ID: | 12540.918696356@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ryan Bradetich | 1999-02-11 02:28:58 | interface libpq Makefile.in patch |
Previous Message | Bruce Momjian | 1999-02-10 23:29:33 | Re: [HACKERS] Multiples concatenation operator (||) |