Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Consider upgrading PostgreSQL as it is rather old now... –  dezso Jul 18 '13 at 19:37
    
Yeah, will have to when I start using replication. –  Nat Weiss Jul 18 '13 at 20:33

2 Answers 2

up vote 4 down vote accepted

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:

EXPLAIN VERBOSE
SELECT *
FROM public.players
WHERE username = 'foobar'

In both cases it should show something like:

Index scan using using players_username_key on public.players (cost=...[truncated])
  Output: id, username, trophies ...
share|improve this answer
    
Thanks. In both cases, the cost was cost=0.00..2.41 rows=1 width=540. –  Nat Weiss Jul 18 '13 at 20:44
    
I expected the size of the database returned by SELECT pg_database_size() to decrease after dropping the unneeded index. Why did the database size stay the same? I should mention that there are only 35 rows currently. –  Nat Weiss Jul 18 '13 at 20:45
    
See here for a list of possibilities as to why the space isn't immediately reclaimed: dba.stackexchange.com/questions/16901/… –  sehrope Jul 18 '13 at 20:56

With index duplication you not only waste disk space, but also increase the overhead for changes to the table. Each additional index increases the time to add and delete rows, and may increase the time for updates.

This is the worst type of key duplication, and as noted only the Unique index is necessary. The entries in the other index will be unique, but may not be as efficiently stored, as the index needs to handle non-unique entries.

I also run across indexes which are a subset of another index. These are usually not required, although in some cases may be justifiable. Rarely have my tests shown significant query speed improvement from the extra index.

share|improve this answer
    
Thanks. It's helpful to recognize this as a duplicate index situation. –  Nat Weiss Jul 19 '13 at 2:39
    
@NatWeiss The first two paragraphs apply in your case, only the third extends the situation, but as you have a duplicate index, you may run into the situation noted in the third. –  BillThor Jul 20 '13 at 2:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.