Right now I have a postgresql 8.4 database set up for players in a multiplayer game. I want the username column to be unique. I also want to be able to lookup a player by username quickly. Here's the output of \d players
:
Table "public.players"
Column | Type | Modifiers
------------+---------+------------------------------------------------------
id | bigint | not null default nextval('players_id_seq'::regclass)
username | text | not null
trophies | integer |
Indexes:
"pk_id" PRIMARY KEY, btree (id)
"players_username_key" UNIQUE, btree (username)
"players_trophies_index" btree (trophies DESC NULLS LAST)
"players_username_index" btree (username)
I'm not a DBA, so bear with me. This seems like an inefficient use of disk space by having two indices on the username column: one for uniqueness and one for fast lookup. Is it possible to combine them into one index, maintaining uniqueness and fast lookup? If so, are there any drawbacks to such an approach?
Best Answer
You only need a single index, the unique one. Queries to lookup by
username
will use the unique index. There's no advantage to having a second index on the same column marked as non-unique.You can verify that this by performing an
EXPLAIN
on the query. Connect to you database and compare the results of the following before and after you drop the index and confirm that they are the same:In both cases it should show something like: