Technical Article | Sep 16, 2008

Why isn't PostgreSQL using my index?

by Frank Wiles

This is a common question I see from PostgreSQL users.  In fact, someone was just in IRC asking it and it prompted this post.  The exchange usually goes:

Steve: I have this column foo and I have an index on it, but when I do SELECT * FROM table WHERE foo = X, EXPLAIN doesn't use the index.  What am I doing wrong?

90% of the time the answer is unfortunately "Because the query optimizer is smarter than you are". Or maybe it's fortunately depending on how you think about it!

In this user's particular case he was mocking up a database schema and had only one row in the table he was querying against.  People who are more familiar with PostgreSQL will probably roll their eyes at the question, but if you put yourself in the user's shoes I can see how people would be confused by this.  They are thinking, "I put an index on there on purpose, why the hell isn't it working?"

PostgreSQL's query optimizer is smart, really smart and unless you have evidence otherwise you should trust what it is doing.  In this particular case, the optimizer realizes that if a table has only a few rows that using the index is actually slower than just spinning through the entire table.  Just because PostgreSQL isn't using your index today with a small number of rows, does not mean it won't choose to use it later when you have more data or the query changes. Because he was just mocking up a design he didn't have real world data, which is almost always a bad way to performance tune your system unless you are very familiar with how PostgreSQL behaves. 

Now there are other reasons why it might not be using the index.  If you have lots of data and the query you're running appears that it would benefit from the index, it might be a simple matter of forgetting to run an ANALYZE on the table or not having autovacuum turned on.  Until PostgreSQL re-analyzes your table it doesn't really "know" about that index to take it into account when building the query plan. 

While performance tuning PostgreSQL is much easier and better documented than in days gone by, it can still be very confusing and time consuming for the inexperienced.  If your business needs help tuning their system you might consider my PostgreSQL Tuning Service.

Recent Posts

Technical | Frank Wiles

Improved Django Tests

You write tests. Or at least you should be writing tests! As Jacob always says, "Code without tests is broken as designed."

Unfortunately you have to write a ton of boilerplate code to test even the simplest of things. This ...

Technical | Frank Wiles

Django Performance: 4 Simple Things

Optimizing for performance often comes with a bunch of headaches and hard problems, but it doesn’t always have to be that way.

Below are four simple things you can do to quickly and easily improve the performance of your ...

Technical | Frank Wiles

Wagtail 1.0 (beta) best Django CMS?

Now that the Wagtail CMS is gearing up for its 1.0 release, I wanted to take some time to introduce you to the all around best and most flexible Django CMS currently available. Wagtail has been around for a ...

All Posts

We are perfor­mance tuners, Django and PostgreSQL experts, infrastructure and scaling architects.

Learn More

2008-09-16T10:16:49 2013-11-27T14:22:59.617773 2008