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.
Subscribe to feed