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

Re: automated index suggestor -- request for comment

From: johnnnnnn <john(at)phaedrusdeinus(dot)org>
To: Hannu Krosing <hannu(at)tm(dot)ee>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: automated index suggestor -- request for comment
Date: 2002-12-13 15:37:19
Message-ID: 20021213153719.GE8278@performics.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Jean-Luc LachanceDate: 2002-12-13 16:42:25
Subject: Re: [PERFORM] CLUSTER command
Previous:From: johnnnnnnDate: 2002-12-13 15:20:54
Subject: Re: automated index suggestor -- request for comment

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