Vincit / Vincit/objection.js

Query using whereJsonSupersetOf can't take advantage of partial GIN index

Open
#2,008 2 comments 0 reactions 0 assignees View on GitHub

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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.