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	1048ms
to_json	833ms	-21%
row_to_json	717ms	-32%

Заметим, что только в самом медленном варианте с 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_build_object по нужным ключам	1148ms	-69%
to_jsonb - key	3705ms
row_to_json по выборке	792ms	-79%

---

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