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

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.)

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.

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005