Unable to make use of "deep" JSONB index

Hi,

I'm having trouble using an index on the "deep" innards of a JSONB field in that the matching query it is trying to accelerate never uses it. I did get some advice on a simplified version of the problem at [1], but the actual problem remains the same in that "EXPLAIN ANALYZE" never refers to the index. Here are the details including a test case below...

1. The JSONB can be several MB in size. This works fine for all but one access pattern.

2. The JSON in the problem use case looks like this:

{

  "...stuff...": ...

  "employee": {

    "999": {"id": 999, "integer attribute": 0, "boolean-may-be-missing": true, "state": {

      "nested-list": [[], [], ...]

    }

  }

}

3. As per the discussion at [2], using a SELECT with a WHERE on the 3 attributes of interest ("integer attribute", the "boolean-may-be-missing" and "nested-list") incurs a significant overheard which suggests that the JSONB storage is being accessed 3 times. In order to optimise for this case, I constructed a query using the jsonpath support which seems to successfully avoid the triple-fetch by keeping the logic inside the jsonpath query like this:

4. Then I created an index "matching" this query.

5. According to EXPLAIN ANALYSE, the index is never used.

=== version and platform ===

Version: PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

Platform: Ubuntu 22.04 (Jammy), using OS-supplied build

=== 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 <> 0 || @.last_run_of_employment == true || @.state.employment[last][2] == 0)');

--

-- Create index designed to match the query.

--

create index idx1 on payrun using gin ((snapshot->'$.employee.* ? (@.pay_graph <> 0 || @.last_run_of_employment == true || @.state.employment[last][2] == 0)'));

set enable_seqscan = OFF;

--

-- EXPLAIN ANALYZE ...query above...

--

=== end test case ===

The expected result is that with enable_seqscan = OFF, the index should be used, but instead a sequential scan is reported as above. The same happens without enable_seqscan = OFF on

a moderately large test set of over 2000 rows (with extended run times circa 10+ seconds on my hardware).

I have tried the same with similar results on PG13.

Have I constructed the index incorrectly, or is there some other way to convince the query to use it?

Thanks, Shahee

[1] https://www.postgresql.org/message-id/CAHAc2jdiRtw3qus_rvz1QvcxUJ9AgaBKObP-Fvx1q6Vr80n_xw%40mail.gmail.com

[2] https://www.postgresql.org/message-id/CAHAc2jf6Yp2uddfKQyGp%3DbyvOuUzYpsHoPjyKXnUhS5%2B%3DAcuww%40mail.gmail.com