Query using whereJsonSupersetOf can't take advantage of partial GIN index
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 7.3k
- Forks
- 637
- PR merge metrics
- No merged PRs in 30d
Description
I've been following the docs on how to query for a value inside a JSON column and create a partial GIN index on a JSONB column and have hit upon an issue.
Consider the following database schema:
exports.up = knex => {
return knex.schema
.createTable('message', table => {
table.increments('id').primary();
table.jsonb('content');
})
.raw('CREATE INDEX on ?? USING GIN (?? jsonb_path_ops)', [
'message',
'content'
]);
};
In this case, content is a JSON object which contains a type field, describing the type of message.
The partial GIN index means that we should be able to perform the following query and have it use the index:
const pictureMessages = await MessageModel.query.whereJsonSupersetOf(
'content',
{ type: 'PICTURE' }
);
According to the linked article from the docs, in order to use the index, the resulting SQL should be:
select "message.*" from "message" where content @> '{"type":"PICTURE"}'::jsonb
However, Objection produces the following query:
select "message.*" from "message" where ( "content"#>'{}' )::jsonb @> '{"type":"PICTURE"}'::jsonb
Sadly, this form of the query doesn't use the index, as can be seen below.
cb=# explain analyse select "message".* from "message" where content @> '{"type":"PICTURE"}'::jsonb;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on message (cost=8.01..12.02 rows=1 width=503) (actual time=0.124..8.751 rows=853 loops=1)
Recheck Cond: (content @> '{"type": "PICTURE"}'::jsonb)
Heap Blocks: exact=55
-> Bitmap Index Scan on message_content_index (cost=0.00..8.01 rows=1 width=0) (actual time=0.084..0.093 rows=853 loops=1)
Index Cond: (content @> '{"type": "PICTURE"}'::jsonb)
Planning Time: 0.078 ms
Execution Time: 16.907 ms
(7 rows)
cb=# explain analyse select "message".* from "message" where ( "content"#>'{}' )::jsonb @> '{"type":"PICTURE"}'::jsonb;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on message (cost=0.00..67.93 rows=1 width=503) (actual time=0.010..11.879 rows=853 loops=1)
Filter: ((content #> '{}'::text[]) @> '{"type": "PICTURE"}'::jsonb)
Rows Removed by Filter: 9
Planning Time: 0.048 ms
Execution Time: 20.715 ms
(5 rows)
Here's the line of code which wraps the field reference. I guess this is here for an important reason, so if it needs to stay, maybe there could be a way to opt out of this behaviour, and pass through the field reference as-is?
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start at lib/queryBuilder/operations/jsonApi/postgresJsonApi.js#L93, where the field reference is wrapped, and trace the whereJsonSupersetOf query generation. Compare its SQL with the index-using content @> form shown in the issue. Done means the query can produce the unwrapped field reference when appropriate and PostgreSQL can use the GIN index.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100