Why a simple index is not working in Postgres?

Question

I have a simple table and some sample data

create table test_index (
    id serial primary key,
    name char(255)
);
insert into test_index (name) values ('tom');
insert into test_index (name) values ('john');
insert into test_index (name) values ('ken');

After created the table and data, I created an index for name column

CREATE INDEX idx_test_index_shop_name ON test_index(name);

But when I do the simple query on name column

select * from test_index where name = 'tom';

Its not using the index, just scan through the whole table

It seems a simple thing, but I can't figure out why its not working, does anyone know what is the cause of it?

Update 1

I see the answer suggest this is small data hence it doesn't use index, so I can understand why its not using it here.

But I have the similar setup with the a char(255) column and added index of that column, but the table have 16 millions rows, and it also didn't use the index created, anyone know why?

Update 2

Here is the actual table with index but not using it when querying the table

Here is the verbose output

With just 3 rows (and you requesting all the columns) an index is a waste of time — Mat
– Mat, Commented Jun 10, 2022 at 18:48
You realize an index is not magic? That it is basically an auxiliary look table that incurs overhead when doing said lookup and that as consequence the faster solution for a small number of rows is a sequential scan. — Adrian Klaver
– Adrian Klaver, Commented Jun 10, 2022 at 18:52
It's working great, the database selects the best and fastest query plan: A sequential read on the table, ignoring any index. — Frank Heikens
– Frank Heikens, Commented Jun 10, 2022 at 18:58
This is an example of a larger table that I have 16 millions rows, its have similar setup and its still not using the index, hence to scale down data in here — vito huang
– vito huang, Commented Jun 10, 2022 at 19:27

Joshua D. Drake · Accepted Answer · 2022-06-10 19:10:07Z

1

The planner is estimating that with just three rows, it will be faster to pull the three rows from the heap (table). If it doesn't, it will go to the index, finding the matching pointers in the heap (table) and then go to the table to retrieve the data, making it slower. If you look at the example below, you will see that using the index is actually slower.

create table test_index (
id serial primary key,
name char(255)
);

insert into test_index (name) values ('tom');
insert into test_index (name) values ('john');
insert into test_index (name) values ('ken');
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1

CREATE INDEX idx_test_index_shop_name ON test_index(name);
CREATE INDEX



Seq Scan on test_index  (cost=0.00..1.04 rows=1 width=1028) (actual time=0.010..0.011 rows=1 loops=1)
   Filter: (name = 'tom'::bpchar)
   Rows Removed by Filter: 2
 Planning Time: 0.324 ms
 Execution Time: 0.032 ms

set enable_seqscan to off;

Index Scan using idx_test_index_shop_name on test_index  (cost=0.13..8.15 rows=1 width=1028) (actual time=0.044..0.046 rows=1 loops=1)
   Index Cond: (name = 'tom'::bpchar)
 Planning Time: 0.065 ms
 Execution Time: 0.072 ms

answered Jun 10, 2022 at 19:10

Joshua D. Drake

1,0125 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

vito huang Over a year ago

Thanks for the answer, I can see why its not using index in this situation, but this is an example of a larger table that I have 16 millions rows, its have similar setup and its still not using the index, hence to scale down data in here. BTW how did you force it to use index in your result above?

Frank Heikens Over a year ago

@vitohuang: Share your query plan over here, in your question that is, using EXPLAIN(ANALYZE, VERBOSE, BUFFERS)

vito huang Over a year ago

@FrankHeikens I've attached the query plan, but I don't know what's the buffers and how to use it, let me know if you can give me examples

Joshua D. Drake Over a year ago

If you have 16 million rows, please provide an EXPLAIN ANALYZE query... I have a guess but I need to see the results of that plan.

vito huang Over a year ago

@JoshuaD.Drake will be good to see what you think? btw I've added more verbose explain analyze

Collectives™ on Stack Overflow

Why a simple index is not working in Postgres?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related