I have a table with b-tree index on column A (non-unique). Now I want to add a check for uniqueness of column A and column B combination when inserting, so I want to add a unique composite index (A, B). Should I drop existing non-composite index? (queries in most cases use single index, as I have read)?

Will unique composite index be as effective as non-unique non-composite one for queries only on column A?

CodePudding user response：

If you have a lot of queries going for column A only in a where clause, then most likely you should keep the index on column A in addition to the new one.

The amount of queries which would use the index and the query cost difference are the 2 most important criteria for deciding whether or not to leave the index. As it depends on many factors like amount of content in the table and also query parameters, as Frank Heikens comment says, you can use the EXPLAIN ANALYZE statements to check important queries with and without the index to confirm your hypothesis.

CodePudding user response：

There is a very small probability it would make sense to keep both indexes. If the unique index is almost never exercised (because you never do inserts or non-HOT updates, or queries that benefit from both columns) and you have precisely the right amount of memory and memory usage patterns, then it is possible for the single-column index to be small enough to stay in cache while the composite would not be.

But most likely what would happen is that the composite index would be used at least enough of the time that both indexes would be fighting with each other for cache space, making it overall less effective.