How do I force Postgres to use a particular index?

by

Assuming you’re asking about the common “index hinting” feature found in many databases, PostgreSQL doesn’t provide such a feature. This was a conscious decision made by the PostgreSQL team. A good overview of why and what you can do instead can be found here. The reasons are basically that it’s a performance hack that tends to cause more problems later down the line as your data changes, whereas PostgreSQL’s optimizer can re-evaluate the plan based on the statistics. In other words, what might be a good query plan today probably won’t be a good query plan for all time, and index hints force a particular query plan for all time.

As a very blunt hammer, useful for testing, you can use the enable_seqscan and enable_indexscan parameters. See:

These are not suitable for ongoing production use. If you have issues with query plan choice, you should see the documentation for tracking down query performance issues. Don’t just set enable_ params and walk away.

Unless you have a very good reason for using the index, Postgres may be making the correct choice. Why?

  • For small tables, it’s faster to do sequential scans.
  • Postgres doesn’t use indexes when datatypes don’t match properly, you may need to include appropriate casts.
  • Your planner settings might be causing problems.

See also this old newsgroup post.

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. Does PostgreSQL support “accent insensitive” collations?
  3. Index for finding an element in a JSON array
  4. Postgres unique constraint vs index
  5. List columns with indexes in PostgreSQL
  6. How do you do date math that ignores the year?
  7. What’s the proper index for querying structures in arrays in Postgres jsonb?
  8. PostgreSQL Index on JSON
  9. Sort by column ASC, but NULL values first?
  10. Using an Alias column in the where clause in Postgresql
  11. How to return result of a SELECT inside a function in PostgreSQL?
  12. Reset auto increment counter in postgres
  13. GROUP BY and aggregate sequential numeric values
  14. Safely rename tables using serial primary key columns
  15. How to concatenate columns in a Postgres SELECT?
  16. get JOIN table as array of results with PostgreSQL/NodeJS
  17. How do I determine the last day of the previous month using PostgreSQL?
  18. PL/pgSQL checking if a row exists
  19. Multiple CTE in single query
  20. Return pre-UPDATE column values using SQL only
  21. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  22. Selecting data into a Postgres array
  23. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  24. Get the distinct sum of a joined table column
  25. How much disk-space is needed to store a NULL value using postgresql DB?
  26. PostgreSQL convert columns to rows? Transpose?
  27. PostgreSQL delete with inner join
  28. Regular expression in PostgreSQL LIKE clause
  29. Postgres Error: More than one row returned by a subquery used as an expression
  30. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Leave a Comment