Re: automated index suggestor -- request for comment

On Thu, 2002-12-12 at 21:22, johnnnnnn wrote:
> The manual is pretty sparse on advice regarding indices. Plenty of
> good feature documentation, but not much about when and where an index
> is appropriate (except a suggestion that multi-column indices should
> be avoided).
>
> Of course, the ultimate arbiter of which indices are used is the
> planner/optimizer. If i could somehow convince the optimizer to
> consider indices that don't yet exist, it could tell me which would
> give the greatest benefit should i add them.
>
> So, i'm writing for two reasons. First, i want to gauge interest in
> this tool. Is this something that people would find useful?
>
> Second, i am looking to solicit some advice. Is this project even
> feasible? If so, where would be the best place to start? My assumption
> has been that i would need to hack into the current code for
> determining index paths, and spoof it somehow, but is that possible
> without actually creating the indices?

Isn't this what a DBA (or, heck, even a modestly bright developer)
does during transactional analysis?

You know what the INSERTs and statements-that-have-WHERE-clauses
are, and, hopefully, approximately how often per day (or week)
each should execute.

Then, *you* make the decision about which single-key and multi-key
indexes should be created, based upon
a) the cardinality of each table
b) the frequency each query (includes UPDATE & DELETE) is run
c) how often INSERT statements occur

Thus, for example, an OLTP database will have a significantly
different mix of indexes than, say, a "reporting" database...
--
+---------------------------------------------------------------+
| Ron Johnson, Jr.        mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA      http://members.cox.net/ron.l.johnson  |
|                                                               |
| "My advice to you is to get married: If you find a good wife, |
| you will be happy; if not, you will become a philosopher."    |
|    Socrates                                                   |
+---------------------------------------------------------------+