Блог

У меня есть таблица:

 CREATE TABLE skill (
    user_id integer NOT NULL,
    data jsonb
);
 

И индекс gin в столбце jsonb:

 CREATE INDEX idx_user_id_knowledge ON skill USING gin (data);

INSERT INTO skill(user_id, data)
VALUES (1, '{"technology": "PHP", "knowledge": 77 }'),
       (2, '{"technology": "PHP", "knowledge": 79 }'),
       (3, '{"technology": "PHP", "knowledge": 97 }'),
       (4, '{"technology": "MySQL", "knowledge": 85 }'),
       (5, '{"technology": "MySQL", "knowledge": 89 }');
 

Но когда я запускаю запрос EXPLAIN:

 EXPLAIN
SELECT * FROM skill
WHERE data->>'technology' = 'PHP' AND (data->>'knowledge')::NUMERIC > 50;
 

Результат выглядит следующим образом:

 Seq Scan on skill (cost=0.00..41.75 rows=2 width=36)
Filter: (((data ->> 'technology'::text) = 'PHP'::text) AND (((data ->> 'knowledge'::text))::numeric > '50'::numeric))
 

Почему планировщик запросов не использует созданный мной индекс gin вместо Seq. Сканировать?

http://sqlfiddle.com /#!17/35f2c/5

Индексы Gin TLDR не работают с таким оператором.

Глядя на операторы для GIN индексов, вы можете видеть, что единственными поддерживаемыми операторами для jsonb типов являются: ? ?amp; ?| @> @? @@

См. Документы по функциям и операторам JSON о том, что они означают. Основываясь на вашем запросе, он может оптимизировать сравнение текста, только если вы перепишете свой запрос как таковой:

 SELECT * FROM skill
WHERE data @> '{"technology": "PHP"}' AND (data->>'knowledge')::NUMERIC > 50;
 

Что дает следующий план запроса:

 Bitmap Heap Scan on skill (cost=16.01..20.03 rows=1 width=32)
Recheck Cond: (data @> '{"technology": "PHP"}'::jsonb)
  Filter: (((data ->> 'knowledge'::text))::numeric > '50'::numeric)
  -> Bitmap Index Scan on idx_user_id_knowledge (cost=0.00..16.01 rows=1 width=0)
     Index Cond: (data @> '{"technology": "PHP"}'::jsonb)
 

SQLFiddle для игры в

После некоторых дальнейших манипуляций вы могли бы вместо этого использовать обычный индекс, который напрямую нацелен на эти поля json:

 CREATE INDEX idx_user_id_knowledge ON skill  ((data->>'technology'), ((data->>'knowledge')::NUMERIC));
 

Который может быть использован для обоих условий в вашем запросе.

Обновленная скрипка