Can I control postgresql to use a dedicated index?

Question

As we known, there'is a command called "force index" within mysql database. So this can force mysql to use an index whether optimizer like or not. And my confusion is that how to do the same thing on postgresql? Any reply will be appreciated.

Answer 1

There are no index hints in Postgres, and the core devs have no interest in adding any. The preferred approach is to fix the query planner when it does something silly.

If it's preferring a seq scan in your query, it can mean that:

• A seq scan is really better. Either because you're fetching most of your table, or because you're fetching rows absolutely all over the place.
• You didn't run analyze on your table; analyze table to fix.
• The stats collected on your table are inaccurate because the sample size is insufficient; alter table / set statistics to fix.
• You've run into planner bugs that are either fixed in Postgres latest or need to be reported to the pg-peformance or the pg-hackers list; upgrade and/or report the bug to fix.

More often than not, the first of the above applies, and it really is cheaper to do a seq scan. Think of it this way: imagine you're the postman. Is it less effort to deliver letters in the order they were posted, walking back and forth across town in the process, or to sequentially go through each street when a) you've a few letters to deliver in total vs b) you've a few letters to deliver per street? The rational for using an index vs a seq scan amounts to something like that.