In PostgreSQL, by default, some configuration settings are configured to deal with disk I/O; that is, cost related to perform a sequential and random disk scans.
The following are the default values for these parameters:
postgres=# SHOW seq_page_cost ; seq_page_cost --------------- 1
To perform a single tuple fetch from the disk, the required cost is 1
.
postgres=# SHOW random_page_cost ; random_page_cost ------------------ 4
To perform a single tuple fetch by referring its index, the required cost is 4
.
If all our table's data or the complete database fits in the existing RAM size, then we can lower these values to as minimum as possible, since we don't need to perform any disk I/O operations.
Let's see the plan before setting these parameters, and then we will see the plan cost after lowering them.
Set the parameters with default settings as follows:
postgres=# SET random_page_cost to default ; SET postgres=# SET seq_page_cost to default ; SET postgres...