Index on unique field

Question

I have a users table and there is an index on that table:

add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree

The index type is btree and email is a unique field.
Is it worth having this index or is it better to just remove it?

score 1 · Answer 1 · answered Apr 10 '15 at 18:25

1

I would remove it first and then use lol_dba db:find_indexes from lol_dba to see if it recommends adding the index.

answered Apr 10 '15 at 18:25

Antarr Byrd

24,863
33
100
188

1

so this gem only tells you if you need to add an index? – Bri6ko Apr 10 '15 at 18:40
1

That's it's most important feature – Antarr Byrd Apr 11 '15 at 21:44

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

If the column is defined UNIQUE, it is indexed with a unique btree index automatically anyway, because that's how Postgres implements a UNIQUE constraint.

An additional index would be redundant, dead freight. Delete it.

Details:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

edited May 23 '17 at 12:21

Community

1
1

answered Apr 10 '15 at 19:02

Erwin Brandstetter

605,456
145
1,078
1,228

Index on unique field

2 Answers2