PostgreSQL ignoring (unique) index on TEXT field

Question

I have a users table similar to

CREATE TABLE users (
  id bigserial NOT NULL,
  msisdn text NOT NULL
);

and a UNIQUE constraint defined on msisdn:

ALTER TABLE users ADD CONSTRAINT UNIQUE (msisdn);

Now, I tried running

EXPLAIN SELECT * FROM users WHERE msisdn = '000000000001';

and PostgreSQL is returning

Seq Scan on users  (cost=0.00..7.80 rows=1 width=400)
  Filter: (msisdn = '000000000001'::text)

The table has 256 rows at the moment. Could it be that the table is so small that PostgreSQL thinks it's better to do a full-table scan instead of scanning the index? How can I make sure that PostgreSQL actually uses the index instead of doing a full-table scan?

Answer 1

ypercubeᵀᴹ and a_horse_with_no_name were right:

I had too few rows in the table. For just 256 rows an index gives no benefit at all.

I added 100,000 extra rows and EXPLAIN started showing usage of the index.

Answer 2

Postgres The query planner will ignore the index if your table is small enough.

Sequential scans aint that bad after all, when there isn't enough data index scans are actually higer cost than sequential scans.