AW: AW: [HACKERS] Some notes on optimizer cost estimates

> > > > Couldn't we test some of these parameters inside
> configure and set
> > > > them there?
> > >
> > > If we could figure out a reasonably cheap way of estimating these
> > > numbers, it'd be worth setting up custom values at
> installation time.
> >
> > Imho this whole idea is not so good. (Sorry)
> >
> > My points are:
> > 1. even if it is good for an optimizer to be smart,
> > it is even more important, that it is predictable
>
> ISTM that by the nature of things the most important capability of an
> optimizer is to yield optimal results.

Yes, but beleive me it cannot do that.
There are various reasons for that e.g.:
1. bad/old statistics
2. not considered concurrency issues
3. iterference from other applications
It will only lead a high percentage of optimal plans.
The rest ranges from not so good to far off. It is the responsibility of the
SQL programmer to avoid these, but he can only do that if the
optimizer is predictable.

> This, however, does
> not have to be
> mutually exclusive with predictability. If you estimate some
> CPU and disk
> parameters and write them into a config file, then you can always give
> this config file to a bug fixer. It will still work on his machine, just
> less than optimally.

That is an idea, yes. But I doubt that it is really worth it.
Imho it would be more important to consider concurrency
issues first, and that would be very hard to do.

e.g.

2 sessions doing seq scan on huge table x.
they start their query with a time offset, that
does not allow session 2 to use pages from session 1
buffer cache (not enough memory).
It would be optimal if session 1 would wait so long that
session 2 can read from cache.
They would both benefit from this strategy.

Andreas