PostgreSQL Antipatterns: создаем JSON из строки

Я уже не раз поднимал в статьях тему [не]эффективной работы с json[b] в PostgreSQL — и как его лучше превращать в выборку, и как можно «транспонировать». Сегодня же рассмотрим некоторые возможности по его генерации из строки таблицы на стороне базы.

Пример из серии «не супер» взят с одного из запросов, отправленных в архив нашего сервиса визуализации планов запросов explain.tensor.ru:

SELECT   to_json(     json_build_object(       'team_id'     , team.team_id     , 'sport_id'     , team.sport_id     , 'team_name'     , team.team_name     , 'team_full'     , team.team_full     , 'team_url'     , team.team_url     , 'team_country'     , country.country_name     , 'country_id'     , team.country_id     , 'team_emblem'     , team.team_emblem     , 'download_date'     , team.download_date     , 'save_date'     , team.save_date     )   ) to_json_1 FROM   team JOIN   country     ON team.country_id = country.country_id

Первый же вопрос, который возникает — а зачем делать to_json(json_build_object(...))? Ведь функция json_build_objectи так уже готовый json возвращает — зачем его еще-то раз превращать в json? Видимо, это какие-то артефакты «давно минувших дней»…

На самом деле, для json-генерации по строке таблицы можно использовать обе эти функции, но незачем делать это одновременно.

Давайте грубо оценим их «скорострельность» на миллионе строк всего из одного поля, а заодно вспомним про еще одну функцию — row_to_json.

json_build_object (1048ms)

EXPLAIN (ANALYZE, COSTS off) WITH T AS (   SELECT generate_series(1, 1e6) i ) SELECT   json_build_object('i', i) json FROM   T;

Subquery Scan on t (actual time=0.010..1021.210 rows=1000000 loops=1)   ->  ProjectSet (actual time=0.004..210.837 rows=1000000 loops=1)         ->  Result (actual time=0.001..0.001 rows=1 loops=1) Planning Time: 0.052 ms Execution Time: 1048.717 ms

to_json (833ms)

EXPLAIN (ANALYZE, COSTS off) WITH T AS (   SELECT generate_series(1, 1e6) i ) SELECT   to_json(T) json FROM   T;

Subquery Scan on t (actual time=0.014..807.052 rows=1000000 loops=1)   ->  ProjectSet (actual time=0.004..202.262 rows=1000000 loops=1)         ->  Result (actual time=0.001..0.001 rows=1 loops=1) Planning Time: 0.057 ms Execution Time: 833.478 ms

row_to_json (717ms)

EXPLAIN (ANALYZE, COSTS off) WITH T AS (   SELECT generate_series(1, 1e6) i ) SELECT   row_to_json(T) json FROM   T;

Subquery Scan on t (actual time=0.012..690.754 rows=1000000 loops=1)   ->  ProjectSet (actual time=0.004..190.792 rows=1000000 loops=1)         ->  Result (actual time=0.001..0.001 rows=1 loops=1) Planning Time: 0.051 ms Execution Time: 717.296 ms

За исключением незначащих пробелов в текстовом представлении json, эти запросы дают эквивалентные результаты, но за очень разное время:

Заметим, что только в самом медленном варианте с json_build_object нам пришлось перечислять целевые поля и их значения «вручную», что особенно актуально в контексте исходного запроса.

Теперь мы знаем, что исходный запрос можно было бы переписать более эффективно примерно так:

SELECT   row_to_json(team) to_json_1 FROM   team JOIN   country     ON team.country_id = country.country_id

Исключение столбцов

Понятно, что вариант сконвертировать «всю» запись в json может быть и не слишком хорош, если там присутствует объемное текстовое поле, которое в итоговом объекте вовсе не требуется.

В этом случае у нас есть три варианта.

json_build_object по нужным ключам (1148ms)

EXPLAIN (ANALYZE, COSTS off) WITH T AS (   SELECT     i   , repeat(' ', i::integer % 256) s   FROM     generate_series(1, 1e6) i ) SELECT   json_build_object('i', i) json FROM   T;

Function Scan on generate_series i (actual time=235.115..1119.221 rows=1000000 loops=1) Planning Time: 0.063 ms Execution Time: 1148.314 ms

to_jsonb — key (3705ms)

EXPLAIN (ANALYZE, COSTS off) WITH T AS (   SELECT     i   , repeat(' ', i::integer % 256) s   FROM     generate_series(1, 1e6) i ) SELECT   to_jsonb(T) - 's' json FROM   T;

Function Scan on generate_series i (actual time=233.755..3650.783 rows=1000000 loops=1) Planning Time: 0.061 ms Execution Time: 3705.812 ms

Даже если забыть про наше желание получить json, а не jsonb, как тут, уже проигрыш 2.5x по времени.

row_to_json по выборке (792ms)

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

EXPLAIN (ANALYZE, COSTS off) WITH T AS (   SELECT     i   , repeat(' ', i::integer % 256) s   FROM     generate_series(1, 1e6) i ) SELECT   row_to_json(T) json FROM   (     SELECT       i     FROM       T   ) T;

Function Scan on generate_series i (actual time=233.817..762.789 rows=1000000 loops=1) Planning Time: 0.060 ms Execution Time: 792.618 ms

К сожалению, удобного исключения столбцов в PostgreSQL пока так и нет, поэтому получается следующее:

В общем, если вам надо из строки выборки получить json — используйте row_to_json, и будет вам счастье, а вашему серверу облегчение!