random_page_cost in postgres
random_page_cost
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 | |
Indexes:
“dept_idx” btree (dept)
I have created a table employee with three columns and index on dept column.
postgres=# select count(*) from employee;
count
——–
100000
(1 row)
postgres=# show random_page_cost;
random_page_cost
——————
4
(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;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
—————-
t
(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.
Viswamitra's blog 