Vincit
Query using whereJsonSupersetOf can't take advantage of partial GIN index
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?
In the meantime I'm working around this with this custom method on the query builder, but it would be great to learn if this could be done without a workaround!
whereJsonContains(fieldReference, partialJson) {
return this.whereRaw('?? @> ?', [
fieldReference,
JSON.stringify(partialJson),
]);
}
