HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PostgreSQL ignoring (unique) index on TEXT field

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqluniquefieldtextignoringindex

Problem

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?

Solution

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.

Context

StackExchange Database Administrators Q#130038, answer score: 7

Revisions (0)

No revisions yet.