PostgreSQL Development Essentials

Optimizer settings for cached data

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 ...