Индексы лля 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-ую - не меньше!!!. Много полезных исправлений и улучшений.

Новые блоги и статьи Все статьи Все блоги /
PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Access VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.	Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .	Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .

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