sql - 使用 JSON 字段时的查询优化

Question

在我的笔记本电脑上运行 PostgreSQL 9.6.4，我有一个名为 node 的表，它有一个主键 id 字段和一个 properties::jsonb 字段。

我在 properties 字段上设置了 GIN 索引。

当我运行这个查询时:

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;

在大约 500 万行的表格上，大约需要 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 字段对它们进行排序？

Answer 1

更改 Planner Method Configuration并强制 Planer 不做 seqscan

例如

      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;