Как одно изменение конфигурации PostgreSQL улучшило производительность медленных запросов в 50 раз

https://habr.com/ru/post/444018/

random_page_cost = 0.1
seq_page_cost = 0.05

Вся правда об индексах в PostgreSQL, Олег Бартунов, Александр Коротков PostgreSQL
19.7.1. Конфигурация методов планировщика

По умолчанию произвольный доступ оценивается в 4 раза дороже, чем последовательный, но вы можете уменьшить значение, random_page_costчтобы оно было таким же или равным, seq_page_costе сли ваша база данных находится на SSD. Это уменьшит предполагаемую стоимость планов запросов, которые включают много произвольного доступа, поэтому планировщик будет выбирать их чаще.

Тест  pgbench

$ psql -c "SHOW random_page_cost;"
$ psql -c "SHOW seq_page_cost;"

random_page_cost = 1.1
seq_page_cost = 1

 
$ psql -c "ALTER TABLESPACE pg_default SET ( seq_page_cost = 1,  random_page_cost = 1.1);"
#$ psql -c "ALTER TABLESPACE pg_default RESET ( seq_page_cost, random_page_cost);"
$ psql -c "SELECT * FROM pg_tablespace;"



$ sudo su postgres
$ psql -l
$ psql -c "DROP DATABASE example;"
$ psql -c "CREATE DATABASE example;"
$ pgbench -i -s 500 example
$ psql -U postgres -d example -c "select pg_size_pretty( pg_database_size('example'))"
$ pgbench -c 10 -j 10 -t 10000 example

postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.395 ms
tps = 2945.848586 (including connections establishing)
tps = 2946.945923 (excluding connections establishing)
postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.422 ms
tps = 2922.406968 (including connections establishing)
tps = 2923.713944 (excluding connections establishing)
postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.443 ms
tps = 2904.094852 (including connections establishing)
tps = 2905.384501 (excluding connections establishing)


random_page_cost = 0.1
seq_page_cost = 0.05

$ psql -c "ALTER TABLESPACE pg_default SET ( seq_page_cost = 0.05,  random_page_cost = 1);"
#$ psql -c "ALTER TABLESPACE pg_default RESET ( seq_page_cost, random_page_cost);"
$ psql -c "SELECT * FROM pg_tablespace;"

#$ sudo systemctl restart postgresql.service

$ sudo su postgres
$ psql -l
$ psql -c "DROP DATABASE example;"
$ psql -c "CREATE DATABASE example;"
$ pgbench -i -s 500 example
$ psql -U postgres -d example -c "select pg_size_pretty( pg_database_size('example'))"
$ pgbench -c 10 -j 10 -t 10000 example

postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.401 ms
tps = 2940.382654 (including connections establishing)
tps = 2941.634033 (excluding connections establishing)
postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.442 ms
tps = 2905.657711 (including connections establishing)
tps = 2907.422695 (excluding connections establishing)
postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.569 ms
tps = 2802.127880 (including connections establishing)
tps = 2803.643751 (excluding connections establishing)


Без настройки:
random_page_cost =4
seq_page_cost =1

$ psql -c "ALTER TABLESPACE pg_default SET ( seq_page_cost = 1,  random_page_cost = 4);"
#$ psql -c "ALTER TABLESPACE pg_default RESET ( seq_page_cost, random_page_cost);"
$ psql -c "SELECT * FROM pg_tablespace;"
 
#$ sudo systemctl restart postgresql.service

$ sudo su postgres
$ psql -l
$ psql -c "DROP DATABASE example;"
$ psql -c "CREATE DATABASE example;"
$ pgbench -i -s 500 example
$ psql -U postgres -d example -c "select pg_size_pretty( pg_database_size('example'))"
$ pgbench -c 10 -j 10 -t 10000 example

postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.430 ms
tps = 2915.430306 (including connections establishing)
tps = 2916.868949 (excluding connections establishing)
postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.379 ms
tps = 2959.319011 (including connections establishing)
tps = 2961.047933 (excluding connections establishing)
postgres@mtest:/home/user$ pgbench -c 10 -j 10 -t 10000 example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 3.543 ms
tps = 2822.239973 (including connections establishing)
tps = 2823.544875 (excluding connections establishing)