Re: Composite Index question

Thank you for all your suggestions and answers.

- Deepak

On Wed, Oct 20, 2010 at 7:41 PM, mark <dvlhntr(at)gmail(dot)com> wrote:

>
> On 10/20/2010 05:43 PM, DM wrote:
> > Composite Index question:
> >
> > I have composite index on 3 columns on a table, by mistake the composite
> > index was created twice on the table.
> >
> > Will there any performance issues on this table because of the 2 same
> > composite indexes?
> >
> > Thanks
> > Deepak
>
> Are the indices of the same type (e.g. both BTree) but with different index
> names?
>
> Is the second composite index the columns in same order as the first ? if
> not in the same column order you might be seeing some benefit for some
> queries but this is dependent on the queries filter clauses. If so you
> might consider augmenting one or both of the indices to better suit your
> queries.
>
> From my experience, it appears to degrade performance because two indices
> have to be maintained. (not to mention also appears to be a waste of disk
> space) I am hopeful someone will correct me if I am wrong.
>
> Ours were from people explicitly creating indexes on columns that had
> indices implicitly created on them when the table was created. Cleanup was
> pretty easy and painless on our production systems.
>
> It's pretty easy to check for exact duplicates all over a given database as
> well as how often each index is being used.
> Check some of the queries here:
> http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html
>
> ..:Mark
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>