Как правильно сделать фильтрацию по числу JSONB в PostgreSQL используя индекс?

Question

[danforth]

Есть примерно такая структура: sqlfiddle.com/#!17/6cea9/1

Всего около 2 млн. товаров, все запросы с подмножествами выполняются быстро, в пределах 20 мс. максимум, а вот запрос с использованием числового сравнения выполняется долго.
1. Какой нужно индекс повесить и как нужно писать запрос, чтобы этот индекс использовался? Пробовал обычный (не gin) индекс, а также btree. Но если писать (features->>'capacity')::int < 300, то запрос все равно отрабатывает очень долго (около 300-500 мс.).

2. Есть ли какой-то универсальный способ выборки со сравнением по числу, чтобы не индексировать каждое поле JSON объекта? Сегодня там capacity, завтра color в десятичном формате, каждый раз вешать индекс при добавлении числовой характеристики не очень хочется.

upd.: при запросе

EXPLAIN (ANALYSE, BUFFERS) SELECT id, name, price, features FROM product
WHERE (features->>'color')::int > 1000000
ORDER BY price ASC
LIMIT 25

получаю:

EXPLAIN

Limit  (cost=0.43..10.56 rows=25 width=161) (actual time=0.018..0.093 rows=25 loops=1)
  Buffers: shared hit=51
  ->  Index Scan Backward using product_price_index on product  (cost=0.43..317546.33 rows=783959 width=161) (actual time=0.016..0.071 rows=25 loops=1)
        Filter: (((features ->> 'color'::text))::integer > 1000000)
        Rows Removed by Filter: 23
        Buffers: shared hit=51
Planning time: 0.068 ms
Execution time: 0.120 ms

Т.е. индекс используется, хотя в данном случае index срабатывает на price столбце? Тогда почему запрос отрабатывает быстро? Как заставить работать индекс, если условий WHERE несколько?

Answer 1

[Melkij]

Индекс возможно повесить функциональный:

create index on tablename using btree(((features ->> 'capacity'::text)::integer));

Соответственно предикат по этому же самому выражению получит возможность использовать этот индекс.

Больше возможностей внятно индексировать jsonb для запросов на числовые диапазоны мне как-то не вспоминается. Сортировку по полю-то только btree и умеет из всех актуальных access method.

Т.е. индекс используется, хотя в данном случае index срабатывает на price столбце? Тогда почему запрос отрабатывает быстро?

Да, индекс используется - индекс по price. Потому что вы по нему сортируете. И планировщик опираясь на свою статистику надеется, что сможет быстро найти 25 строк читая строки в порядке требуемой сортировки по индексу и по пути проверять выкидывать неподходящие.

Comments

[danforth]

использовать в запросах также как и без индекса: кастуем к int и сравниваем?
получается, что каждую числовую характеристику нужно индексировать руками? :( очень не удобно. хотел взять как альтернативу EAV, попробовать на проект, и вот это сейчас заставило в серьёз задуматься.

[Melkij]

в общем случае выражение в индексе должно совпадать с тем, что пишем в запросе. Для красоты можно сделать immutable language sql хранимку с jsonb и именем параметра на входе и числом на выходе.
Для фильтров каталога вроде серебряной пули не сделали ещё