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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
