Postgresql – ny index with O(1) complexity for lookup in PostgreSQL

I don't know the PostgreSQL engine, however in the real world, doing

WHERE X > y

will cause the engine to seek to the point in the B-tree index where X = y, then scan the rest of the data to return the relevant rows. With enough data, it will take significant time to return.

Clearly, WHERE X = y will return more quickly since it only has to return a single row, necessitating only the initial seek operation.

This is, of course, assuming X is indexed.

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 ...