O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

How do I force a query to use an index

Here, we show how to force the database to use an index. In fact, it is not possible to tell PostgreSQL to use an index, but you can trick it into using one 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 done carefully, 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 carefully testing on live.

As the PostgreSQL optimizer does not take into account the parallel load caused by other backends, it may make sense to lie to PostgreSQL ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required