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