Search Postgresql Archives

Re: Composite Index question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you for all your suggestions and answers.

- Deepak

On Wed, Oct 20, 2010 at 7:41 PM, mark <dvlhntr@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux