3

If I have a couple of indexes on the same column in postgres, what is the way I can specify which one to use on the given query? Mostly for testing, not the production purposes. In other words, I need the MySQL alternative for USE INDEX or even FORCE INDEX. No, it's not the duplicate due to that's about forcing postgres to use index scans over plain scans which is not the topic.

whoever
  • 575
  • 4
  • 18
  • 1
    Question: why? Answer: you don't need to, Postgres will use an index if it appears to be usable. Valid up-to-date statistics are needed to let PG determine if an index is of any use in your paricular query. – joop Aug 11 '14 at 14:35
  • Bill: as I understand, that's about forcing postgres to use index scans over plain scans which is not the topic. or am I getting it wrong? – whoever Aug 11 '14 at 14:35
  • 1
    joop: I need it to benchmark the performance of different indexes and it'd be much cleaner to have a statement like that due to the testing structure. – whoever Aug 11 '14 at 14:37
  • 1
    Wrong. You do not need to benchmark performance. You'll have to verify the correctness of your data model first, *then* **maybe** worry about performance. – joop Aug 11 '14 at 14:39
  • 1
    joop: That's not that I'm testing my data model! I just need to have Any Large Database and test my version of index implementation vs the existing ones. – whoever Aug 11 '14 at 14:42

1 Answers1

4

I'm afraid there is no such thing in Postgres:

See http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

Many people over the years have requested that the PostgreSQL project implement "optimizer hints" or "query hints" as they are implemented in other RDBMSes such as Oracle and MySQL. The official current stance from the community is this:

We are not interested in implementing hints in the exact ways they are commonly implemented on other databases. Proposals based on "because they've got them" will not be welcomed. If you have an idea that avoids the problems that have been observed with other hint systems, that could lead to valuable discussion.

The wiki lists some alternatives to hinting here, however none of these suggestions are equivalents of what you are looking for.

However, you may be able to force the join order as described in 14.3. Controlling the Planner with Explicit JOIN Clauses, depending on what the values for from_collapse_limit and join_collapse_limit are (you may be able to set/reset them on the fly). This can then indirectly affect which indexes are used, but again there is no explicit choice of index.

Community
  • 1
  • 1
jmiserez
  • 2,991
  • 1
  • 23
  • 34
  • Thanks for the answer, Postgres offers a ton of features the others don't. One of my problems today is it's choosing the wrong index to work with, despite my best efforts. – Kevin Parker Jan 18 '20 at 03:04
  • Try the things described here: https://www.postgresql.org/docs/12/indexes-examine.html and you may actually be able to force a plan type setting one of these options: https://www.postgresql.org/docs/12/runtime-config-query.html. Also check out the other answers in the linked duplicate question. Most likely there is something wrong with your index, it's statistics or the query and forcing a plan may not do what you want. Also check that the index contains all the necessary columns, if they're there the optimizer should use them. – jmiserez Jan 18 '20 at 08:15