random_page_cost in postgres


This parameter sets the planner’s estimate of the cost of a non-sequentially-fetched disk page , like fetching blocks using the index. The default value is 4.0. Reducing this value will cause the system to prefer index access. If you have disks with high throughput like SSD(solid state disk) you can reduce this. If most of the database is present in the cache , then both seq_page_cost and random_page_cost parameters can be reduced.

Example :-

postgres=# \d+ employee
                       Table “public.employee”
 Column |  Type   | Modifiers | Storage  | Stats target | Description
 id     | integer |           | plain    |              |
 dig    | text    |           | extended |              |
 dept   | integer |           | plain    |              |
    “dept_idx” btree (dept)

I have created a table employee with three columns and index on dept column.

postgres=# select count(*) from employee;
(1 row)
postgres=# show random_page_cost;
(1 row)

Now the value is 4 which is default.

postgres=# explain analyze select * from employee where dept<70000;
                                                  QUERY PLAN
 Seq Scan on employee  (cost=0.00..2185.00 rows=70018 width=41) (actual time=0.029..33.445 rows=69999 loops=1)
   Filter: (dept < 70000)
   Rows Removed by Filter: 30001
 Planning time: 0.243 ms
 Execution time: 36.927 ms
(5 rows)

postgres=# alter system set random_page_cost=0.5;
postgres=# select pg_reload_conf();
(1 row)

postgres=# explain analyze select * from employee where dept<70000;
                                                           QUERY PLAN
 Index Scan using dept_idx on employee  (cost=0.29..1976.36 rows=70018 width=41) (actual time=0.050..27.701 rows=69999 loops=1)
   Index Cond: (dept < 70000)
 Planning time: 0.161 ms
 Execution time: 31.030 ms
(4 rows)

After reducing the value of random_page_cost system started using the “Index scan”.

NOTE : If we have some tables which are placed in disk with high throuhput we can set a low random_page_cost for that specific tablespace using the “ALTER TABLESPACE” command.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s