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
.
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?