PostgreSQL 10.4 - how to index jsonb for sql functions not operators?

Question

I have a table named "k3_order" with jsonb column "json_delivery".

Example content of that column is:

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

I've created index on json_delivery->'packageNumbers':

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

Now I use this two SQL Queries:

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';

The second is faster and using index, but the first doesn't.

Is there any way to create index in PostgreSQL 10.4 in order for query 1) to use it?

Is this even possible in PostgreSQL 10.4 or newer versions?

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

produces:

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';

produces:

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

user_6464308 · Answer

Indexes can only be used by queries in the following cases:

the WHERE condition contains an expression of the form , where
- an index has been created on
- is an operator in the index family of the operator class of the index
- is an expression that stays constant for the duration of the index scan
the ORDER BY clause has the same or the exact opposite ordering as the index definition, and the index access method supports sorting (from v13 on, an index can also be used if it contains the starting columns of the ORDER BY clause)
the PostgreSQL version is v12 and higher, and the WHERE condition contains an expression of the form bool_func(...), where the function returns boolean and has a planner support function.

Now json_delivery->'packageNumbers' ? '0000000596034Q' satisfies the first condition, so an index scan can be used.

jsonb_exists(json_delivery->'packageNumbers', > '0000000596034Q') could only use an index if there were a planner support function for jsonb_exists, but there is none:

SELECT prosupport FROM pg_proc
WHERE proname = 'jsonb_exists';

 prosupport 
════════════
 -
(1 row)

PostgreSQL 10.4 - how to index jsonb for sql functions not operators?

1 Answers