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

Thanks Erik. Is the point that the index has to be on the JSON field
as a whole (i.e. "snapshot") rather than deep inside it (e.g.
"snapshot.something.further[down]")?

On Sun, 12 Jun 2022 at 11:53, Erik Rijkers <er@xs4all.nl> wrote:
>
> 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