Subject Re: [firebird-support] Optimizer tricks was Re: Count(id) returns null
Author David Johnson
On Wed, 2005-09-07 at 14:21 -0400, Ann W. Harrison wrote:
> > Ann W. Harrison wrote:
> >>
> >> ... Academic optimizers are full of little tricks ...
> >> How much code - and execution time - should we
> >>devote that effort?
> >>
> > Jason Dodson wrote:
> > Actually, these "little tricks" are what make some RDBMS's worth using.
> Do you have specific examples? The case that started this was
> WHERE 1 = 0 - a condition that can be evaluated at compile time as
> false. Is it actually worth testing whether both sides of an
> equality can be evaluated at compile time and performing the
> evaluation?
> >
> > Maybe a configuration parameter like UseStupidTricks=True/False.
> The optimizer is complex enough without adding stupid tricks and tests
> for whether they should be used. If you have specific kinds of dumb
> queries you think should be handled, lets consider them case by case.
> Regards,
> Ann

"Stupid tricks" are often used to suggest a better index to the
optimizer - especially when you know something about the business of the
data that the optimizer can't know. Simply including the "extra" column
in the where clause is often sufficient to allow the optimizer to look
at the index that the programmer feels should be used.

Usually this is predicated by awkward table designs, but we can't all
have the luxury of designing our databases from the ground up.