How to index objects inside array in JSONB field?

Question

I want to index the JSONB field with GIN indexing. Inside this field, I have an array of objects. Precisely, this is how it looks (shortened second object with three dots):

[
    {
        "tags": ["student work", "fast apply"],
        "intensity": [
            {
                "shift": "fulltime",
                "period": "hours",
                "duration": "9"
            },
            {
                "shift": "parttime",
                "period": "hours",
                "duration": "4"
            }
        ]
    },
    { ... }
]

This is how I filter this table in WHERE clause:

items.intensity @? '$[*] ? (@.tags == "student work" || @.tags == "undefined" || @.tags.size() == 0) ? (@.intensity[*].shift == "fulltime")'

This is the index I tried but didn't work:

CREATE INDEX idxginintensitytags ON items USING GIN (intensity jsonb_path_ops);

Explain analyze:

    #   Node    Rows    Loops
Actual
1.  Bitmap Heap Scan on items as items (rows=154922 loops=1)
Recheck Cond: (intensity @? '$[*]?(@."intensity"[*]."shift" == "fulltime")'::jsonpath)
Heap Blocks: exact=33478
154922  1
2.  Bitmap Index Scan using idxginintensitytags (rows=154922 loops=1)
Index Cond: (intensity @? '$[*]?(@."intensity"[*]."shift" == "fulltime")'::jsonpath)
154922  1

I want to filter my table by tags, shifts, periods, and durations. I have 200,000 rows in that table.

How can I index this field?

I am using the latest version - PostgreSQL 13.

Your example data doesn't have any keys named "target" so what is your example query supposed to do with all of those targets? — jjanes, Aug 20 '21 at 22:47
If your query really returns 154922, that is not going to be blazingly fast. — jjanes, Aug 21 '21 at 02:52

score 1 · Accepted Answer · answered Aug 20 '21 at 23:49

1

In my hands, a problem is that it does use the index, even though doing so is slower. And the reason for that is that @.tags.size() == 0 cannot be determined by the index, so it ends ups returning all table rows to be rechecked, but the planner evidently doesn't realize this will happen.

Can you express this concept in a different way?

answered Aug 20 '21 at 23:49

jjanes

37,812
5
27
34

I can ignore @.tags.size() == 0 if it is a problem for performance – Ulvi Aug 20 '21 at 23:52
Removing that definitely speeds things up for me. If it doesn't work for you, please edit your question to include an `EXPLAIN (ANALYZE)` of the query. – jjanes Aug 20 '21 at 23:59
Added it to my answer. Performance is the same as before, there is no improvement. I shortened the filter but it didn't help either – Ulvi Aug 21 '21 at 00:26

How to index objects inside array in JSONB field?

1 Answers1