Edit

Share via

Query Tuning / Planner Cost Constants

  • Article

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