patternsqlMinor
PostgreSQL ignoring (unique) index on TEXT field
Viewed 0 times
postgresqluniquefieldtextignoringindex
Problem
I have a
and a
Now, I tried running
and PostgreSQL is returning
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?
users table similar toCREATE 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
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.