主页 / dba / 问题 / 90002
Accepted
jgm
Asked: 2015-01-24 04:05:22 +0800 CST

PostgreSQL 运算符使用索引但底层函数不使用

  • 772

我正在尝试JSONB与 JDBC 一起使用，这意味着我必须避免使用任何使用“？”的运算符。字符（因为 PostgreSQL JDBC 驱动程序对此字符没有转义）。取一张简单的表格：

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES
    ('{"name":"First","tags":["foo"]}')
  , ('{"name":"Second","tags":["foo","bar"]}')
  , ('{"name":"Third","tags":["bar","baz"]}')
  , ('{"name":"Fourth","tags":["baz"]}');
CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

使用命令行，我可以运行一个简单的选择，并按预期使用索引：

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonthings  (cost=113.50..30236.13 rows=10000 width=61) (actual time=0.024..0.025 rows=1 loops=1)
   Recheck Cond: ((d -> 'name'::text) ? 'First'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_jsonthings_name  (cost=0.00..111.00 rows=10000 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((d -> 'name'::text) ? 'First'::text)
 Planning time: 0.073 ms
 Execution time: 0.047 ms
(7 rows)

由于我无法使用该?字符，因此我使用了支持运算符的功能?。但是，它没有使用索引：

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE jsonb_exists(d->'name','First');
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=3333334 width=61) (actual time=0.016..3135.119 rows=1 loops=1)
   Filter: jsonb_exists((d -> 'name'::text), 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.051 ms
 Execution time: 3135.138 ms
(5 rows)

为什么会发生这种情况，我该怎么做才能使函数使用索引？请注意，实际上该表中还有另外 10MM 行，我也已enable_seqscan关闭，所以这不是计划者决定不使用索引的情况。

为了回应评论，我尝试使用自定义运算符：

CREATE OPERATOR ### (
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

但这有同样的问题：

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ### 'First';
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=10000 width=61) (actual time=0.012..3381.608 rows=1 loops=1)
   Filter: ((d -> 'name'::text) ### 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.046 ms
 Execution time: 3381.623 ms
(5 rows)

更新

最新的 PostgreSql 驱动程序（截至 2015 年 3 月）具有转义?字符的能力，因此这种特定情况不再是问题。

postgresql index-tuning

1 个回答

  1. Best Answer

    支持的运算符和解决方法

    json列上 GIN 索引的默认运算符类jsonb_ops仅支持这些运算符（每个文档）：

    Name        Indexed Data Type   Indexable Operators
...
jsonb_ops   jsonb               ? ?& ?| @>

    您可以通过相反的方式实现这一点：使用运算符创建一个简单的IMMUTABLESQL函数?，该函数可以内联，并且将像运算符本身一样使用索引：

    CREATE OR REPLACE FUNCTION jb_contains(jsonb, text)
  RETURNS bool AS
'SELECT $1 ? $2' LANGUAGE sql IMMUTABLE;

    这行得通，我在 Postgres 9.4 中测试过......

    误解

    但是，您一直在问错误的问题。你的问题有两个基本的误解。

    1. jsonb运算符不能?用于搜索值。仅适用于键或数组元素。手册：

      描述：
      该字符串是否作为 JSON 值中的顶级键存在？

      你得到了错误的操作符，WHERE条件不能工作：WHERE d->'name' ? 'First'

    2. 无论哪种方式，您拥有的表达式索引都没有意义

      CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

      • 表达式d->'name'返回一个jsonb值。您需要将值作为.d->>'name'text

      • 但这仍然毫无意义。由于name键的值是一个简单的字符串，因此 GIN 索引（尽管可能）从一开始就没有任何意义。

    解决方案

    您不需要操作员?- 所以也没有解决方法。
    以下是两种实际可行的方法：

    1. 普通 GIN 索引d并使用“包含”运算符@>：

      CREATE INDEX idx_jsonthings_d_gin ON jsonthings USING GIN (d);

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}'

      您甚至可以使用更专业的操作符类jsonb_path_ops。看：

    2. B-tree 表达式索引d->>'email'并使用 good old 进行测试=：

      CREATE INDEX idx_jsonthings_d_email ON jsonthings ((d->>'email'));

SELECT d FROM jsonthings WHERE d->>'email' = 'First';

    第二个索引会小很多，查询速度会更快。

    • 3

相关问题