Forcing a query to use an index

In this recipe, we will be discussing how to force a query to pick an index.

Getting ready

As we discussed in the previous chapters, the optimizer generates a set of plans based on the statistics it collected. Among all these plans, whatever plan has the least cost value would be preferred as a final execution plan of that query. Forcing a specific index to the SQL query is not possible in the current release of PostgreSQL; however, you can somehow guide the planner to pick the index scan over the other bitmap and sequential scans by disabling the session level optimizer parameters. Otherwise, you have to change the arbitrary cost value of random_page_cost so that it is close to the value of the seq_page_cost parameter.

How to do it...

Let's write a sample SQL query that prefers the sequential scan, as follows:

benchmarksql=# EXPLAIN ANALYZE SELECT COUNT(*) FROM bmsql_item WHERE i_price BETWEEN 10 AND 80; 
                                                ...