PostgreSQL 9 Administration Cookbook - Second Edition

Forcing a query to use an index

Here, we will show you how to force the database to use an index. In fact, it is not possible to tell PostgreSQL to use an index by submitting an access path hint, like other DBMS products do. However, you can trick it into using an index by telling the optimizer that all other options are prohibitively expensive.

Getting ready

First, you have to make sure that it is worth it to use the index. This is best done on a development or testing system, but if you are careful, it can also be done on the production server. Sometimes, it is very hard to generate a load similar to a live system in a test environment, and then your best option may be to carefully test it on the production server.

As the PostgreSQL optimizer does ...