InterBase has a command, available in SQL, called SET STATISTICS. Executing this command allows you to tell the server to recompute the stored selectivity of the specified index. It works on both system-created and user-created indices. The computed selectivity is the number one factor in informing the optimizer as to whether or not the index will be useful for a particular query. But computing the selectivity takes a little bit of time, so it’s not done with every query. Instead, it’s done at certain times — I don’t know the complete list, but one example is when you restore a DB — and the value is stored in the system tables for use when you actually run a query.
Now some InterBase users may not be familiar with this command since in old versions of IB it didn’t do very much; the optimizer would insist on using certain indices no matter how bad the selectivity was. But that’s changed considerably in the past few releases; the optimizer in 7.5.1 is considerably smarter than in older versions, and it’s becoming very clear to me in my current optimization work that the IB-computed selectivity is important and frequently out of date in production databases which aren’t restored from backup frequently. The fix is very simple: Execute SET STATISTICS on the index in question. InterBase PLANalyzer even has a handy-dandy button on the PLAN analysis tab which will do this for you. You should do this on all indices in your query before doing any other optimization work as it may solve your problem entirely.
Post a Comment