Re: optimizing queries and indexes...

On Sun, 9 Sep 2001, Josh Berkus wrote:

> Ah. Well, you answer optimization questions so often that I'd assumed
> that you had a hand in it. Is the optimizer all Tom and Bruce's work?

Mostly Tom I believe.

> > At least on 7.1 and below, if you have a dummy value that is very
> > common
> > but doesn't really pass any information (like 'N/A' for example),
> > consider
> > using NULL instead. The optimizer statistics can often be thrown
> > off-kilter by values that are much more common than the real data.
>
> Personally, I cannot reccomend this. There are a number of
> normalization problems with using NULL instead of 'N/A' or 0 or another
> "no" value. Some database theorists (Fabian Pascal & co.) even propose
> the elimination of NULL from the SQL spec on the grounds that it
> encourages bad DB design. As such, I have a hard time reccommending any
> course that involves adding *more* NULLs to the database, especially for
> a marginal query performance gain.

NULLs are rather ugly, but the difference is often index scan vs sequence
scan and on billion row tables that starts being those messages about, I
do this query and it never comes back. This should become much less of an
issue in 7.2 however with Tom's enhancing of the statistics generated.