GUCs for Index Selection

posted Oct 31, 2014, 6:11 AM by Sachchida Ojha
1. random_page_cost (master/session/reload) Default value: 100
Sets the planner’s estimate of the cost of a non sequentially fetched disk page
Lower value increases the chances for index scan to be picked

2. enable_indexscan (master/session/reload) Default value: on
Enables or disables the query planner’s use of index-scan plan types

3. enable_nestloop (master/session/reload) Default value: off
Enables or disables the query planner’s use of nested-loop join plans
This should be enabled for use of index in nested loop joins

4. enable_bitmapscan (master/session/reload) Default value: on
Enables or disables the query planner’s use of bitmap-scan plan types. 
Generally bitmap scan provides faster access, however you can try disabling it in specifically if you are getting very few rows out of index

5. enable_seqscan (master/session/reload) Default value: on
Disabling enable_seqscan results in use of index
Use this parameter very carefully only as last resort

Iterative tuning steps to favor index usage

Start by turning setting or confirming the following GUC settings

enable_indexscan to on

For joins via index lookup, set enable_nestloop to on

Start by lowering random_page_cost

Set to 20

If still not using the index, then set it to 10

If still not using the index, increase seq_page_cost

Set to 10

If still not using the index, then set it to 15
Comments