Иногда мы пишем SQL-запросы, мало задумываясь над тем фактом, что сначала они должны быть по сети как-то доставлены до сервера, а затем их результат - обратно в клиентское приложение. Если при этом на пути до сервера присутствует еще и пулер соединений типа pgbouncer, дополнительно "перекладывающий" байты между входящими и исходящими коннектами, ситуация становится еще тяжелее...
Поэтому сегодня рассмотрим некоторые типичные ситуации, в которых разработчики иногда принимают не самые оптимальные решения, гоняя по сети мегабайты трафика при общении с сервером PostgreSQL - а заодно посмотрим, как можно увидеть такую ситуацию в плане с помощью explain.tensor.ru и подумаем над вариантами, как сделать подобное взаимодействие более эффективным.
Сервер PostgreSQL тонет в неэффективных запросах
query = 'SELECT * FROM docs WHERE id IN (' + ids.join(',') + ')';
Помимо очевидных проблем с безопасностью из-за возможных SQL-инъекций, такой подход в большинстве случаев не позволяет драйверу понять, что подобный запрос уже передавался, и применить автоматическое использование подготовленных запросов.
Классическим решением в этой ситуации будет разделение тела запроса и его параметров:
query = 'SELECT * FROM docs WHERE id = ANY($1::integer[])';
...
params = '{' + ids.join(',') + '}'; // текстовое представление массива
Более полно с разными вариантами эффективной передачи данных в запрос можно ознакомиться в статье "PostgreSQL Antipatterns: передача наборов и выборок в SQL".
INSERT INTO users(id, name) VALUES(1, 'Vasya');
INSERT INTO users(id, name) VALUES(2, 'Petya');
INSERT INTO users(id, name) VALUES(3, 'Kolya');
Более продвинутый уже знает, что в один INSERT-оператор можно передавать сразу несколько строк:
INSERT INTO users(id, name)
VALUES
(1, 'Vasya')
, (2, 'Petya')
, (3, 'Kolya');
Еще более опытный, уже обжегшийся на предыдущем пункте с SQL-инъекциями, использует $n-параметры в INSERT:
INSERT INTO users(id, name)
VALUES
($1, $2)
, ($3, $4)
, ($5, $6);
Как думаете, быстро ли сервер может разобрать строку из нескольких сотен параметров объемом в пару десятков мегабайт?.. Мне приходилось сталкиваться со сгенерированными INSERT, где "номерные" параметры доходили до $9000.
Собственно, а зачем нам сначала нагружать клиента преобразованием всех параметров в заведомо неоптимальный текстовый формат, генерацией тела INSERT, а затем сервер разбором всего этого обратно? Ведь есть оператор COPY, который позволяет передавать данные для вставки в гораздо более эффективном текстовом, и даже двоичном форматах?
COPY users(id, name) FROM stdin;
1\tVasya\n2\tPetya\n3\tKolya\n
\.
INSERT INTO users(id, name, department)
SELECT
us[1]::integer
, us[2]::text
, us[3]::text
FROM
(
SELECT
us::text[]
FROM
unnest($1::text[]) us
) T
ON CONFLICT
DO NOTHING;
$1 = '{"{1,Vasya,Developers}","{2,Petya,Developers}","{3,Kolya,Developers}","{4,Masha,Support}","{5,Sasha,Support}"}'
Нетрудно заметить, что названия отделов у нас наверняка будут многократно дублироваться, поэтому заранее аккуратно сложив данные в JSON-формате, можно существенно сэкономить на трафике:
INSERT INTO users(id, name, department)
SELECT
(val->>0)::integer -- взяли нужный элемент json-массива
, val->>1
, dep
FROM
(
SELECT
json_array_elements(value) val -- развернули массивы-людей
, key dep
FROM
json_each($1::json) -- развернули ключи-отделы
) T
ON CONFLICT
DO NOTHING;
$1 = '{"Developers":[[1,"Vasya"],[2,"Petya"],[3,"Kolya"]],"Support":[[4,"Masha"],[5,"Sasha"]]}'
CREATE INDEX tbl(id, ts DESC);
Многие с готовностью вспомнят, что я неоднократно рекомендовал использовать в подобных случаях UNION ALL, чтобы не происходило деградации скорости запроса при Bitmap Scan - и в статье "PostgreSQL Antipatterns: вредные JOIN и OR", и в "Рецепты для хворающих SQL-запросов":
(
SELECT * FROM tbl WHERE id = 1 ORDER BY ts DESC LIMIT 1
)
UNION ALL
(
SELECT * FROM tbl WHERE id = 2 ORDER BY ts DESC LIMIT 1
)
UNION ALL
(
SELECT * FROM tbl WHERE id = 3 ORDER BY ts DESC LIMIT 1
)
И вот тут-то уж никак не избежать генерации запроса! Или все-таки есть способ?..
Нам ведь ничто не мешает вместо цикла, генерирующего тело запроса, использовать итерации внутри самого запроса:
SELECT
T.*
FROM
unnest('{1,2,3}'::integer[]) _id
, LATERAL ( -- выполняется отдельно для каждого ID
SELECT
*
FROM
tbl
WHERE
id = _id
ORDER BY
ts DESC
LIMIT 1
) T;
Немного более подробно о примерах использования LATERAL можно прочитать у Hans-Jürgen Schönig в "Understanding LATERAL joins in PostgreSQL" и у Luca Ferrari в "A simple example of LATERAL use".
Альтернативой использованию LATERAL может стать связка ARRAY + unnest по модели описанной в "SQL HowTo: пишем while-цикл прямо в запросе".
SELECT
ARRAY["2021-04", "2021-05", "2021-06"] -- это мы столбцы складываем в массив
FROM
(
SELECT
sum(
CASE
WHEN dt >= '2021-04-01' AND dt < '2021-05-01'
THEN qty
END
) "2021-04"
, sum(
CASE
WHEN dt >= '2021-05-01' AND dt < '2021-06-01'
THEN qty
END
) "2021-05"
, sum(
CASE
WHEN dt >= '2021-06-01' AND dt < '2021-07-01'
THEN qty
END
) "2021-06" -- это имена столбцов
FROM
sales
WHERE
dt >= '2021-04-01' AND dt < '2021-07-01'
) T;
В зависимости от реальной задачи, нормальным решением может стать рекурсивный запрос, использование функции generate_series или даже простая группировка:
SELECT
array_agg(sum ORDER BY id)
FROM
(
SELECT
date_trunc('month', dt) id
, sum(qty)
FROM
sales
WHERE
dt >= '2021-04-01' AND dt < '2021-07-01'
GROUP BY
1
) T;
Тут можно только лишь посоветовать "учить матчасть" и расширять кругозор на предмет возможностей, которые может вам предоставить PostgreSQL.
Поэтому мы сосредоточимся на вопросе "почему" в обмене клиент-сервер могут возникнуть избыточные данные.
ids <- 'SELECT id FROM users WHERE department = $1';
'SELECT * FROM docs WHERE user IN (' + ids.join(',') + ')'
Но грамотно написанный именно на стороне SQL запрос легко устранит подобную проблему:
SELECT
*
FROM
docs
WHERE
user IN (
SELECT id FROM users WHERE department = $1
);
1405 строк по 101 байту - получается примерно 139KB к передаче
Вычисляется данный размер достаточно просто: умножаем плановую "ширину" (width) возвращаемых записей на их реальное количество (actual rows), возвращенных корневым узлом плана.
Подсветка значений в узле плана
Отброшено 99% записей
Сложная иерархия с двумя CTE
curl -X POST https://explain.tensor.ru/explain \
-H "Content-Type: application/json" \
-d @FILENAME
# тут FILENAME – путь к файлу, содержащему параметры вызова в виде JSON-объекта
Пользуйтесь!
habr.com
Поэтому сегодня рассмотрим некоторые типичные ситуации, в которых разработчики иногда принимают не самые оптимальные решения, гоняя по сети мегабайты трафика при общении с сервером PostgreSQL - а заодно посмотрим, как можно увидеть такую ситуацию в плане с помощью explain.tensor.ru и подумаем над вариантами, как сделать подобное взаимодействие более эффективным.
Сервер PostgreSQL тонет в неэффективных запросах
Запросы и их параметры
Чем больший объем данных приходится передавать на сервер, тем больше ресурсов PostgreSQL тратит на их парсинг вместо выполнения самого запроса.Данные в теле SQL
Классический антипаттерн в этом ключе - прямая "врезка" параметров прямо в тело запроса:query = 'SELECT * FROM docs WHERE id IN (' + ids.join(',') + ')';
Помимо очевидных проблем с безопасностью из-за возможных SQL-инъекций, такой подход в большинстве случаев не позволяет драйверу понять, что подобный запрос уже передавался, и применить автоматическое использование подготовленных запросов.
Классическим решением в этой ситуации будет разделение тела запроса и его параметров:
query = 'SELECT * FROM docs WHERE id = ANY($1::integer[])';
...
params = '{' + ids.join(',') + '}'; // текстовое представление массива
Более полно с разными вариантами эффективной передачи данных в запрос можно ознакомиться в статье "PostgreSQL Antipatterns: передача наборов и выборок в SQL".
INSERT vs COPY
Иногда возникает необходимость вставить в таблицу сразу много-много записей. Начинающий разработчик в этом случае обычно "клеит" построчно операторы вставки:INSERT INTO users(id, name) VALUES(1, 'Vasya');
INSERT INTO users(id, name) VALUES(2, 'Petya');
INSERT INTO users(id, name) VALUES(3, 'Kolya');
Более продвинутый уже знает, что в один INSERT-оператор можно передавать сразу несколько строк:
INSERT INTO users(id, name)
VALUES
(1, 'Vasya')
, (2, 'Petya')
, (3, 'Kolya');
Еще более опытный, уже обжегшийся на предыдущем пункте с SQL-инъекциями, использует $n-параметры в INSERT:
INSERT INTO users(id, name)
VALUES
($1, $2)
, ($3, $4)
, ($5, $6);
Как думаете, быстро ли сервер может разобрать строку из нескольких сотен параметров объемом в пару десятков мегабайт?.. Мне приходилось сталкиваться со сгенерированными INSERT, где "номерные" параметры доходили до $9000.
Собственно, а зачем нам сначала нагружать клиента преобразованием всех параметров в заведомо неоптимальный текстовый формат, генерацией тела INSERT, а затем сервер разбором всего этого обратно? Ведь есть оператор COPY, который позволяет передавать данные для вставки в гораздо более эффективном текстовом, и даже двоичном форматах?
COPY users(id, name) FROM stdin;
1\tVasya\n2\tPetya\n3\tKolya\n
\.
Клон-значения параметров
Допустим, COPY вам все-таки не подходит, поскольку вы не можете гарантировать отсутствие пересечений вставляемых данных с уже находящимися в таблице, поэтому приходится использовать INSERT ... ON CONFLICT и передавать тонну параметров:INSERT INTO users(id, name, department)
SELECT
us[1]::integer
, us[2]::text
, us[3]::text
FROM
(
SELECT
us::text[]
FROM
unnest($1::text[]) us
) T
ON CONFLICT
DO NOTHING;
$1 = '{"{1,Vasya,Developers}","{2,Petya,Developers}","{3,Kolya,Developers}","{4,Masha,Support}","{5,Sasha,Support}"}'
Нетрудно заметить, что названия отделов у нас наверняка будут многократно дублироваться, поэтому заранее аккуратно сложив данные в JSON-формате, можно существенно сэкономить на трафике:
INSERT INTO users(id, name, department)
SELECT
(val->>0)::integer -- взяли нужный элемент json-массива
, val->>1
, dep
FROM
(
SELECT
json_array_elements(value) val -- развернули массивы-людей
, key dep
FROM
json_each($1::json) -- развернули ключи-отделы
) T
ON CONFLICT
DO NOTHING;
$1 = '{"Developers":[[1,"Vasya"],[2,"Petya"],[3,"Kolya"]],"Support":[[4,"Masha"],[5,"Sasha"]]}'
Сгенерированный SELECT
Но что если часть запрос действительно необходимо повторить, чтобы эффективно использовать индекс?.. Допустим, мы хотим получить последние по времени записи по каждому из нескольких идентификаторов, когда у нас есть индекс:CREATE INDEX tbl(id, ts DESC);
Многие с готовностью вспомнят, что я неоднократно рекомендовал использовать в подобных случаях UNION ALL, чтобы не происходило деградации скорости запроса при Bitmap Scan - и в статье "PostgreSQL Antipatterns: вредные JOIN и OR", и в "Рецепты для хворающих SQL-запросов":
(
SELECT * FROM tbl WHERE id = 1 ORDER BY ts DESC LIMIT 1
)
UNION ALL
(
SELECT * FROM tbl WHERE id = 2 ORDER BY ts DESC LIMIT 1
)
UNION ALL
(
SELECT * FROM tbl WHERE id = 3 ORDER BY ts DESC LIMIT 1
)
И вот тут-то уж никак не избежать генерации запроса! Или все-таки есть способ?..
Нам ведь ничто не мешает вместо цикла, генерирующего тело запроса, использовать итерации внутри самого запроса:
SELECT
T.*
FROM
unnest('{1,2,3}'::integer[]) _id
, LATERAL ( -- выполняется отдельно для каждого ID
SELECT
*
FROM
tbl
WHERE
id = _id
ORDER BY
ts DESC
LIMIT 1
) T;
Немного более подробно о примерах использования LATERAL можно прочитать у Hans-Jürgen Schönig в "Understanding LATERAL joins in PostgreSQL" и у Luca Ferrari в "A simple example of LATERAL use".
Альтернативой использованию LATERAL может стать связка ARRAY + unnest по модели описанной в "SQL HowTo: пишем while-цикл прямо в запросе".
Промежуточные столбцы (снова генерация)
Например, нам надо вывести массив с количеством продаж за каждый из трех месяцев 2-го квартала. Самый странный вариант решения этой задачи, который мне доводилось видеть, генерировал промежуточные столбцы в тело SQL и выглядел примерно так:SELECT
ARRAY["2021-04", "2021-05", "2021-06"] -- это мы столбцы складываем в массив
FROM
(
SELECT
sum(
CASE
WHEN dt >= '2021-04-01' AND dt < '2021-05-01'
THEN qty
END
) "2021-04"
, sum(
CASE
WHEN dt >= '2021-05-01' AND dt < '2021-06-01'
THEN qty
END
) "2021-05"
, sum(
CASE
WHEN dt >= '2021-06-01' AND dt < '2021-07-01'
THEN qty
END
) "2021-06" -- это имена столбцов
FROM
sales
WHERE
dt >= '2021-04-01' AND dt < '2021-07-01'
) T;
В зависимости от реальной задачи, нормальным решением может стать рекурсивный запрос, использование функции generate_series или даже простая группировка:
SELECT
array_agg(sum ORDER BY id)
FROM
(
SELECT
date_trunc('month', dt) id
, sum(qty)
FROM
sales
WHERE
dt >= '2021-04-01' AND dt < '2021-07-01'
GROUP BY
1
) T;
Тут можно только лишь посоветовать "учить матчасть" и расширять кругозор на предмет возможностей, которые может вам предоставить PostgreSQL.
Возврат результатов
Влияние сетевых задержек при разных вариантах возврата результатов (много/мало, сразу все или дольками, клиентским или серверным курсором) на общее время выполнения запроса Jobin Augustine подробно разобрал в недавно опубликованной в блоге Percona статье "Impact of Network and Cursor on Query Performance of PostgreSQL".Поэтому мы сосредоточимся на вопросе "почему" в обмене клиент-сервер могут возникнуть избыточные данные.
Без(д)умное использование ORM
В силу особенностей ORM (или неумения им пользоваться) могут возникать вот такие "двухходовки", в которых туда и обратно между сервером и клиентским приложением гоняются пачки одинаковых данных:ids <- 'SELECT id FROM users WHERE department = $1';
'SELECT * FROM docs WHERE user IN (' + ids.join(',') + ')'
Но грамотно написанный именно на стороне SQL запрос легко устранит подобную проблему:
SELECT
*
FROM
docs
WHERE
user IN (
SELECT id FROM users WHERE department = $1
);
Обработка данных на клиенте
Вариант, описанный в статье "PostgreSQL Antipatterns: навигация по реестру", когда для реализации постраничной навигации на клиента вычитывается вся таблица целиком, в жизни встречается нечасто, но вот незнание каких-то возможностей PostgreSQL запросто может приводить к вычитке избыточных данных на клиента.Мониторинг размера resultset
Чтобы упростить поиск и анализ подобных ситуаций, мы добавили на explain.tensor.ru отображение примерного объема данных, возвращаемых запросом:
1405 строк по 101 байту - получается примерно 139KB к передаче
Вычисляется данный размер достаточно просто: умножаем плановую "ширину" (width) возвращаемых записей на их реальное количество (actual rows), возвращенных корневым узлом плана.
Визуализация EXPLAIN - что еще у нас нового?
Помимо оценки размера resultset, мы еще немного доработали возможности нашего сервиса визуализации планов.Подсветка значений
Числовые, строковые и "атрибутные" значения в развернутом виде узла теперь подкрашиваются, чтобы их можно было почти мгновенно заметить:
Подсветка значений в узле плана
Отметка фильтрующих узлов
Теперь обнаружить узлы, где отбрасываются какие-то из уже прочитанных из базы записей, стало намного проще - у каждого такого узла теперь слева есть метка с цветом, соответствующим доле отфильтрованного:
Отброшено 99% записей
Иерархия сложных планов
Теперь в сложных планов со множеством вложенных CTE/InitPlan/SubPlan можно наглядно отследить, где кончается поддерево конкретного узла, и кто является его прямыми потомками:
Сложная иерархия с двумя CTE
Публичный API
Теперь вы можете автоматизировать отправку планов из своих систем для их визуализации и дальнейшего анализа, используя API нашего сервиса:curl -X POST https://explain.tensor.ru/explain \
-H "Content-Type: application/json" \
-d @FILENAME
# тут FILENAME – путь к файлу, содержащему параметры вызова в виде JSON-объекта
Пользуйтесь!
PostgreSQL Antipatterns: «слишком много золота»
Иногда мы пишем SQL-запросы, мало задумываясь над тем фактом, что сначала они должны быть по сети как-то доставлены до сервера, а затем их результат - обратно в клиентское приложение. Если при этом...
habr.com