cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |
cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |
cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |
cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |
cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |
cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |
cpu_index_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each index entry during an index scan. |
Data type |
numeric |
Default value |
0.005 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_index_tuple_cost |
cpu_operator_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each operator or function executed during a query. |
Data type |
numeric |
Default value |
0.0025 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_operator_cost |
cpu_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of processing each row during a query. |
Data type |
numeric |
Default value |
0.01 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
cpu_tuple_cost |
effective_cache_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's assumption about the size of the disk cache. |
Data type |
integer |
Default value |
Depends on resources (vCores, RAM, or disk space) allocated to the server. |
Allowed values |
1-2147483647 |
Parameter type |
dynamic |
Documentation |
effective_cache_size |
Azure-specific notes
The default value for the effective_cache_size
server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the effective_cache_size
server parameter of that instance.
Every time you change the product assigned to an instance, you should also adjust the value for the effective_cache_size
parameter according to the values in the following formula.
The formula used to compute the value of effective_cache_size
is dependent on the value of shared_buffers
, and looks like (memoryGib * 131072) - shared_buffers
.
Memory size |
shared_buffers |
effective_cache_size |
2 GiB |
32768 |
229376 |
4 GiB |
131072 |
393216 |
8 GiB |
262144 |
786432 |
16 GiB |
524288 |
1572864 |
32 GiB |
1048576 |
3145728 |
48 GiB |
1572864 |
4718592 |
64 GiB |
2097152 |
6291456 |
80 GiB |
2621440 |
7864320 |
128 GiB |
4194304 |
12582912 |
160 GiB |
5242880 |
15728640 |
192 GiB |
6291456 |
18874368 |
256 GiB |
8388608 |
25165824 |
384 GiB |
12582912 |
37748736 |
432 GiB |
14155776 |
42467328 |
672 GiB |
22020096 |
66060288 |
jit_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation is activated, if enabled. |
Data type |
integer |
Default value |
100000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_above_cost |
jit_inline_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation attempts to inline functions and operators. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_inline_above_cost |
jit_optimize_above_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the query cost above which JIT compilation applies expensive optimizations. |
Data type |
integer |
Default value |
500000 |
Allowed values |
-1-2147483647 |
Parameter type |
dynamic |
Documentation |
jit_optimize_above_cost |
min_parallel_index_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of index data for a parallel scan. |
Data type |
integer |
Default value |
64 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_index_scan_size |
min_parallel_table_scan_size
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the minimum amount of table data that must be scanned for a parallel scan to be considered. |
Data type |
integer |
Default value |
1024 |
Allowed values |
0-715827882 |
Parameter type |
dynamic |
Documentation |
min_parallel_table_scan_size |
parallel_setup_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of starting up worker processes for parallel query. |
Data type |
numeric |
Default value |
1000 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_setup_cost |
parallel_tuple_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. |
Data type |
numeric |
Default value |
0.1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
parallel_tuple_cost |
random_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a nonsequentially fetched disk page. |
Data type |
numeric |
Default value |
2 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
random_page_cost |
seq_page_cost
Attribute |
Value |
Category |
Query Tuning / Planner Cost Constants |
Description |
Sets the planner's estimate of the cost of a sequentially fetched disk page. |
Data type |
numeric |
Default value |
1 |
Allowed values |
0-1.79769e+308 |
Parameter type |
dynamic |
Documentation |
seq_page_cost |