Re: Unable to make use of "deep" JSONB index

Op 12-06-2022 om 11:34 schreef Shaheed Haque:
> OK, I have corrected and simplified the test case (including switching
> to a btree index). The WHERE clause and the inex now look like this:
>
> ...WHERE ((snapshot -> 'employee' -> '999' ->>
> 'pay_graph')::integer != 0);
> ...USING btree (((snapshot -> 'employee' -> '$.*' ->>
> 'pay_graph')::integer != 0));
>
> But the index is still not being used (test case below). I have
> confirmed that the equality operator is listed for "search" (I assume
> inequality is the same as equality, but I tried both):
>
> # \dAo btree jsonb*
> List of operators of operator families
> AM | Operator family | Operator | Strategy | Purpose
> -------+-----------------+-----------------+----------+---------
> btree | jsonb_ops | <(jsonb,jsonb) | 1 | search
> btree | jsonb_ops | <=(jsonb,jsonb) | 2 | search
> btree | jsonb_ops | =(jsonb,jsonb) | 3 | search
> btree | jsonb_ops | >=(jsonb,jsonb) | 4 | search
> btree | jsonb_ops | >(jsonb,jsonb) | 5 | search
> (5 rows)
>
> If this is not a bug, then how should the query or the index be
> changed to make this work?
>
>
> === begin test case ===
>
> CREATE TABLE payrun (
> id serial primary key,
> snapshot JSONB
> );
>
> INSERT INTO payrun(snapshot)
> VALUES
> ('{"employee": {"999": {"id": 999, "state": {"employment":
> [["1920-01-01", null, 3]]}, "pay_graph": 0, "last_run_of_employment":
> false}, "111": {"id": 111, "state": {"employment": [["1920-01-01",
> null, 5]]}, "pay_graph": 3, "last_run_of_employment": true}}}'),
> ('{"employee": {"999": {"id": 999, "state": {"employment":
> [["1970-01-01", null, 3]]}, "pay_graph": 6, "last_run_of_employment":
> true}, "222": {"id": 222, "state": {"employment": [["1920-01-01",
> null, 5]]}, "pay_graph": 5, "last_run_of_employment": true}}}'),
> ('{"employee": {"998": {"id": 998, "state": {"employment":
> [["1980-01-01", null, 3]]}, "pay_graph": 7, "last_run_of_employment":
> false}, "333": {"id": 333, "state": {"employment": [["1920-01-01",
> null, 5]]}, "pay_graph": 3, "last_run_of_employment": true}}}')
> ;
>
> SELECT id,snapshot #>'{employee,999,state,employment}' FROM "payrun"
> WHERE ((snapshot -> 'employee' -> '999' ->> 'pay_graph')::integer !=
> 0);
>
> --
> -- Create index designed to match the query.
> --
> CREATE INDEX idx1 ON payrun USING btree (((snapshot -> 'employee' ->
> '$.*' ->> 'pay_graph')::integer != 0));
>
> set enable_seqscan = OFF;
>

How is this?

I took the triple condition from your earlier email.
I did not use your index.
I added one index using gin jsonb_path_ops.

create index payrun_jspathop_idx ON payrun using gin (snapshot
jsonb_path_ops);
set enable_seqscan = OFF;
\timing on
select id, snapshot #>'{employee,999,state,employment}' from payrun
where snapshot @? '$."employee"."999" ?
( @.pay_graph <> 0
|| @.last_run_of_employment == true
|| @.state.employment[last][2] == 0
)';
id | ?column?
----+---------------------------
2 | [["1970-01-01", null, 3]]
(1 row)

Time: 0.897 ms
explain analyze select id, snapshot #>'{employee,999,state,employment}'
from payrun
where snapshot @? '$."employee"."999" ?
( @.pay_graph <> 0
|| @.last_run_of_employment == true
|| @.state.employment[last][2] == 0
)';

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on payrun (cost=136.00..140.02 rows=1 width=36)
(actual time=0.018..0.019 rows=1 loops=1)
Recheck Cond: (snapshot @? '$."employee"."999"?((@."pay_graph" != 0
|| @."last_run_of_employment" == true) ||
@."state"."employment"[last][2] == 0)'::jsonpath)
Rows Removed by Index Recheck: 2
Heap Blocks: exact=1
-> Bitmap Index Scan on payrun_jspathop_idx (cost=0.00..136.00
rows=1 width=0) (actual time=0.007..0.007 rows=3 loops=1)
Index Cond: (snapshot @? '$."employee"."999"?((@."pay_graph"
!= 0 || @."last_run_of_employment" == true) ||
@."state"."employment"[last][2] == 0)'::jsonpath)
Planning Time: 0.034 ms
Execution Time: 0.033 ms
(8 rows)

Time: 0.284 ms

hope that helps.

Erik Rijkers