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

I just checked, and jsonb_exists has no support function.

Laurenz Albe
– Laurenz Albe

2021-06-08 00:38:08 +00:00
Commented Jun 8, 2021 at 0:38 — Laurenz Albe
– Laurenz Albe, Commented Jun 8, 2021 at 0:38

Laurenz Albe · Accepted Answer · 2021-06-08 14:59:34Z

1

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

the WHERE condition contains an expression of the form <indexed expression> <operator> <constant>, where
- an index has been created on <indexed expression>
- <operator> is an operator in the index family of the operator class of the index
- <constant> 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)

edited Jun 8, 2021 at 14:59

answered Jun 8, 2021 at 0:32

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Tomasz Zadora Over a year ago

Well jsonb_exists returns boolean, it should work that way. Here is a list of all PostgreSQL json related functions: github.com/tztztztz/List-of-all-PostgreSQL-10-operators/blob/…

Laurenz Albe Over a year ago

Yes, but see my final query, which shows that it has no support function. So it won't work. We would probably welcome a patch to add a support function.

Tomasz Zadora Over a year ago

It would be ideal for ORM I'm using if that kind of query would use the index:

SELECT id, delivery_method_id FROM k3_order WHERE jsonb_exists(CAST(jsonb_object_field_text(json_delivery, 'packageNumbers')  as jsonb), '0000000596034Q')

Laurenz Albe Over a year ago

As I said, you are welcome to write a patch.

Tomasz Zadora Over a year ago

Well, I've just installed PostgreSQL 13 on another test machine, and sql SELECT prosupport FROM pg_proc WHERE proname = 'jsonb_exists'; shows it is supported, but the other query still doesn't use the index.

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related