Оптимизация запросов при использовании поля JSON

Запуск PostgreSQL 9.6.4 на моем ноутбуке, у меня есть таблица с именем node, которая имеет поле первичного ключа id и поле properties::jsonb.

У меня есть индекс индекса GIN в поле properties.

Когда я запускаю этот запрос:

SELECT   n.*
FROM     node n
WHERE    node_type_id = '2'
AND      properties @> '{"slug":"wild-castles"}'::JSONB
ORDER BY n.id ASC OFFSET 0 LIMIT 10;

on ~ 5M rows table требуется около 20 секунд, чтобы получить ответ. Заглянув в план объяснения, я выяснил, что оптимизатор запросов сначала сортирует таблицу по первичному ключу, а затем фильтрует поле properties:

Limit  (cost=0.56..1517.94 rows=10 width=154)
  ->  Index Scan using node_pkey on node n  (cost=0.56..739571.11 rows=4874 width=154)
        Filter: ((properties @> '{"slug": "wild-castles"}'::jsonb) AND ((node_type_id)::text = '2'::text))

Но когда я удаляю упорядочение, я вижу оптимизатор, используя индекс, как ожидалось:

SELECT n.*
FROM   node n
WHERE  node_type_id = '2'
AND    properties @> '{"slug":"wild-castles"}'::JSONB
OFFSET 0 LIMIT 10;

Limit  (cost=93.77..127.10 rows=10 width=154)
  ->  Bitmap Heap Scan on node n  (cost=93.77..16338.56 rows=4874 width=154)
        Recheck Cond: (properties @> '{"slug": "wild-castles"}'::jsonb)
        Filter: ((node_type_id)::text = '2'::text)
        ->  Bitmap Index Scan on node_ix02  (cost=0.00..92.55 rows=4874 width=0)
              Index Cond: (properties @> '{"slug": "wild-castles"}'::jsonb)

Кроме того, простой WHERE properties @> '{"slug":"wild-castles"}'::JSONB ведет себя так, как ожидалось:

EXPLAIN SELECT   n.*
FROM     node n
WHERE    properties @> '{"slug":"wild-castles"}'::JSONB
;

Bitmap Heap Scan on node n  (cost=93.77..16326.38 rows=4874 width=154)
  Recheck Cond: (properties @> '{"slug": "wild-castles"}'::jsonb)
  ->  Bitmap Index Scan on node_ix02  (cost=0.00..92.55 rows=4874 width=0)
        Index Cond: (properties @> '{"slug": "wild-castles"}'::jsonb)

Итак, я думаю, мне интересно, почему оптимизатор не использовал индекс для фильтрации строк сначала, а затем упорядочил их по полю id?

Ответ 1

Измените "Конфигурация метода планера" , а строгальный станок не сделает seqscan

eg

      SET enable_seqscan = OFF;

       SELECT   n.*
        FROM     node n
               WHERE    node_type_id = '2'
               AND      properties @> '{"slug":"wild-castles"}'::JSONB
             ORDER BY n.id ASC OFFSET 0 LIMIT 10;

Ответ 2

По моему опыту, вам иногда приходится обманывать планировщика запросов, чтобы заставить его работать хорошо, и он требует некоторой настройки и возиться с...

Я бы попробовал запустить это, чтобы посмотреть, как он работает:

SELECT nn.* FROM (
    SELECT n.*
    FROM   node n
    WHERE  node_type_id = '2'
    AND    properties @> '{"slug":"wild-castles"}'::JSONB
) nn
ORDER BY nn.id ASC OFFSET 0 LIMIT 10;