gin-index-on-jsonb-without-expression

What triggers it

An IndexStmt whose accessMethod lowercased is gin, with at least one indexParams[].IndexElem that has a name and no expr — a bare column reference rather than an expression. One extra gate is text, not AST: ctx.originalSql.toLowerCase().includes("jsonb_path_ops") makes the rule stand down.

What does not

A GIN index whose keys are all expressions, since those IndexElems carry expr and no name. Any statement whose text contains jsonb_path_ops anywhere, including inside a comment on the same statement — the check is a raw substring test over the whole statement. Non-GIN access methods. ALTER TABLE ... ADD CONSTRAINT, which is not an IndexStmt.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

Nothing to do with locks; MP001 handles whether the index build should be CONCURRENTLY. The point is that the index will exist, will be maintained on every write, and still will not be used by the queries the author had in mind: default jsonb_ops GIN answers @>, ?, ?| and ?&, and the ->> extraction that most ORMs generate falls back to a sequential scan.

Why it matters

A GIN index with default jsonb_ops does NOT speed up queries using ->> or ->. Most ORMs generate WHERE metadata->>'key' = 'value' queries, which will still do a sequential scan. Use an expression B-tree index on the specific path instead.

Unsafe, and safe

CREATE INDEX idx_events_data ON events USING GIN (data);
-- Useless for: WHERE data->>'status' = 'active'

-- For ->> queries, use expression B-tree:
CREATE INDEX idx_events_status ON events ((data->>'status'));
-- For @> containment, use jsonb_path_ops:
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops);

What it assumes

The rule cannot see column types. It has no catalog access and never reads ctx.existingIndexes or ctx.cluster, so it does not actually know the column is jsonb — it fires on any GIN index over any bare column. That makes a USING GIN (tags) on a text[], a tsvector full-text index, and a trigram index written as USING GIN (name gin_trgm_ops) all false positives, since the opclass is never inspected and only the literal string jsonb_path_ops suppresses it. It also cannot know which operators the application actually uses, which is exactly why the fixer classifies it unfixable.

What the CLI prints

⚠ [MP056] WARNING (line 1)
  GIN index "idx_events_data" on "events"(data) uses bare column reference. This index won't speed up ->> or -> queries, only @>, ?, ?|, ?& containment operators.

  Safe alternative:
  -- For ->> queries, use an expression B-tree index instead:
  -- CREATE INDEX CONCURRENTLY idx_events_data ON events ((data->>'key'));
  -- For @> containment queries, add jsonb_path_ops:
  -- CREATE INDEX CONCURRENTLY idx_events_data ON events USING GIN (data jsonb_path_ops);

  Why: A GIN index with default jsonb_ops on a bare JSONB column does NOT speed up queries using ->> or ->. Most ORMs (Prisma, TypeORM, Knex) generate WHERE metadata->>'key' = 'value' queries, which will still do a sequential scan. Use an expression B-tree index on the specific path instead, or use jsonb_path_ops if you only use @> containment queries.
  Docs: https://migrationpilot.dev/rules/mp056

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP056
CREATE INDEX idx_events_data ON events USING GIN (data);

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP056: false

# or keep it, and downgrade it
rules:
  MP056:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP042require-index-nameindexes should have explicit names
• MP001require-concurrent-indexCREATE INDEX must use CONCURRENTLY