How to force Postgres to use a specified index?

Question

How do I force Postgres to use an index when it insists on performing a sequential scan?

Answer 1

Assuming you are asking about the "index hint" feature common in many databases, PostgreSQL does not provide such a feature. This is an intentional decision made by the PostgreSQL team. A good overview of why and what you can do can be found here. The basic reason is that this is a performance optimization method that tends to cause more problems when the data changes, and PostgreSQL's optimizer can re-evaluate the plan based on statistics. In other words, what might be a good query plan today may not be a good query plan all the time, and index hints force a specific query plan.

As a very crude tool, for testing purposes, you can use the enable_seqscan and enable_indexscan parameters. See:

• Check index usage
• enable_Parameters

These are not intended for sustained production use. If you encounter problems with query plan selection, you should review the documentation for tracking query performance issues. Don't just set the enable_ parameter and leave.

Unless you have a very good reason to use an index, Postgres will probably make the right choice. why?

• For small tables, sequential scanning is faster.
• Postgres does not use indexes when the data types do not match, you may need to include appropriate conversions.
• Your plan settings may be causing problems.

Also see this old newsgroup post.