From: | Shaheed Haque <shaheedhaque(at)gmail(dot)com> |
---|---|
To: | Erik Rijkers <er(at)xs4all(dot)nl> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Unable to make use of "deep" JSONB index |
Date: | 2022-06-12 11:12:55 |
Message-ID: | CAHAc2jehgzj-Y6V0n5PSVyF+tiT=BUkbxsZE2Wjec9x-YpE8ng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)xs4all(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Shaheed Haque | 2022-06-12 19:31:19 | Re: Unable to make use of "deep" JSONB index |
Previous Message | Erik Rijkers | 2022-06-12 10:53:52 | Re: Unable to make use of "deep" JSONB index |