PostgreSQL 10.4-如何为sql函数而非运算符索引jsonb

Question

我有一个名为"k3_order"；带有jsonb列"；json_edelivery"；。

该列的示例内容为：

{
"delivery_cost": "11.99",
"packageNumbers": [
"0000000596034Q"
]
}

我已经在json_edelivery->'上创建了索引packageNumbers':

CREATE INDEX test_idx ON k3_order USING gin(json_delivery->'packageNumbers');

现在我使用这两个SQL查询：

SELECT id, delivery_method_id 
FROM k3_order 
WHERE jsonb_exists (json_delivery->'packageNumbers', '0000000596034Q');
SELECT id, delivery_method_id 
FROM k3_order 
WHERE json_delivery->'packageNumbers' ? '0000000596034Q';

第二个更快，使用索引，但第一个没有。

有没有办法在PostgreSQL 10.4中创建索引，以便查询1(使用它？

这在PostgreSQL 10.4或更新版本中可能吗？

EXPLAIN ANALYZE SELECT id, delivery_method_id 
FROM k3_order 
WHERE jsonb_exists (json_delivery->'packageNumbers', > '0000000596034Q');

生产：

Seq Scan on k3_order  (cost=0.00..117058.10 rows=216847 width=8 (actual time=162.001..569.863 rows=1 loops=1)
Filter: jsonb_exists((json_delivery -> 'packageNumbers'::text), '0000000596034Q'::text)
Rows Removed by Filter: 650539
Planning time: 0.748 ms
Execution time: 569.886 ms

EXPLAIN ANALYZE SELECT id, delivery_method_id 
FROM k3_order 
WHERE json_delivery->'packageNumbers' ? '0000000596034Q';

生产：

Bitmap Heap Scan on k3_order  (cost=21.04..2479.03 rows=651 width=8) (actual time=0.022..0.022 rows=1 loops=1)
Recheck Cond: ((json_delivery -> 'packageNumbers'::text) ? '0000000596034Q'::text)
Heap Blocks: exact=1
->  Bitmap Index Scan on test_idx  (cost=0.00..20.88 rows=651 width=0) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: ((json_delivery -> 'packageNumbers'::text) ? '0000000596034Q'::text)
Planning time: 0.182 ms
Execution time: 0.050 ms

Answer 1

只有在以下情况下，查询才能使用索引：

• WHERE条件包含形式为<indexed expression><operator><constant>的表达式，其中

  • 已在<indexed expression>上创建索引

  • <operator>是索引的运算符类的索引族中的运算符

  • <constant>是在索引扫描的持续时间内保持不变的表达式

• ORDER BY子句具有与索引定义相同或完全相反的顺序，并且索引访问方法支持排序(从v13开始，如果索引包含ORDER BY子句的起始列，则也可以使用索引(

• PostgreSQL版本是v12及更高版本，WHERE条件包含形式为bool_func(...)的表达式，其中函数返回boolean，并具有计划器支持函数。

现在json_delivery->'packageNumbers' ? '0000000596034Q'满足第一个条件，因此可以使用索引扫描。

如果jsonb_exists有规划师支持功能，jsonb_exists(json_delivery->'packageNumbers', > '0000000596034Q')只能使用索引，但没有：

SELECT prosupport FROM pg_proc
WHERE proname = 'jsonb_exists';
prosupport 
════════════
-
(1 row)