How do I force Postgres to use a particular index?

Question

How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan?

Answer 1

A quick search on google resulted in the following newsgroup post: Post

set enable_seqscan=false;

Or

set enable_indexscan=true;

PS: Planner Method Configuration

Keep in mind though, 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.

Answer 2

I've used index hints in Oracle before, but it seems PostgreSQL favors query planner optimization over that feature, here's a post I found discussing some options:

Can you give Postgres index hints like you can do in Oracle and Sybase?

Answer 3

EnterpriseDB's PostgresPlus Advanced Server product supports Oracle hints syntax, though that product is not free.

Answer 4

The question on itself is very much invalid. Forcing (by doing enable_seqscan=off for example) is very bad idea. It might be useful to check if it will be faster, but production code should never use such tricks.

Instead - do explain analyze of your query, read it, and find out why PostgreSQL chooses bad (in your opinion) plan.

There are tools on the web that help with reading explain analyze output - one of them is explain.depesz.com - written by me.

Another option is to join #postgresql channel on freenode irc network, and talking to guys there to help you out - as optimizing query is not a matter of "ask a question, get answer be happy". it's more like a conversation, with many things to check, many things to be learned.