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

At 10:05 26/01/00 -0600, Ross J. Reedstrom wrote:
>On Wed, Jan 26, 2000 at 08:07:17PM +1100, Philip Warner wrote:
>> 
>> 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.
>
>Ah, but you _can_ affect how the plans chosen, which in turn can affect
>the optimizer. Not as part of a running, production system, I grant you,
>but for debugging performance problems (and in particular, changes from
>one release to the next) it can be useful. What I'm talking about are
>the switches to the backend that tell pgsql not use particular kinds
>of joins/scans in planning a query
>
>>From postgres(1):
>
>       -f     Forbids the use of particular scan and  join  meth­
>              ods:  s  and  i  disable sequential and index scans
>              respectively, while n, m and h disable nested-loop,
>              merge   and   hash  joins  respectively.   (Neither
>              sequential scans nor nested-loop joins can be  dis­
>              abled  completely;  the  -fs and -fn options simply
>              discourage the  optimizer  from  using  those  plan
>              types if it has any other alternative.)

I think what I would ike to see is the flip-side to this: the ability to
force it to do, eg, nested-loop joins. But this is certainly a good start,
and it would be *great* if (as is suggested later in the thread), these
could be made runtime settings, and even better if they could be set for an
individual query.


>While not the whole ball of wax in terms of controlling the
>planner/optimizer stages, it does give you one more knob to tweak,
>beyond saying "This query took 2 sec. on release X, now it takes 2
>min. on release X+1"
>
>Perhaps someone (Phil?) could collect 'bad' queries, and run them against
>each release, and donate that part of the release notes to Bruce.

I would have though they might be better placed in the regression tests,
and issue a warning when a query is more than, say, 50% slower. The problem
with this is that you really only see performance problems on large tables,
so such tests would take a long time to construct & run. Nevertheless, I
like to idea of maintaining a collection of known 'bad' queries - I'll try
to track down the ones that caused me problems.


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