Tuning query-related parameters

Submitted by elyzium on Thu, 2019-10-17 14:46

The following are the query planning related parameters that usually require tuning:
random_page_cost
seq_page_cost
effective_cache_size
work_mem
constraint_exclusion
These parameters can be set in the postgresql.conf configuration file.

random_page_cost: This parameter is basically used to estimate the cost of a random page
fetch in abstract cost units. The default value of this parameter is 4.0. Random page cost is
basically used to represent the coefficient between the cost of looking up one row via
sequential scans against the cost of looking up a single row individually using random
access, that is, disk seeks. This factor influences the query planner's decision to use indexes
instead of a table scan while executing queries. Reducing this value relative to the
seq_page_cost parameter will cause the system to prefer index scans. If, however, this
value is increased, it would make index scans appear expensive from a cost perspective.

seq_page_cost: This parameter is used to estimate the cost of a sequential page fetch in
abstract cost units. The default value of this parameter is 1.0 and this may need to be
lowered for caching effects. For situations where the database is cached entirely in RAM,
this value needs to be lowered. Always set random_page_cost to be greater than or equal
to seq_page_cost.

effective_cache_size: This parameter setting is used to provide an estimate of how
much memory is available for disk caching by the operating system and within the database
itself after considering and accounting for what is being used by the OS itself and other
applications. This parameter does not allocate any memory, rather it serves as a guideline as
to how much memory you expect to be available in the RAM, as well as in the PostgreSQL
shared buffer. This helps the PostgreSQL query planner to figure out whether the query
plans it is considering for query execution would fit in the system memory or not. If this is
set too low, indexes may not be used for executing queries the way you would expect.

work_mem: This parameter defines the amount of memory to be used by internal sort
operations and hash tables before switching to temporary disk files. When a running query
needs to sort data, an estimate is provided by the database as to how much data is involved
and it will compare it to the work_mem parameter. If it is larger, it will write out all the data
and use a disk-based sort mechanism instead of reverting to in-memory sort, which would
be much slower than the memory-based sort. If there are a lot of complex sorts involved,
and if you have good memory available, then increasing the value of the work_mem
parameter would allow PostgreSQL to do in-memory sorts, which will be faster than diskbased
ones. The amount of memory as specified by the value of the work_mem parameter
gets applied for each sort operation done by each database session and complex queries can
use multiple working memory-based sort buffers.
For instance, if the value of work_mem is
set to 100 MB, and if there are around 40 database sessions submitting queries, one would
end up using around 4 GB of real system memory. The catch is that one cannot predict the
number of sorts one client session may end up doing and thereby theoretically work_mem is
a per sort parameter instead of being a per client one effectively indicating that the memory
available from the use of work_mem is unbound, where there are a number of clients doing a
large number of sorts concurrently. From the point of view of optimization to arrive at
the optimum setting for the work_mem parameter, we must first consider how much free
system memory is available after the allocation of shared_buffers further divided by the
value of max_connections and then considering a fraction of that figure albeit half of that
would be an assertive value for the work_mem parameter. One needs to set this value
explicitly as the default value is too low for in-memory sorts.

constraint_exclusion: If in PostgreSQL you are using partitioned tables that utilize
constraints, then enabling the constraint_exclusion parameter allows the query planner to
ignore partitions that cannot have the data being searched for when that can be proven. In
earlier PostgreSQL versions before 8.4, this value was set to off, which meant that unless
toggled on partitioned tables, this will not work as expected. The new default value since
version 8.4 has been to set this value to partition, in which case the query planner will
examine constraints for inheritance child subtables and UNION ALL queries. If this value is
turned on, it will examine constraints on all tables and this will impose extra planning
overhead even for simple queries and thus there will be a performance overhead.

Comments

Daqrlr o (not verified)

Mon, 2020-05-11 06:09

Main menu

Status message

You are here

All Tags

Top 30 viewed articles

Tuning query-related parameters

Tags:

Comments

Pages

Add new comment

Filtered HTML

Plain text

To Do List