Re: automated index suggestor -- request for comment

On Fri, Dec 13, 2002 at 05:00:32PM +0000, Hannu Krosing wrote:
> On Fri, 2002-12-13 at 14:56, george young wrote:
> > > Of course you could just determine all possibly useful indexes
> > > and generate then anyhow an then drop them if they were not used
> > > ;)
> >
> > Why not! At least for selects, this seems like the ideal. For
> > insert and update, you have to deal with updating the superfluous
> > indexes -- does the planner include index updating in its work
> > estimates?

Well, i had a few reasons i didn't want to *actually* create the
indices:

1- Disk space. If it's evaluating all indices, including multi-column
indices, that ends up being a significant space drain.

2- Time. Creating indices can take a while for big tables (again,
moreso for multi-column indices).

3- Usability on running systems. If i can eliminate actual index
creation, it won't tie up disk access on systems that are already
dealing with high load.

> At least I think we don't optimize the plan for different index
> access patterns for updating indexes.

I don't think that's the case either, which makes it more difficult to
estimate negative cost of index creation. Not sure how i'll deal with
that except by (for now) ignoring it.

-johnnnnnnnnnnn