Re: Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a

On Wed, Dec 2, 2009 at 5:08 PM, Greg Sabino Mullane <greg(at)turnstep(dot)com> wrote:
>>> What about 14? Could we at least raise it to 14? 1/2 :)
>
>> I doubt we can raise it at all without lying to ourselves about the
>> likely results of so doing.  The GEQO planning times are in the low
>> double digits of milliseconds.  My apps typically have a budget of at
>> most ~200 ms to plan and execute the query, and I'm not always
>> operating on empty tables.
>
> Well, this might be addressed elsewhere, but it's not just the planning
> time, it's the non-repeatable plans when you hit geqo. That tends to
> drive my clients mad (as in very confused, and then angry). And the plans
> that geqo comes up with are seldom very good ones either. So yes, it's an
> adjustable knob, but I'd rather see it default to 0 or >= 14.

Actually, I think Tom made some changes for 8.5 that should eliminate
the randomness, if not the badness. Or am I misremembering?

One other thing I'm noticing about the current implementation is that
it seems to spend an entirely excessive amount of brain power
considering the best order in which to execute cross-joins. If I do
X, A JOIN B ON Pab JOIN C ON Pac JOIN D ON Pad JOIN E ON Pae, it looks
to me like join_search_one_level() will try joining X to each of A-E.
That seems fairly pointless; why would I ever want to join X to
anything other than {A B C D E}?

> Well, it's more a matter of consensus on the Right Thing To Do rather
> than a Simple Matter of Coding. Some of the more interesting conversations
> over the years has been on what to set the defaults to (random_page_cost
> anyone?). The conflict is then "real world anecdotes" versus "test-backed,
> data-driven numbers". It's hard to get real numbers on many things,
> especially when people are using Postgres on a staggeringly large collection
> of hardware, database size, activity, etc. There's always a balance to
> hit the sweet spot for many knobs (both in postgresql.conf and elsewhere)
> between benefitting the most people while adversely impacting the least
> number of people. The project has been very, very conservative in this
> respect, which is why they need people like me who keep pushing in the
> other direction. Even if I secretly agree with Tom 99% of the time. :)

Heh. Well, we did raise default_statistics_target quite a bit for
8.4. I suggested raising from_collapse_threshold,
join_collapse_threshold, and geqo_threshold, but diligent
experimenation by Tom and Andres Freund revealed this idea to suck. I
think it's an interesting area for more work to try to eliminate the
suckage, but I don't think we're there yet. I don't think I remember
the last round of debates about random_page_cost and seq_page_cost;
the current values probably are too high for most people, because
typically you've got a lot of stuff cached. We should maybe also
think about raising the default value for work_mem. It's hard for me
to believe that the average Postgres user wants a sort that takes more
than 1MB of memory to spill to disk; there certainly are people who
probably want that, but I doubt there are very many. I believe we've
been using that value for a decade, and memory size has increased a
lot in that time.

...Robert