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

At 09:51 26/01/00 +0100, Zeugswetter Andreas SB wrote:
>
>> > > > 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.

Based on experience with optimizer improvements across releases of DB
products (not PostgreSQL, I hastily add), I would be inclined to say (from
bitter experience) that no optimizer is ever truly predicatable. The SQL
programmer has to be given the tools to ensure that a 'bad' query can be
forced to run the same way with each release, and release notes should
indicate what extra strategies are now available, in case the 'bad' query
can be made better.

It gets my goat (a bit) when commercial DB manufacturers believe that they
can solve intractable optimization problems - it would be a pity for PGSQL
to go the same way. I'd love to have the opportunity to prove my point with
PGSQL, but since I can't affect the optimizers choices in any way, I am
left with rhetoric, and examples from commercial DBs, which aren't, really,
relevant.

Sorry about the chestnut.

>> 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
>
>************
>
>
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/