Andrew Sullivan wrote:
> Just because I'm one of those statistics true believers, what sort of
> information do you think it is possible for the DBA to take into
> consideration, when building a hint, that could not in principle be
> gathered efficiently by a statistics system? It seems to me that
> you're claiming that DBAs can have magic knowledge.
Is one example is the table of addresses clustered by zip-code
and indexes on State, City, County, etc?
The current statistics systems at least see no correlation between
these fields (since the alphabetical ordering of cities and
numbering of postal codes is quite different). This makes the
planner under-use the indexes because it sees no correlation and
overestimates the number of pages read and the random accesses
needed.
However since San Francisco, CA data happens to be tightly packed
on a few pages (since it shares the same few zip codes), few
pages are needed and mostly sequential access could be used
when querying SF data -- though the optimizer guesses most pages
in the table may be hit, so often ignores the indexes.
Now I'm not saying that a more advanced statistics system
couldn't one-day be written that sees these patterns in the
data -- but it doesn't seem likely in the near term. DBA-based
hints could be a useful interim work-around.