Database Reference
In-Depth Information
Coniguration parameters to optimize
queries
Like other databases, PostgreSQL has coniguration parameters that can be
conigured permanently or session predicated. The
postgresql.conf
ile is
utilized to conigure most of the coniguration parameters. We will only discuss
the parameters that can affect the performance. They are as follows:
•
work_mem
: The disk I/O is the dominant cost factor in queries; if case
queries involve a large number of complex sorts, then it can increment the
disk access. If the system has lots of recollection, then the database should
perform the in-recollection sorting to reduce the disk read. The
work_mem
coniguration parameter is utilized to determine when the sorting will be
performed in a recollection or the disk sort will be utilized. If we have lots of
recollection, then the
work_mem
parameter should be set to the optimal value
so that every sort can be performed in-recollection. The
work_mem
parameter
is per connection for each sort; this makes it authentically hard to set the
value of
work_mem
. The default value of
work_mem
is 4 MB. This can be seen
using the following statement:
warehouse_db=# show work_mem;
work_mem
----------
4MB
The size of
work_mem
is applied to every sort performed by each connection,
which makes it very hard to set the optimal value of this parameter. So, be
careful while setting this parameter
The memory used by
work_mem
uses the following formula:
Memory used for by work_mem = work_mem * max_connections *
(number of sort per query)
Let's consider the following example of
work_mem
:
warehouse_db=# EXPLAIN ANALYZE SELECT * FROM record.history
ORDER by history_id;
QUERY PLAN
-----------------------------------------------------------
Sort (cost=24450815.88..24700815.88 rows=100000000
width=46) (actual time=322234.459..435479.723
rows=100000000 loops=1)
Sort Key: history_id
Sort Method: external sort Disk: 5474096kB
