Skip to content

Conversation

@dtcristo
Copy link
Contributor

This method wasn't making use of the que_jobs.args index and was performing badly on large datasets.

GIN indexes don't support the = operator, but support existence ? and containment @> among others. To make use of the index the query has to check for containment of a subset document rather than match a value within the document directly.

QueJob#by_job_args is already querying in this way; this change makes QueJob#by_job_class consistent with it.

Context:

https://www.postgresql.org/docs/9.6/datatype-json.html#JSON-INDEXING

Given a jsonb column "data" indexed as follows:

CREATE INDEX data_index ON table USING GIN (data);

And with data in this format:

{
  "name": "Melbourne",
  "type": "City"
}

This query will make use of the index:

SELECT data FROM table WHERE data @> '{"name": "Melbourne"}';

But this one will not:

SELECT data FROM table WHERE data->>'name' = "Melbourne";

This method wasn't making use of the que_jobs.args index and was
performing badly on large datasets.

GIN indexes don't support the '=' operator, but support existence '?'
and containment '@>' among others. To make use of the index the query
has to check for containment of a subset document rather than match a
value within the document directly.

QueJob#by_job_args is already querying in this way; this change makes
QueJob#by_job_class consistent with it.

Context:

https://www.postgresql.org/docs/9.6/datatype-json.html#JSON-INDEXING

Given a jsonb column "data" indexed as follows:
  CREATE INDEX data_index ON table USING GIN (data);

And with data in this format:
  {
    "name": "Melbourne",
    "type": "City"
  }

This query will make use of the index:
  SELECT data FROM table WHERE data @> '{"name": "Melbourne"}';

But this one will not:
  SELECT data FROM table WHERE data->>'name' = "Melbourne";
@oeoeaio
Copy link
Contributor

oeoeaio commented Jul 5, 2023

Yep, this seems like a sensible change.

@oeoeaio oeoeaio merged commit 9b26406 into que-rb:master Jul 5, 2023
@dtcristo dtcristo deleted the dtcristo/improve-by-job-class-perf branch July 5, 2023 06:20
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

3 participants