#postgresql #indexing #jsonb
#postgresql #индексирование #jsonb
Вопрос:
Я пытаюсь проиндексировать большой столбец JSONB на основе текстового поля (со строкой даты ISO). Этот индекс отлично работает с использованием =, но игнорируется, если я использую условие.
create table test_table (
id text NOT null primary key,
data jsonb,
text_test text
);
Затем я добавляю кучу данных в столбец jsonb. И чтобы убедиться, что мой JSON действителен, извлеките / скопируйте значение, которое меня интересует, из столбца JSONB в другой текстовый столбец для проверки.
update test_table set text_test = (data->>'dueDate');
Краткий пример показывает, что это хорошие строки даты в формате ISO:
select text_test, (data->>'dueDate') from test_table limit 1;
-- 2020-08-07T11:59:00 2020-08-07T11:59:00
Я добавляю индексы btree как в столбец JSONB, так и в столбец text_test copy. Я попытался добавить один с явным приведением ‘:: text’, а также один с ‘text_pattern_ops’.
create index test_table_duedate_iso on test_table using btree(text_test);
create index test_table_duedate_iso_jsonb on test_table using btree((data->>'dueDate'));
create index test_table_duedate_iso_jsonb_cast on test_table using btree(((data->>'dueDate')::text));
create index test_table_duedate_iso_jsonb_cast_pattern on test_table using btree(((data->>'dueDate')::text) text_pattern_ops);
Теперь, если я запрашиваю точное значение, explain показывает его, используя «приведенную» версию индекса. Хорошо.
explain select * from test_table where (data->>'dueDate') = '2020-08-07T11:59:00';
"-> Bitmap Index Scan on test_table_duedate_iso_jsonb_cast (cost=0.00..10.37 rows=261 width=0)"
Но если я попробую это с помощью a >, он выполняет очень медленное полное сканирование.
explain analyze select count(*) from test_table where (data->>'dueDate') > '2020-04-14';
--Aggregate (cost=10037.94..10037.95 rows=1 width=8) (actual time=1070.808..1070.813 rows=1 loops=1)
-- -> Seq Scan on test_table (cost=0.00..9994.42 rows=17409 width=0) (actual time=0.069..1057.258 rows=2930 loops=1)
-- Filter: ((data ->> 'dueDate'::text) > '2020-04-14'::text)
-- Rows Removed by Filter: 49298
--Planning Time: 0.252 ms
--Execution Time: 1070.874 ms
Итак, просто чтобы проверить мое здравомыслие, я делаю тот же запрос к столбцу text_test, он использует его индекс по желанию:
explain analyze select count(*) from test_table where text_test > '2020-04-14';
--Aggregate (cost=6037.02..6037.03 rows=1 width=8) (actual time=19.979..19.984 rows=1 loops=1)
-- -> Bitmap Heap Scan on test_table (cost=77.76..6030.14 rows=2754 width=0) (actual time=1.354..11.007 rows=2930 loops=1)
-- Recheck Cond: (text_test > '2020-04-14'::text)
-- Heap Blocks: exact=455
-- -> Bitmap Index Scan on test_table_duedate_iso (cost=0.00..77.07 rows=2754 width=0) (actual time=1.215..1.217 rows=2930 loops=1)
-- Index Cond: (text_test > '2020-04-14'::text)
--Planning Time: 0.145 ms
--Execution Time: 20.041 ms
Я также протестировал индексирование числового поля в JSON, и оно действительно работает правильно, используя его индекс для запросов с ранжированным типом. Так что это что-то связанное с текстовым полем или что-то, что я делаю с ним неправильно.
PostgreSQL 11.5 на x86_64-pc-linux-gnu, скомпилированный gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14), 64-разрядный
Комментарии:
1. Возможно, это как-то связано с тем фактом, что Postgres не ведет статистику по значениям jsonb. Он просто предполагает равную 1%-ную селективность. Попробуйте отключить растровое сканирование, просто чтобы проверить эту теорию. установите enable_bitmapscan = выкл.;
2. Почему вы используете BTREE вместо ДЖИНА? BTREE вряд ли полезен для jsonb. Другая проблема заключается в том, что теперь вы пытаетесь сравнить текстовое поле (data->> ‘DueDate’) с датой. Меньше и больше не имеют большого смысла при использовании текстовых полей.
3. @jjanes Извините, я перепутал тестовые примеры, которые я включил при создании вопроса (<против >). Я обновил его, чтобы он был согласованным, и добавил дополнительные детали анализа.
4. @FrankHeikens Насколько я понимаю, я эффективно создаю функциональный индекс для одного атрибута JSONB. И поскольку я хочу выполнить поиск по диапазону, btree хорошо подходит. Я думал, что gin больше подходит для поиска и составного поиска, что мне не нужно. И я нигде не использую даты, это весь текст из-за ограничений JSON и ограничений индекса to_date, основанных на изменяемости.
5. Спасибо за исправление направления сравнения. Фактическое количество строк одно и то же, но ожидаемые значения сильно отличаются. Вы АНАЛИЗИРОВАЛИ таблицу после создания функционального индекса? Функциональные индексы имеют свою собственную статистику, но они не собираются, пока таблица не будет проанализирована. И простое создание функционального индекса не приведет к автоматическому анализу.