Всем привет, меня зовут Денис Лимарев, я руковожу разработкой в одной из продуктовых команд Uzum Tezkor. В этой статье разберу несколько оптимизаций запросов к БД, которыми наша команда пользуется при разработке своих сервисов, и опишу подход к оптимизациям запросов в целом. В своих проектах мы используем PostgreSQL версии 14.15, поэтому все запросы я проанализировал на ней, и ваши результаты могут отличаться в зависимости от вашей версии.

Оглавление:

1. Схема данных

2. Оптимизации ad-hoc

3. Денормализация данных

4. Настройка условий запроса, или IN vs JOIN

5. Накинем ресурсов на бэкенд

6. Заключение

7. Список литературы и полезные ссылки

Схема данных

Георгий работает в городской библиотечной сети, где отвечает за наполнение каталогов издателей. Однажды к нему подошёл тимлид со следующей проблемой: «Мы перестали укладываться в нормативы по скорости поиска книг в каталоге, и даже превысили допустимый порог ошибок за этот месяц — нужно срочно что-то предпринять». «Разберёмся», — ответил Георгий, и сразу отправился изучать метрики библиотечной системы.

Посмотрев метрики API, Георгий собрал профилировщиком статистику по медленным операциям сервиса. Стало очевидно, что проблема в запросах к БД. Поэтому Георгий изучил схему данных проекта:

CREATE TABLE catalogs (
   id uuid primary key,
   publisher_id uuid not null,
   status enum('available', 'upcoming', 'archived') not null,
   constraint catalogs_publisher_status_uniq unique (publisher_id, status)
);

CREATE TABLE genres (
   id uuid primary key,
   name text not null,
   catalog_id uuid not null
);

create index idx_genres_catalog_id
   on genres (catalog_id);

CREATE TABLE book_genre (
   book_id uuid not null,
   genre_id uuid not null
);

create index idx_book_genre_hash_book_id
   on book_genre using hash (book_id);

create index idx_book_genre_hash_genre_id
   on book_genre using hash (genre_id);

create unique index book_genre_reverse_pkey
   on book_genre (genre_id, book_id);

CREATE TABLE books (
   id uuid primary key,
   title text not null,
   isbn text not null,
   price int not null,
   status enum('available', 'upcoming') not null,
   publisher_id uuid not null,
   catalog_id uuid not null
);

CREATE INDEX idx_books_publisher_catalog_status
   on books (publisher_id, catalog_id, status);

CREATE TABLE book_editions (
   book_id uuid not null,
   edition_id uuid not null
);

create unique index book_edition_pkey
   on book_editions (book_id, edition_id);

CREATE TABLE editions (
   id uuid primary key,
   title text not null,
   isbn text not null,
   price int not null,
   max_copies int not null
);

CREATE TABLE stockrooms (
   id uuid primary key,
   book_isbn text not null,
   quantity int not null,
   publisher_id uuid not null,
   updated_at timestamptz not null,
   constraint stockrooms_publisher_book_uniq unique (publisher_id, book_isbn)
);

Оптимизации ad-hoc

Первым путем API, на который упал взгляд Георгия, был /api/v1/books. Он состоял из последовательных запросов в БД:

SELECT b.id, b.title, b.isbn, b.price
FROM catalogs c
JOIN genres g ON c.id = g.catalog_id
JOIN book_genre bg ON bg.genre_id = g.id
JOIN books b ON b.id = bg.book_id
WHERE b.isbn IN (:isbns)
  AND c.status = 'available';

Далее частями выбираются издания:

SELECT e.title, e.isbn, e.price
FROM book_editions be
JOIN editions e ON be.edition_id = e.id
WHERE be.book_id IN (:book_ids);

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

ALTER TABLE books ADD COLUMN with_editions boolean NOT NULL DEFAULT true;

Таким образом удалось перенести принятие решения о наличии издания в код до непосредственного запроса в БД. Это помогло уменьшить время выполнения части запросов.

Денормализация данных

Затем Георгий перешел к запросу книг:

SELECT b.id, b.title, b.isbn, b.price
FROM catalogs c
     JOIN genres g ON c.id = g.catalog_id
     JOIN book_genre bg ON bg.genre_id = g.id
     JOIN books b ON b.id = bg.book_id
WHERE b.isbn IN (:isbns)
  AND c.status = 'available'
  AND c.publisher_id = :publisher_id;

Первым делом выполнил EXPLAIN ANALYZE:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop  (cost=2.39..692.44 rows=2 width=93) (actual time=0.738..78.045 rows=500 loops=1)                                                                              |
|  ->  Nested Loop  (cost=0.71..42.29 rows=357 width=16) (actual time=0.542..12.698 rows=3280 loops=1)                                                                      |
|      ->  Nested Loop  (cost=0.71..17.55 rows=12 width=16) (actual time=0.455..1.183 rows=155 loops=1)                                                                     |
|            ->  Index Scan using catalog_publisher_status_uniq on catalogs c  (cost=0.29..2.50 rows=1 width=16) (actual time=0.049..0.051 rows=1 loops=1)                  |
|                  Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND (status = 'available'::catalog_status))                                   |
|            ->  Index Scan using idx_genres_catalog_id on genres g  (cost=0.42..14.93 rows=12 width=32) (actual time=0.402..1.085 rows=155 loops=1)                         |
|                  Index Cond: (catalog_id = c.id)                                                                                                                          |
|      ->  Index Scan using idx_book_genre_hash_genre_id on book_genre bg  (cost=0.00..1.49 rows=57 width=32) (actual time=0.059..0.071 rows=21 loops=155)                   |
|            Index Cond: (genre_id = g.id)                                                                                                                                  |
|  ->  Index Scan using books_pkey on books b  (cost=1.68..1.82 rows=1 width=93) (actual time=0.020..0.020 rows=0 loops=3280)                                                |
|        Index Cond: (id = bg.book_id)                                                                                                                                      |
|        Filter: ((isbn)::text = ANY ('{тут 500 isbn книг}'::text[]))                                                                                                       |
|        Rows Removed by Filter: 1                                                                                                                                          |
|Planning Time: 40.434 ms                                                                                                                                                   |
|Execution Time: 78.272 ms                                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Попытка добавить новые индексы, которые могли бы улучшить ситуацию, не увенчалась успехом: текущие индексы лучше всего покрывали запрос. После обсуждения с коллегами появилась идея обогатить таблицу books дополнительными данными, которые уменьшат селективность запроса: добавить catalogs.id, так как информация о жанрах в запросе не требовалась, и связанные сущности genres и catalogs после создания записей books никогда не изменялись.

Миграцию новых данных провели в три подхода:

• сначала добавили необязательное поле catalog_id:

  ALTER TABLE books ADD COLUMN catalog_id uuid null;

• частями обновили данные по books;

• после этого сделали новое поле обязательным.

Теперь новый запрос на книги выглядел так:

SELECT b.id, b.title, b.isbn, b.price
FROM catalogs c
     JOIN books b ON b.catalog_id = c.id
WHERE b.isbn IN (:isbns)
  AND c.status = 'available'
  AND c.publisher_id = :publisher_id;

Уменьшение количества связанных таблиц в запросе значительно сократило время ответа.

+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop  (cost=2.10..55697.11 rows=1 width=93) (actual time=8.349..33.778 rows=500 loops=1)                                                               |
|  ->  Index Scan using catalog_publisher_status_uniq on catalogs c  (cost=0.29..2.51 rows=1 width=16) (actual time=0.016..0.019 rows=1 loops=1)               |
|        Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND (status = 'available'::catalog_status))                               |
|  ->  Index Scan using idx_books_publisher_catalog_status on books b  (cost=1.81..55694.59 rows=1 width=109) (actual time=8.328..33.659 rows=500 loops=1)     |
|        Index Cond: (catalog_id = c.id)                                                                                                                      |
|        Filter: ((isbn)::text = ANY ('{тут 500 isbn книг}'::text[]))                                                                                         |
|        Rows Removed by Filter: 2780                                                                                                                         |
|Planning Time: 35.097 ms                                                                                                                                     |
|Execution Time: 33.880 ms                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

Разница между новым и старым запросом:

Было:
Planning Time: 40.434 ms
Execution Time: 78.272 ms

Стало:
Planning Time: 35.097 ms
Execution Time: 33.880 ms

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

Настройка условий запроса, или IN vs JOIN

Первым монитор сработал на медленный запрос стоков, когда количество записей в таблице stocks достигло отметки в один миллион записей. После этого команда запланировала работы по оптимизации. Георгий проявил инициативу и забрал эту задачу себе. Согласно планировщику, проблема была в следующем запросе: он перестал укладываться в 100 мс в 98 % запросов:

SELECT s.id,
       s.book_isbn,
       s.quantity
FROM stockrooms s
WHERE s.publisher_id = :publisher_id
  AND s.book_isbn IN (:isbns);

Георгий не первый раз занимался оптимизацией, поэтому первым делом выполнил EXPLAIN ANALYZE:

+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Index Scan using stockrooms_publisher_book_uniq on stockrooms s  (cost=0.56..846.94 rows=15 width=45) (actual time=1.237..19.237 rows=500 loops=1)           |
|  Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND ((book_isbn)::text = ANY ('{тут 500 isbn книг}'::text[])))                  |
|Planning Time: 43.230 ms                                                                                                                                     |
|Execution Time: 19.324 ms                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+

Из анализа было ясно, что индексы есть и запрос в них попадает. Попытки найти другие, более выгодные по времени ответа индексы не увенчались успехом, поэтому Георгий продолжил поиски. Старшие коллеги подсказали, что можно попробовать заменить IN на JOIN с CTE, что Георгий и попытался сделать. Получился такой запрос:

WITH book_isbns AS (SELECT unnest(ARRAY [:book_isbns]) AS book_isbn)
SELECT s.id,
       s.book_isbn,
       s.quantity
FROM stockrooms s
     JOIN book_isbns b ON s.book_isbn = b.book_isbn
WHERE s.publisher_id = :publisher_id;

Прогон запроса на продовых данных показал значительное ускорение по сравнению с версией с IN, поэтому Георгий решил выполнить EXPLAIN ANALYZE для выяснения причин:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop  (cost=0.56..1392.82 rows=7 width=45) (actual time=0.115..6.434 rows=500 loops=1)                                                                        |
|  ->  ProjectSet  (cost=0.00..2.52 rows=500 width=32) (actual time=0.002..0.058 rows=500 loops=1)                                                                    |
|        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)                                                                       |
|  ->  Index Scan using stockrooms_publisher_book_uniq on stockrooms s  (cost=0.56..2.77 rows=1 width=45) (actual time=0.012..0.012 rows=1 loops=500)                  |
|        Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND ((book_isbn)::text = (unnest('{тут 500 isbn книг}'::text[]))))                 |
|Planning Time: 2.385 ms                                                                                                                                              |
|Execution Time: 6.523 ms                                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

В случае с JOIN операции сканирования индекса значительно «дешевле», то есть индекс таблицы используется более эффективно.

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

В итоге, в нашем случае разница между новым и старым запросом составила:

Было:
Planning Time: 43.230 ms
Execution Time: 19.324 ms

Стало:
Planning Time: 2.385 ms
Execution Time: 6.523 ms

Накинем ресурсов на бэкенд

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

SELECT b.id,
       b.title,
       b.price      AS price,
       b.isbn       AS isbn,
       b.status     AS status
FROM books b
     JOIN catalogs c ON c.publisher_id IN (:publisher_ids)
                    AND c.status = 'available'
WHERE b.publisher_id IN (:publisher_ids)
  AND b.catalog_id = c.id
  AND b.status IN ('available', 'upcoming');
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop  (cost=0.84..13.93 rows=1 width=98) (actual time=0.056..15.414 rows=871 loops=1)                                                            |
|  ->  Index Scan using catalog_publisher_status_uniq on catalogs c  (cost=0.29..5.01 rows=2 width=16) (actual time=0.032..0.060 rows=2 loops=1)        |
|        Index Cond: ((publisher_id = ANY ('{тут несколько id}'::uuid[])) AND (status = 'available'::catalog_status))                                  |
|  ->  Index Scan using idx_books_publisher_catalog_status on books b  (cost=0.56..4.45 rows=1 width=114) (actual time=0.022..7.619 rows=436 loops=2)|
|        Index Cond: ((publisher_id = ANY ('{тут несколько id}'::uuid[])) AND (catalog_id = c.id))                                                     |
|        Filter: (status = ANY ('{available,upcoming}'::book_status[]))                                                                                |
|        Rows Removed by Filter: 6180                                                                                                                  |
|Planning Time: 0.461 ms                                                                                                                               |
|Execution Time: 15.496 ms                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

Команда долго думала и решила, что оптимальным вариантом будет распараллелить запрос на бэкенде, чтобы сохранить запрос простым и уменьшить объем покрываемых записей.

Запрос практически не изменили, только ограничили количество выбираемых publisher_id одним значением.

SELECT b.id,
       b.title,
       b.price      AS price,
       b.isbn       AS isbn,
       b.status     AS status
FROM books b
     JOIN catalogs c ON c.publisher_id = :publisher_id
                    AND c.status = 'available'
WHERE b.publisher_id = :publisher_id
  AND b.catalog_id = c.id
  AND b.status IN ('available', 'upcoming');

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

Пакет database/sql из стандартной библиотеки не позволял сканировать результирующие строки потокобезопасно, следовательно, на большем количестве строк и нескольких колонках в ответе можно было значительно потерять в операции scan. Пример:

rows, err := db.Query(`
SELECT b.id,
       b.title,
       b.price      AS price,
       b.isbn       AS isbn,
       b.status     AS status
FROM books b
     JOIN catalogs c ON c.publisher_id = :publisher_id
                    AND c.status = 'available'
WHERE b.publisher_id = :publisher_id
  AND b.catalog_id = c.id
  AND b.status IN ('available', 'upcoming');
`)
defer rows.Close()

for rows.Next() { // тут 10k значений, в несколько горутин мы обойти результат не можем
var id int
var name string
err := rows.Scan(&id, &name)
// обработка строки
}

На бэкенде запрос стали запускать отдельно, по каждому publisher_id до четырёх запросов одновременно. Это позволило оставить запрос к БД простым, но усложнило логику поддержки на бэкенде. В результате этих изменений Георгию удалось срезать до 50 мс на сканировании больших каталогов из БД в ОП.

Заключение

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

Помимо описанных выше вариантов, механики можно реализовать и в других комбинациях. Например, денормализацию данных можно выполнять без изменениях структуры начальных таблиц через агрегацию данных в matview. А оптимизации ad-hoc будут свои для каждого конкретного сервиса и его бизнес-процесса. Попробуйте рассмотреть ваши сервисы на разных уровнях детализации, это поможет понять, где можно сэкономить.

А если вы любите и умеете оптимизировать базы данных, приходите в команду. У нас много подобных интересных задач.

Список литературы и полезные ссылки

• https://explain.tensor.ru/plan/ — визуализация explain;

• https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/ — сравнение разных подходов поиска по списку значений для PostgreSQL 10;

• https://habr.com/ru/companies/postgrespro/articles/662021/ — оптимизации большого количества JOIN;

• https://minervadb.xyz/selectivity-and-cardinality-estimations-in-postgresql/ — короткая статья про оценку кардинальности и селективности запросов;

• https://www.postgresql.org/docs/current/planner-stats.html — статья про использование статистики в запросах и причем тут селективность;

• https://www.postgresql.org/docs/current/row-estimation-examples.html — статья про оценку количества строк через статистику;

• https://habr.com/ru/companies/exWargaming/articles/323354/ — иногда причина низкой скорости выполнения запросов являются ошибки, например, такие как дедлоки.