Skip to main content

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.


Popular posts from this blog

DBMS_OUTPUT.PUT_LINE in postgresql

Below is the example to print the variable value in postgres anonymous block.

 t integer;
 t := nextval('DocumentContextIDSEQ');
RAISE NOTICE 'Hello %',t;
NOTICE:  Hello 13

 t integer;
 select nextval('DocumentContextIDSEQ') into t;
RAISE NOTICE 'Hello %',t;
NOTICE:  Hello 11

INS-30131 Failed to access the temporary location

While doing the 12c1 installation in AIX , I have faced the below error.

Cause - Failed to access the temporary location.
Action - Ensure that the current user has required permissions to access the temporary location.
Additional Information:
 - Framework setup check failed on all the nodes 
 - Cause: Cause Of Problem Not Available 
 - Action: User Action Not Available Summary of the failed nodes all  

- Version of exectask could not be retrieved from node " " 
- Cause: Cause Of Problem Not Available 
- Action: User Action Not Available
The error code is [INS-30131]. I've the full privileges of an administrator-ship.

Solution :-

By seeing the error "not able to access temporary location" , I thought it is a privilage issue. But this is issue is due to the insufficient space in /tmp filesystem. I have manually set the TEMP variable to different file system. After that it worked fine.

export TMP='/u01/app/stage/database'
export TEMP='/u01/app/stage/datab…

How to do schema refresh in Oracle

This is one of the frequent task a DBA get in routine tasks. Schema refresh is nothing but copying the schema from one database to another database. There are two methods majorly used for performing the same(for taking the backup and doing the restoration).

1) datapump
2) Transportable Tablespace

We use datapump if the schema size is less in size. The datapump automatically copies the PL/SQL code , JAVA classes , Sequences etc to the destination database which is not possible with the Transportable Tablespace(in 12c we can do this). Below is the schema refresh using the first method.

1) First check the tablespaces in use by the schemas in the source and destination.

SQL> select tablespace_name from dba_segments where owner='DEV';  


SQL> select tablespace_name from dba_segments where owner='PROD';


2) Check the privilages and roles assigned in PROD and DEV.

======= Privialges …