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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general