Протестировал 5 популярных в интернете методов оптимизации запросов в PostgreSQL, и вот что из этого вышло

Статья будет полезна, если ты начинающий разработчик и столкнулся с долгим выполнением запросов в PostgreSQL. Сразу вывод: не всё то полезно для оптимизации запросов, что написано в интернете.

Я Леонид, разработчик Java в компании «Programming Store». Протестировал популярные советы из интернета по методам оптимизации и увеличения производительности БД. Расскажу, что из этого вышло.

Для примера я развернул СУБД PostgreSQL 14 в контейнере докера. Настройки оставил по умолчанию. Создал две таблицы: users и products. Заполнил их данными. Users имеет 1 000 000 записей, products – 3 010 000 записей. Ниже приведены схемы данных таблиц.

Таблица products:

create table products ( id bigint not null constraint products_pkey primary key, user_id bigint, name varchar, tara varchar, volume varchar, brand varchar, uuid_a uuid, uuid_b uuid, uuid_c uuid, uuid_d uuid, uuid_e uuid, uuid_f uuid, uuid_g uuid, uuid_h uuid, uuid_i uuid, uuid_j uuid, uuid_k uuid, uuid_l uuid, uuid_m uuid, uuid_n uuid, uuid_o uuid, uuid_p uuid, uuid_q uuid, uuid_r uuid, uuid_s uuid, uuid_t uuid, uuid_u uuid, uuid_v uuid, uuid_w uuid, uuid_x uuid, uuid_y uuid, uuid_z uuid );

Таблица users:

create table users ( id bigint not null constraint users_pkey primary key, first_name varchar, last_name varchar, uuid_a varchar(36), uuid_b varchar(36), uuid_c varchar(36), uuid_d varchar(36), uuid_e varchar(36), uuid_f varchar(36), uuid_g varchar(36), uuid_h varchar(36), uuid_i varchar(36), uuid_j varchar(36), uuid_k varchar(36), uuid_l varchar(36), uuid_m varchar(36), uuid_n varchar(36), uuid_o varchar(36), uuid_p varchar(36), uuid_q varchar(36), uuid_r varchar(36), uuid_s varchar(36), uuid_t varchar(36), uuid_u varchar(36), uuid_v varchar(36), uuid_w varchar(36), uuid_x varchar(36), uuid_y varchar(36), uuid_z varchar(36) );

Самый распространенный совет по увеличению производительности запроса, который я встречал, — это добавление индексов.

Индекс — это метод настройки производительности, позволяющий быстрее извлекать записи. При выполнении запроса к таблице, в которой нет нужных индексов, будет выполнено последовательное сканирование строк (Seq Scan).

Кейс №1

Для примера выполнил следующий запрос, который выводит на экран все записи, у которых имя «Арбуз»:

select name from products where name = 'Арбуз';

Запросив план запроса, мы видим, что выполнен Seq Scan. Время выполнения 500.771 ms:

Теперь давайте добавим индекс на поле name и выполним предыдущие действия:

create index products_name_index on products (name);

Время выполнения: 16.882 ms.

Теперь план запроса нам показывает поиск по индексу. Время выполнения уменьшилось: было 500.771 ms, стало 16.882 ms.

Вывод по кейсу 1. Добавление индекса хорошо помогает ускорять запросы поиска в БД, но не нужно считать это панацеей от всех проблем. Если бездумно добавлять индексы на каждое поле, то мы рано или поздно столкнемся с обратной ситуацией при добавлении, обновлении или удалении записей, т.к. СУБД после операции записи обновляет эти индексы. И чем больше индексов, тем больше времени это занимает.

Кейс №2

Давайте выполним операцию добавления записи в таблицу, в которой еще нет избытка индексов.

Сначала удаляем ранее добавленный индекс:

drop index products_name_index;

После этого выполняем запрос на обновление записи:

update products set name = 'Вода', brand = 'VOSS' where name = 10000;

Время выполнения: 0.065 ms.

Теперь добавим индексы:

create index products_brand_index on products (brand); create index products_name_index on products (name); create index products_tara_index on products (tara); create index products_volume_index on products (volume); create index products_uuid_a_index on products (uuid_a); create index products_uuid_b_index on products (uuid_b); create index products_uuid_c_index on products (uuid_c); create index products_uuid_d_index on products (uuid_d); create index products_uuid_e_index on products (uuid_e); create index products_uuid_f_index on products (uuid_f);

Повторим операцию добавления записи:

update products set name = 'Вода', brand = 'VOSS' where name = 20000;

На этот раз мы видим увеличение времени выполнения: было 0.065 ms, стало 0.394 ms.

Вывод по кейсу №2. Перед добавлением индекса нужно проанализировать необходимость в его добавлении.

Для поиска редко используемых индексов можно воспользоваться следующим запросом:

SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;

Так выглядит результат выполнения данного запроса. В нем три столбца:

index — название индекса;
table — название таблицы, для которого создан индекс;
drop_statement — запрос для удаления данного индекса.

Можно изменить значение idx_scan в условии или добавить его в выводимые поля. Данное значение указывает количество использований данного индекса.

Следующий вариант уменьшения времени выполнения запроса — это уменьшение количества возвращаемых полей из БД.

При запросах к базе часто пренебрегают количеством выводимых полей, используя оператор «*» после SELECT. Если полей много, то из-за этого увеличивается время выполнения запроса.

Для примера выполним следующий запрос:

select * from products p inner join users u on u.id = p.user_id;

Вот его план запроса:

Время выполнения: 7837.342 ms.

А теперь выполним запрос конкретными полями:

select p.name, p.brand, p.tara, u.first_name, u.last_name from products p inner join users u on u.id = p.user_id;

И вот его план запроса:

Время выполнения: 2442.886 ms (а было 7837.342 ms).

Вывод: Данный пример наглядно показывает, что необходимо сокращать количество получаемых полей из БД.

Другая проблема, похожая на предыдущую — это неправильное использование типов данных для столбцов.

В изначально подготовленной таблице у меня были поля типа UUID. Поле типа UUID занимает 16 байт. Если для хранения uuid использовать другой тип, например, varchar(36), то будет занято больше места, соответственно и запрос будет выполняться дольше.

Для наглядности выполним следующий запрос:

select * from users;

Вот его план запроса:

Время выполнения: 146.715 ms.

Теперь изменим тип uuid полей на varchar(36):

alter table users alter column uuid_a type varchar(36) using uuid_a::varchar(36); alter table users alter column uuid_b type varchar(36) using uuid_b::varchar(36); alter table users alter column uuid_c type varchar(36) using uuid_c::varchar(36); alter table users alter column uuid_d type varchar(36) using uuid_d::varchar(36); alter table users alter column uuid_e type varchar(36) using uuid_e::varchar(36); alter table users alter column uuid_f type varchar(36) using uuid_f::varchar(36); alter table users alter column uuid_g type varchar(36) using uuid_g::varchar(36); alter table users alter column uuid_h type varchar(36) using uuid_h::varchar(36); alter table users alter column uuid_i type varchar(36) using uuid_i::varchar(36); alter table users alter column uuid_j type varchar(36) using uuid_j::varchar(36); alter table users alter column uuid_k type varchar(36) using uuid_k::varchar(36); alter table users alter column uuid_l type varchar(36) using uuid_l::varchar(36); alter table users alter column uuid_m type varchar(36) using uuid_m::varchar(36); alter table users alter column uuid_n type varchar(36) using uuid_n::varchar(36); alter table users alter column uuid_o type varchar(36) using uuid_o::varchar(36); alter table users alter column uuid_p type varchar(36) using uuid_p::varchar(36); alter table users alter column uuid_q type varchar(36) using uuid_q::varchar(36); alter table users alter column uuid_r type varchar(36) using uuid_r::varchar(36); alter table users alter column uuid_s type varchar(36) using uuid_s::varchar(36); alter table users alter column uuid_t type varchar(36) using uuid_t::varchar(36); alter table users alter column uuid_u type varchar(36) using uuid_u::varchar(36); alter table users alter column uuid_v type varchar(36) using uuid_v::varchar(36); alter table users alter column uuid_w type varchar(36) using uuid_w::varchar(36); alter table users alter column uuid_x type varchar(36) using uuid_x::varchar(36); alter table users alter column uuid_y type varchar(36) using uuid_y::varchar(36); alter table users alter column uuid_z type varchar(36) using uuid_z::varchar(36);

Смотрим на план предыдущего запроса:

Время выполнения: 238.234 ms.

Таким образом, мы приходим к выводу, что необходимо правильно подбирать типы данных.

Теперь перейдем к настройкам СУБД PostgreSQL. Для начала рассмотрим популярный вариант с увеличением значения для work_mem.

WORK_MEM задаёт объём памяти, который будет использоваться для внутренних операций сортировки и хеш-таблиц, прежде чем будут задействованы временные файлы на диске.

Увеличение данной памяти приведет к большей производительности запросов, где используются сортировки и хэш-таблицы.

Для примера оставим стандартное значение work_mem (4mb) и выполним следующий запрос:

select name, tara, brand from products group by name, tara, brand;

План запроса:

Время выполнения 4194.659 ms.

Увеличим значение work_mem до 512mb и посмотрим на план предыдущего запроса:

Время выполнения 2550.668 ms.

В первом примере для выполнения группировки не хватило памяти и группировка выполнялась порционно. Это видно по количеству Batches. Во втором примере все влезло в память и Batches всего 1.

Вывод. Как видно из примера, увеличение памяти уменьшает время выполнения запроса. Но при выборе значения надо учитывать, что общий используемый объём памяти может превышать указанное значение, так как для каждой операции и сеанса используется указанный объем памяти.

Помимо work_mem на просторах интернета предлагается использовать параллельное выполнение. Для этого используются воркеры.

max_parallel_workers_per_gather задаёт максимальное число рабочих процессов, которые могут запускаться одним узлом.

Параллельное выполнение может ускорить выполнение запросов. Давайте проверим это на следующем примере со значением max_parallel_workers_per_gather = 0:

select * from products where name = 'Арбуз';

План запроса:

Время выполнения: 466.685 ms.

Увеличим значение max_parallel_workers_per_gather = 2 и выполним план предыдущего запроса:

Время выполнения: 180.492 ms.

Как видим, время выполнения сильно уменьшилось. Распараллеливание помогло увеличить производительность. Но учтите, что параллельные запросы могут потреблять значительно больше ресурсов, чем непараллельные, так как каждый рабочий процесс является отдельным процессом. По итогу увеличение значения может негативно сказаться на производительности.

Также для примера можно взять запрос из предыдущего пункта и выполнить его со значением max_parallel_workers_per_gather = 0:

select name, tara, brand from products group by name, tara, brand;

План запроса:

Время выполнения: 4421.002 ms.

А вот что будет, если увеличить значение max_parallel_workers_per_gather = 2 и посмотреть на план запроса:

Время выполнения: 6266.960 ms.

Тут наглядно видно ухудшение производительности. Хоть работа и стала распараллеленной, но это же породило больше действий. В каждой из параллелей выполнялась сортировка, а после — объединение данных. На все это тратится дополнительное время.

К оптимизации запросов нужно подходить с умом, так как не все популярные решения увеличат производительность в вашем конкретном случае. Выше были приведены частные примеры в вакууме. Приведенные в примерах методы работают, но также есть моменты, когда стало только хуже.

Перед началом оптимизации запросов надо проанализировать ситуацию. Зачастую может быть потрачено в разы больше ресурсов на оптимизацию запроса, нежели на увеличение производительности другим путем, например, путем добавления мощности на сервере и настройки параметров СУБД. Четвертый и пятый пункт статьи это наглядно демонстрируют.

Возможны случаи, когда запрос, который как вам кажется, не оптимальный и долго выполняется – вызывается крайне редко и нет необходимости тратить на него уйму своих ресурсов для оптимизации. Или, например, при добавлении индексов другие запросы начнут медленнее отрабатывать.

Буду рад ответить на вопросы в комментариях :)

Протестировал 5 популярных в интернете методов оптимизации запросов в PostgreSQL, и вот что из этого вышло

Тестирую метод №1: «добавить индексы»

Тестирую метод №2: «выводить конкретные поля»

Тестирую метод №3: «изменить тип данных»

Тестирую метод №4: «увеличить память WORK_MEM»

Тестирую метод №5: «параллельное выполнение»

Выводы