We have a jsonb column 'roller' as
{
"roller": {
"dob": "8/16/1956",
"ext": {
"helio_status": ""
},
"grade": "11",
"gender": "M",
"sis_id": "3456704387",
"location": {
"zip": "99999"
},
"state_id": "546556560",
"roller_number": "ASWE51460438"
}
}
We pass a variable that we want to find in {roller, sis_id}
to check if it exists
u.roller#>>'{roller, sis_id}' = 'SE223dsd'
We created a gin index on roller but it does not work, it always does a seq scan on all of them.
Is there a specific type of index that will be better? Any assistance appreciated.
CodePudding user response:
If you always look for that specific path, a regular B-Tree index is probably the best choice:
create index on the_table ( (roller#>>'{roller, sis_id}') );
You have to make sure that you always use the expression roller#>>'{roller, sis_id}'
in your WHERE clause. Not something that is just equivalent like e.g. roller -> 'roller' ->> 'sis_id'