Индексы лля jsonb

@miclee1380 · Регистрация: 28.07.2013

Студворк — интернет-сервис помощи студентам

Привет! Недавно начали переход на PostgreSQL, есть неясности с индексацией поля типа jsonb. Создаю индекс для этого поля:

SQL
1
CREATE INDEX idx_gin_table__data_place_id ON TABLE USING gin (DATA);

Пробую сделать вот это:

SQL
1
2
3
EXPLAIN ANALYSE SELECT DISTINCT DATA -> 'place' ->> 'id' AS "place_id"
FROM "table"
ORDER BY "place_id;

Пишет вот что:

Bash
1
2
3
4
5
6
7
Unique  (cost=10933.37..11274.31 rows=68188 width=32) (actual time=1409.897..1420.983 rows=42 loops=1);
Planning time: 0.177 ms;
Execution time: 1422.426 ms;
  ->  Sort  (cost=10933.37..11103.84 rows=68188 width=32) (actual time=1409.896..1417.524 rows=68188 loops=1);
        Sort Method: external merge  Disk: 1000kB;
        Sort Key: (((data -> ''place''::text) ->> ''id''::text));
        ->  Seq Scan on table  (cost=0.00..5458.82 rows=68188 width=32) (actual time=0.161..1124.366 rows=68188 loops=1);

То есть выполняется очень долго, индекс не юзается. Подскажите, как правильно создать индекс или как правильно выбрать уникальные значения из вложенных полей jsonb? Заранее спасибо!

@grgdvo · 25.12.2019, 22:15

Общее утверждение:
distinct по логике работы практически не поддается оптимизации.
Чтобы выбрать все уникальные, нужно просмотреть все записи - отсюда SeqScan как основное средств просмотра всей таблицы и никакой индекс здесь не поможет. Его можно заменить на IndexScan, но все равно нужно захватить все записи, еще там возникают вопросы с NULL.

По вашему плану:
1) Во-первых, external sort всего исходного множества записей. Очень медленно! Зачем сортировать все на первом шаге выполнения??
Так никакого work_mem не хватит такие сортировки делать.
Естественно перебрали work_mem и попали на external sort через временный файл - Очень медленно!
Покажите что у вас с параметрами планировщика запроса. hashagg отключен что ли или еще что-то отключено??
Кстати какая версия ПГ?
2) Во-вторых, с диска что ли долго читает?! Добавьте buffers и timing в explain, чтобы понимать сколько дисковые операции занимают время. Также сделать ANALYZE и VACUUM таблице. Вообще прочитать, что это и зачем и использовать на регулярной основе. Особенно если частые изменения данных в таблице.

Вот игры с планами.
Сначала генерация начальных данных.

SQL
1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table
(
jdata jsonb
);
--truncate table test_table;
INSERT INTO test_table (jdata)
SELECT ('{"place": {"id": "'||chr((random()*100)::INT % 26 + 65)||'"}}')::jsonb
FROM generate_series(1, 100000) AS gs(x)

SQL
1
2
3
4
5
EXPLAIN analyze
SELECT DISTINCT 
jdata -> 'place' ->> 'id' AS "place_id" 
FROM "test_table"
ORDER BY "place_id";

План по умолчанию

Code
1
2
3
4
5
6
7
8
Sort  (cost=2585.00..2585.07 rows=26 width=32) (actual time=47.284..47.285 rows=26 loops=1)
  Sort Key: (((jdata -> 'place'::text) ->> 'id'::text))
  Sort Method: quicksort  Memory: 26kB
  ->  HashAggregate  (cost=2584.00..2584.39 rows=26 width=32) (actual time=47.259..47.262 rows=26 loops=1)
        Group Key: ((jdata -> 'place'::text) ->> 'id'::text)
        ->  Seq Scan on test_table  (cost=0.00..2334.00 rows=100000 width=32) (actual time=0.028..29.718 rows=100000 loops=1)
Planning Time: 0.090 ms
Execution Time: 47.331 ms

Отключаем hashagg

SQL
1
2
3
4
5
6
SET enable_hashagg=no
EXPLAIN analyze
SELECT DISTINCT 
jdata -> 'place' ->> 'id' AS "place_id" 
FROM "test_table"
ORDER BY "place_id";

Результат как у вас. Долго! Уже в два раза на моих простых модельных данных.

Code
1
2
3
4
5
6
7
Unique  (cost=13032.82..13532.82 rows=26 width=32) (actual time=75.491..94.645 rows=26 loops=1)
  ->  Sort  (cost=13032.82..13282.82 rows=100000 width=32) (actual time=75.490..86.548 rows=100000 loops=1)
        Sort Key: (((jdata -> 'place'::text) ->> 'id'::text))
        Sort Method: external merge  Disk: 1184kB
        ->  Seq Scan on test_table  (cost=0.00..2334.00 rows=100000 width=32) (actual time=0.017..30.060 rows=100000 loops=1)
Planning Time: 0.057 ms
Execution Time: 95.250 ms

Строим индекс! Обратите внимание, что GIN тут не поможет никак.
Строим btree индекс просто, чтобы получить сортированную последовательность значений, полученных в результате выполнения оператора "доставания" значений из JSON.

SQL
1
2
--drop index IF EXISTS idx_jdata_place_id;
CREATE INDEX idx_jdata_place_id ON "test_table" USING btree ((jdata -> 'place' ->> 'id'));

План:

Code
1
2
3
4
Unique  (cost=0.42..6698.42 rows=26 width=32) (actual time=0.024..63.434 rows=26 loops=1)
  ->  Index Scan using idx_jdata_place_id on test_table  (cost=0.42..6448.42 rows=100000 width=32) (actual time=0.023..56.494 rows=100000 loops=1)
Planning Time: 0.182 ms
Execution Time: 63.459 ms

И все равно проиграли hashagg.

@miclee1380 · 26.12.2019, 07:16 **[ТС]**

grgdvo, спасибо за очень подробный ответ! Версия postgresql9.6. Тесты на слабой машине проводим. Попробую с индексами поиграть и сортировку делать на php после получения.

@grgdvo · 26.12.2019, 21:06

Сообщение от miclee1380

Версия postgresql9.6.

Почему такая старая версия?? Обратите внимание, что не так много времени осталось на поддержку этой версии.
Если переезжаете, то переезжайте сразу на последнюю 11-ую - не меньше!!!. Много полезных исправлений и улучшений.

Новые блоги и статьи Все статьи Все блоги /
Символьное дифференцирование igorrr37 13.02.2026 / * Логарифм записывается как: (x-2)log(x^2+2) - означает логарифм (x^2+2) по основанию (x-2). Унарный минус обозначается как ! */ #include <iostream> #include <stack> #include <cctype>. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»
SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .	Установка Qt-версии Lazarus IDE в Debian Trixie Xfce volvo 10.02.2026 В общем, достали меня глюки IDE Лазаруса, собранной с использованием набора виджетов Gtk2 (конкретно: если набирать текст в редакторе и вызвать подсказку через Ctrl+Space, то после закрытия окошка. . .

@miclee1380 10 / 10 / 3 Регистрация: 28.07.2013 Сообщений: 181
	26.12.2019, 07:16 [ТС]
	grgdvo, спасибо за очень подробный ответ! Версия postgresql9.6. Тесты на слабой машине проводим. Попробую с индексами поиграть и сортировку делать на php после получения. 0