Skip site navigation (1) Skip section navigation (2)

Re: automated index suggestor -- request for comment

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: automated index suggestor -- request for comment
Date: 2002-12-14 19:46:16
Message-ID: 1039895176.16976.26.camel@haggis (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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(dot)l(dot)johnson(at)cox(dot)net          |
| Jefferson, LA  USA  |
|                                                               |
| "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                                                   |

In response to

pgsql-performance by date

Next:From: Bruce MomjianDate: 2002-12-14 22:58:30
Subject: Re: [GENERAL] PerformPortalClose warning in 7.3
Previous:From: Tom LaneDate: 2002-12-14 04:48:38
Subject: Re: ~* + LIMIT => infinite time?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group