From: | Erik Rijkers <er(at)xs4all(dot)nl> |
---|---|
To: | Shaheed Haque <shaheedhaque(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Unable to make use of "deep" JSONB index |
Date: | 2022-06-12 10:53:52 |
Message-ID: | d3a9000f-ba7c-63b2-fe79-40fd89bfc9d6@xs4all.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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 11:12:55 | Re: Unable to make use of "deep" JSONB index |
Previous Message | Shaheed Haque | 2022-06-12 09:34:10 | Re: Unable to make use of "deep" JSONB index |