Почему поиск в полях JSONB увеличивает время запроса в 62 раза в Postgres?

Question

Почему поиск в полях JSONB увеличивает время запроса в 62 раза в Postgres?

Я выполняю простой запрос, который извлекает данные из одной таблицы. Если я смотрю только не-JSON поля, которые находятся в таблице, запрос занимает 16 мс. Если я включу поля, которые ссылаются на поля в данных JSONB, то он увеличивается в 62 раза. Если я посмотрю два разных поля JSONB, то удвою это.

--EXPLAIN (ANALYZE,buffers)
SELECT 
  segment as segment_no,
  begin_time,
  segment_data::json->'summary'->'begin_milage' as begin_milage,
  segment_data::json->'summary'->'end_milage' as end_milage
FROM
  segments_table
WHERE
  vehicle=12 AND trip=3
ORDER BY
  begin_time;

Запрос занимает 2,0 секунды с двумя полями JSON, включенными в предложение SELECT. Если пропустить одно, это займет 1,0 с, если пропустить оба поля JSON, тогда запрос займет всего 16 мс.

Сама таблица имеет около 700 записей. Запрос возвращает 83 записи. Запуск разных запросов Я заметил, что чем больше записей возвращается, тем больше времени требуется для выполнения запроса (приблизительно 0,0066 * X^1,32 мс) при запросе 2 полей JSON.

Я попытался добавить индекс для транспортного средства и поиска поездки, но это не имело большого значения (как ожидалось). Кажется, что это фактический поиск данных, а поиск данных в поле JSONB - это то, что требует времени. Теперь, если бы поля JSON были необходимы в предложении WHERE, было бы более понятно увидеть такое ухудшение, но это не так.

Конечно, простым решением было бы вытащить каждое поле из BLOB-объекта JSON и создать для него отдельные поля в самой таблице. Но прежде чем идти по этому пути, есть ли что-нибудь еще, что могло бы решить эту проблему производительности?

Вот результаты для АНАЛИЗА:

Sort  (cost=13.25..13.27 rows=10 width=28) (actual time=1999.899..1999.901 rows=71 loops=1)
  Sort Key: begin_time
  Sort Method: quicksort  Memory: 35kB
  Buffers: shared hit=5663
  ->  Bitmap Heap Scan on segments_table  (cost=4.38..13.08 rows=10 width=28) (actual time=1.332..1999.730 rows=71 loops=1)
        Recheck Cond: ((vehicle = 644) AND (trip = 3))
        Heap Blocks: exact=3
        Buffers: shared hit=5663
        ->  Bitmap Index Scan on segments_table_vehicle_64df5bc5_uniq  (cost=0.00..4.38 rows=10 width=0) (actual time=0.052..0.052 rows=71 loops=1)
              Index Cond: ((vehicle = 644) AND (trip = 3))
              Buffers: shared hit=2
Planning time: 0.368 ms
Execution time: 2000.000 ms

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

Единственные изменения, которые я внес в стандартную конфигурацию сервера postgres, - это увеличение shared_buffers от 128 МБ до 256 МБ и настройка effective_cache_size = 1GB, Я также уменьшил max_connections от 100 до 20.

Вышеуказанные результаты работают под Win7 на 8-ядерном процессоре i7. Также провели тот же тест под Ubuntu на двухъядерном процессоре, и запрос занял примерно столько же: 2,2 секунды (при включении двух полей JSONB в предложение SELECT).

Обновление:

Одно поле JSON в классе SELECT:

EXPLAIN (ANALYZE,buffers)
SELECT 
  segment as segment_no,
  begin_time, 
  segment_data::json->'summary'->'end_mileage' as end_mileage
FROM
  segments_table
WHERE
  vehicle=644 AND trip=3
ORDER BY
  begin_time;

Результат:

Sort  (cost=13.15..13.17 rows=10 width=28) (actual time=999.695..999.696 rows=71 loops=1)
  Sort Key: begin_time
  Sort Method: quicksort  Memory: 26kB
  Buffers: shared hit=2834
  ->  Bitmap Heap Scan on segments_table  (cost=4.38..12.98 rows=10 width=28) (actual time=0.781..999.554 rows=71 loops=1)
        Recheck Cond: ((vehicle = 644) AND (trip = 3))
        Heap Blocks: exact=3
        Buffers: shared hit=2834
        ->  Bitmap Index Scan on segments_table_vehicle_64df5bc5_uniq  (cost=0.00..4.38 rows=10 width=0) (actual time=0.052..0.052 rows=71 loops=1)
              Index Cond: ((vehicle = 644) AND (trip = 3))
              Buffers: shared hit=2
Planning time: 0.353 ms
Execution time: 999.777 ms

Нет поля JSON в предложении SELECT:

EXPLAIN (ANALYZE,buffers)
SELECT 
  segment as segment_no,
  begin_time
FROM
  segments_table
WHERE
  vehicle=644 AND trip=3
ORDER BY
  begin_time;

Результат:

Sort  (cost=13.05..13.07 rows=10 width=10) (actual time=0.194..0.205 rows=71 loops=1)
  Sort Key: begin_time
  Sort Method: quicksort  Memory: 19kB
  Buffers: shared hit=5
  ->  Bitmap Heap Scan on segments_table  (cost=4.38..12.88 rows=10 width=10) (actual time=0.088..0.122 rows=71 loops=1)
        Recheck Cond: ((vehicle = 644) AND (trip = 3))
        Heap Blocks: exact=3
        Buffers: shared hit=5
        ->  Bitmap Index Scan on segments_table_vehicle_64df5bc5_uniq  (cost=0.00..4.38 rows=10 width=0) (actual time=0.048..0.048 rows=71 loops=1)
              Index Cond: ((vehicle = 644) AND (trip = 3))
              Buffers: shared hit=2
Planning time: 0.590 ms
Execution time: 0.280 ms

Определение таблицы:

CREATE TABLE public.segments_table
(
  segment_id integer NOT NULL DEFAULT nextval('segments_table_segment_id_seq'::regclass),
  vehicle smallint NOT NULL,
  trip smallint NOT NULL,
  segment smallint NOT NULL,
  begin_time timestamp without time zone NOT NULL,
  segment_data jsonb,
  CONSTRAINT segments_table_pkey PRIMARY KEY (segment_id),
  CONSTRAINT segments_table_vehicle_64df5bc5_uniq UNIQUE (vehicle, trip, segment, begin_time)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX segments
  ON public.segments_table
  USING btree
  (segment);

CREATE INDEX vehicles
  ON public.segments_table
  USING btree
  (vehicle);

CREATE INDEX trips
  ON public.segments_table
  USING btree
  (trip);

Обновление № 2:

Исправление проблемы с кастингом, как указано @Mark_M, изменение json to jsonb` уменьшает время запроса с 2 с до 300 мс:

EXPLAIN (ANALYZE,buffers)
SELECT 
  segment as segment_no,
  begin_time,
  segment_data::jsonb->'summary'->'begin_mileage' as begin_mileage,
  segment_data::jsonb->'summary'->'end_mileage' as end_mileage
FROM
  segments_table
WHERE
  vehicle=644 AND trip=3
ORDER BY
  begin_time;

  Sort  (cost=13.15..13.17 rows=10 width=28) (actual time=296.339..296.342 rows=71 loops=1)
  Sort Key: begin_time
  Sort Method: quicksort  Memory: 35kB
  Buffers: shared hit=5663
  ->  Bitmap Heap Scan on segments_table  (cost=4.38..12.98 rows=10 width=28) (actual time=0.275..296.229 rows=71 loops=1)
        Recheck Cond: ((vehicle = 644) AND (trip = 3))
        Heap Blocks: exact=3
        Buffers: shared hit=5663
        ->  Bitmap Index Scan on segments_table_vehicle_64df5bc5_uniq  (cost=0.00..4.38 rows=10 width=0) (actual time=0.045..0.045 rows=71 loops=1)
              Index Cond: ((vehicle = 644) AND (trip = 3))
              Buffers: shared hit=2
Planning time: 0.352 ms
Execution time: 296.473 ms

Это немного улучшилось, но все же в 18 раз просто поиск с использованием полей не-JSON, но это намного лучше. Это разумное снижение производительности для использования поля JSONB?

2

json postgresql database-design database-performance database-caching

Источник

user759673 20 сен '17 в 18:25

0 ответов

Другие вопросы по тегам json postgresql database-design database-performance database-caching