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

At 13:17 24/01/00 -0500, Tom Lane wrote:
>Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> My points are:
>> 1. even if it is good for an optimizer to be smart,
>>     it is even more important, that it is predictable
>
>A good point indeed.  And unless we find that there is a huge range in
>the ratios across different machines, we'd be wasting our time trying to
>calibrate the numbers for a particular machine --- we could just as well
>use an average value.  The optimizer has many other, far worse, sources
>of error than that.
>
>> 2. I compile on test machine, production is completely different
>>     (more processors, faster disks and controllers)
>
>In practice we'd do this at initdb time, not configure time, so I'm
>not sure that that's really an issue.  But your other point is
>well taken.

I would guess it would become an issue if a server is upgraded (better/more
disks, faster CPU etc). This could be fixed by storing the optimizer
settings in a system table in the DB, and reading them the first time a
backend opens it. Just an idea.

If you *do* go with the 'store them in the DB' solution, then you also need
to provide an way of updating them (SQL, presumably), and a utility to
refresh them based on the current hardware.

What this then amounts to is 'hand-tuning' of optimizer settings, which is
an old chestnut that I would like to see reconsidered - sometimes, for
specific important queries, it is very good to be able to tell the DB how
to go about satisfying the query (or at least, what join order to use and
which indices to scan). Is this so far removed from the above,
philosophically? Is it worth considering?


Bye for now,

Philip Warner.

----------------------------------------------------------------
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   |/