CODEWITHSUNDEEP
postgresql
Leahcim
Leahcim

Reputation: 41919

query is slower after adding index

I have a books table in a postgres database that has a user_id column. When there was no index, querying all the books with a certain user_id took 0.607 ms

lesson4=# select * from books where user_id = 5;

Time: 0.607 ms

I then created an index like this

lesson4=# create index user_idx on books (user_id);
CREATE INDEX
Time: 43.288 ms

I then ran the same query and it took more than double the time

lesson4=# select * from books where user_id = 5;

Time: 1.397 ms

Note, there are only a few rows in the database. Not sure if that has an impact.

Did I create the index correctly? Why is it running slower now?

Upvotes: 0

Views: 2362

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

Your query is a little over three times as slow. Here's what is probably happening, assuming:

  1. The table is about 10 pages in length
  2. The table and the index both fit in RAM
  3. You are pulling a few records from different pages, but far less than 10% of the table.

The planner determines the index might help, so it does an index scan. This requires a tree search through the index, followed by a sequential scan through the relevant pages.

Without the index, you do a sequential scan through the ten pages, find the answer. Not only is the second faster, it is simpler.

This is why on PostgreSQL, indexes aren't the magic answer for performance problems. Usually it is worth noting that performance is approaching the danger zone before even thinking about adding an index.

Upvotes: 1

Related Questions