random_page_cost

The random_page_cost parameter helps determine the cost of performing random page access. The query planner in Amazon RDS and Aurora uses this parameter, along with other statistics about the table, to determine the most efficient plan for running a query.

The seq_page_cost and random_page_cost parameters are closely related and usually used together by the planner to compare the costs of different access methods and decide which one is the most efficient. Therefore, if you change one of these parameters, you should also consider whether the other parameter needs to be adjusted.

In general, the query planner tries to minimize the cost of running a query. The cost is determined by using a combination of the number of disk page reads and the value of random_page_cost. A higher value of random_page_cost tends to favor sequential scans whereas a lower value tends to favor index scans. A lower value also tends to favor nested loop joins instead of hash joins.

The random_page_cost parameter uses the default PostgreSQL engine value (4) unless a value is set in the parameter group or in the local session. You can tune this value depending on the specific characteristics of your server and workload. If most of the indexes used in your workload fit in memory or in the Aurora tiered cache, changing the value of random_page_cost to a value that's close to seq_page_cost is appropriate.

AWS CLI syntax

The following command changes random_page_cost for a specific DB parameter group. This change applies to all instances or clusters that use the parameter group.

# Modify random_page_cost on a DB parameter group
aws rds modify-db-parameter-group \
  --db-parameter-group-name <parameter_group_name> \
  --parameters "ParameterName=random_page_cost,ParameterValue=<new_value>,ApplyMethod=immediate"

# Modify random_page_cost on a DB cluster parameter group
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name <parameter_group_name> \
  --parameters "ParameterName=random_page_cost,ParameterValue=<new_value>,ApplyMethod=immediate"

Type: Dynamic (changes are applied immediately if you set ApplyMethod=immediate)

Default value: 4

Example

Let's say you have a database that stores a large amount of data in a table that's frequently queried with filters on non-indexed columns. The queries take a long time to complete, and the query planner doesn't select the most efficient plan for accessing the data.

One way to improve performance would be to decrease the random_page_cost parameter. If you set it to 1, the cost of random page access would be four times less expensive than the default value. If you left random_page_cost at its default value of 4, random page access would be four times more expensive than sequential page access (as determined by the seq_page_cost parameter, which is 1.0 by default). However, in this specific case, random page access might actually be much more expensive depending on the storage type.

Decreasing the value of the random_page_cost parameter can make the query planner more likely to select an index-based plan or use a different access method that's better suited to the specific characteristics of the table.

We recommend that you monitor query performance after you change the parameter and make adjustments as needed. You should also check the query planner with an EXPLAIN statement to check whether it's selecting an efficient plan.

This is just one example. The optimal setting depends on the specific characteristics of your workload. Also, this is just one aspect of performance tuning; you should also consider other parameters and configuration options that can affect your query performance.