Thread: Composite Index question

Hm. Run some queries; drop the second version of the index definition;
re-run the same queries; report to the group.  The redundant index isn't
helping, that much is certain.

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

If you can think of one benefit from having the redundant index then by
all means keep it.  It certainly eludes me.  Seems to me, removing an
un-necessary index on a huge table can only be a good thing.

On 10/20/2010 06:02 PM, DM wrote:
> Its a huge table in production, i dont want to take any risk.
>
> I can simulate and test this but i was to checking to see If any one
> knows off hand about this.
>
>
>
> I can simulate it but
> On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>     Hm. Run some queries; drop the second version of the index definition;
>     re-run the same queries; report to the group.  The redundant index isn't
>     helping, that much is certain.
>
>     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
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>

Sorry, I cannot prove or disprove any penalty.  I don't currently have
access to any postgres db of any serious magnitude.

On 10/20/2010 06:23 PM, DM wrote:
> I know there is no benfit of having duplicate indexes.
> Inorder for me to make change on production it requires lot of approvals
> and stuff.
>
> I wnat to know if there is any major performance drawback for having
> duplicate composite index, so that i can push hard for the change. Let
> me know.
>
> thanks for your looking into this.
>
>
> On Wed, Oct 20, 2010 at 5:10 PM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>     If you can think of one benefit from having the redundant index then by
>     all means keep it.  It certainly eludes me.  Seems to me, removing an
>     un-necessary index on a huge table can only be a good thing.
>
>     On 10/20/2010 06:02 PM, DM wrote:
>     > Its a huge table in production, i dont want to take any risk.
>     >
>     > I can simulate and test this but i was to checking to see If any one
>     > knows off hand about this.
>     >
>     >
>     >
>     > I can simulate it but
>     > On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent
>     <robjsargent@gmail.com <mailto:robjsargent@gmail.com>
>     > <mailto:robjsargent@gmail.com <mailto:robjsargent@gmail.com>>> wrote:
>     >
>     >     Hm. Run some queries; drop the second version of the index
>     definition;
>     >     re-run the same queries; report to the group.  The redundant
>     index isn't
>     >     helping, that much is certain.
>     >
>     >     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
>     >
>     >     --
>     >     Sent via pgsql-general mailing list
>     (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
>     >     <mailto:pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>>)
>     >     To make changes to your subscription:
>     >     http://www.postgresql.org/mailpref/pgsql-general
>     >
>     >
>
>

DM <dm.aeqa@gmail.com> writes:
> I know there is no benfit of having duplicate indexes.
> Inorder for me to make change on production it requires lot of approvals and
> stuff.

> I wnat to know if there is any major performance drawback for having
> duplicate composite index,

Of course there is: it doubles the index-update overhead every time you
update the table, in return for no benefit whatsoever.  Get rid of the
duplicate index.  (Now, if the table is seldom updated, it might not be
urgent to do so.  But you ought to plan on doing it.)

            regards, tom lane

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