Postgres JSONB indexes: test the rewrite’s meaning

A Postgres JSONB index helps only when its indexed expression and operator fit the query. Before rewriting text extraction into containment to make a GIN index eligible, test whether the rewrite returns the same rows. JSON numbers and strings can become identical extracted text while remaining different containment values. If that distinction belongs to the application's contract, preserve the predicate and consider an expression index instead. ^[1][2][3]

Why start with results instead of the execution plan?#

Start with results because an index-compatible query can be fast and wrong. A whole-document GIN index does not make every operation involving that document eligible. PostgreSQL's JSON indexing documentation distinguishes the supported operators and shows why applying an operator to an extracted subdocument differs from applying it to the indexed column. ^[1][6]

That often leads to a reasonable suggestion: replace an extracted-text comparison with containment. Check whether the rewritten predicate still recognizes the stored number and string forms. A schema description is useful evidence, but a migration history, partner import or old writer may have introduced more than one representation. An optimization review should make its type assumption visible.

This article follows a fictional event store whose actor_id was written as a number by one producer and a string by another. It is an illustrative semantic fixture, not a benchmark or a report of customer data. The aim is to choose a predicate contract before asking the planner how cheaply it can execute that predicate.

The existing keyset cursor guide deals with a similar review discipline at a different boundary: retain values that affect result correctness before optimizing their transport. Neither performance work nor a cleaner-looking query authorizes silently changing which records the application recognizes.

What does the mixed-type fixture expose?#

It exposes whether the old query intentionally or accidentally treats a JSON number and a JSON string as the same identifier. The ->> operator extracts a field as text. Containment instead asks whether the document includes a specified JSON value and structure. Those are different questions when the stored types differ. ^[1][2]

Run the following in an isolated PostgreSQL scratch session. It creates only a temporary table. The labels identify the cases, so the result remains readable without depending on generated IDs or output order. The fixture includes absent and JSON-null fields because they are common sources of mistaken equivalence assumptions. ^[1][2]

CREATE TEMP TABLE event_fixture (
  label text PRIMARY KEY,
  payload jsonb NOT NULL
);

INSERT INTO event_fixture (label, payload) VALUES
  ('number', '{"actor_id":42,"tags":["priority"]}'),
  ('string', '{"actor_id":"42","tags":["priority"]}'),
  ('other',  '{"actor_id":7,"tags":[]}'),
  ('null',   '{"actor_id":null}'),
  ('absent', '{}');

SELECT label,
       payload ->> 'actor_id' = '42' AS text_match,
       payload @> '{"actor_id":42}' AS number_match,
       payload @> '{"actor_id":"42"}' AS string_match
FROM event_fixture
ORDER BY label;

Scenario: the text comparison recognizes both number and string, while numeric containment recognizes number and string containment recognizes string. Missing or JSON-null fields do not become a positive match for these equality predicates. Observe the actual SQL result, including null truth values, instead of replacing them with a Boolean in a screenshot. ^[1][2]

If the application's intended rule is to accept both representations, a numeric containment rewrite changes behavior. If the intended rule is to accept only numeric JSON, the old query was more permissive than the contract. That is a data-quality and application change to review explicitly, even when the proposed GIN index would also improve query eligibility.

Avoid adding a cast solely to make the comparison look typed. An integer cast introduces its own contract for malformed strings, out-of-range values and alternative numeric representations. The right fix might be a validated relational field or a writer migration. Define allowed identifier types before choosing the cast or index.

How can you compare the result sets explicitly?#

Compare labeled results from the original and candidate predicates in both directions. A count comparison alone can miss different sets of equal size. The following illustrative check reports labels admitted only by the original or only by the candidate. It is intentionally small enough to inspect directly. ^[1][2]

WITH original AS (
  SELECT label FROM event_fixture
  WHERE payload ->> 'actor_id' = '42'
), candidate AS (
  SELECT label FROM event_fixture
  WHERE payload @> '{"actor_id":42}'
)
SELECT 'original_only' AS difference, label
FROM (SELECT label FROM original
      EXCEPT SELECT label FROM candidate) AS original_only
UNION ALL
SELECT 'candidate_only', label
FROM (SELECT label FROM candidate
      EXCEPT SELECT label FROM original) AS candidate_only
ORDER BY difference, label;

Scenario: expect the string label in original_only. That is a failed equivalence test, not a planner problem. Keep the result with the proposed change. If your production contract rules out string identifiers, add a separate evidence step showing how existing records and every active writer satisfy that rule before declaring the difference irrelevant. ^[1][2]

Expand the fixture from your real contract: nested paths, empty arrays, duplicated array entries, absent keys, JSON nulls and objects with similar-looking values. Do not add cases indiscriminately. Each row should represent a condition that an active writer or supported input can produce, or a rejected input the system must handle deliberately.

For live-data comparison, use a controlled snapshot or other stable observation window. Otherwise, concurrent writes can create apparent differences between sequential queries. The small fixture demonstrates semantics; production comparison also needs an appropriate consistency model. Record both the predicate and the data boundary in the review so a later rerun can establish what changed.

Which index preserves the original contract?#

A B-tree expression index on the extracted text is a candidate when the application really wants the existing text equality rule. PostgreSQL supports indexes on expressions, so the indexed result can correspond to the expression actually used by the query. Preserve that relationship when the ORM or query builder emits SQL. ^[3]

In the fictional event store, the following expresses the old rule without recasting its meaning as JSON containment. This is illustrative schema work for a test table named events; adapt the table and migration procedure to your own environment. Creating an index has operational costs, so the statement is not an instruction to run an unreviewed production build.

CREATE INDEX events_actor_text_idx
ON events ((payload ->> 'actor_id'));

SELECT id
FROM events
WHERE payload ->> 'actor_id' = '42';

The index does not repair inconsistent writers. It makes the chosen lookup expression a candidate access path. If the product later decides that actor identity must be numeric, treat that as a contract migration with validation, a writer plan and an explicitly changed query. Keeping the performance change separate makes its correctness easier to review.

Expression indexes also add write work. Their derived values must be maintained when relevant writes occur. Measure the cost against your workload, especially if the indexed document changes frequently. A single favored read query is not enough evidence that every table should acquire another index. ^[3]

Why can the right operator still miss the index?#

The operator can be applied to the wrong expression. A GIN index on a complete document and a GIN index on its tags field index different values. PostgreSQL documents this distinction directly: a key-existence operator applied to an extracted field does not automatically become an index condition on the whole document. ^[1]

For example, payload -> 'tags' ? 'priority' asks about the extracted tags value. A targeted expression index on (payload -> 'tags') is a candidate for that operator. A whole-document containment predicate describing the tags array may be another option, but run the same semantic review before replacing one with the other. ^[1][6]

Operator-class choice is another independent question. The default JSONB GIN class and jsonb_path_ops do not support an identical operator set. Choose the class from the actual predicates, including existence queries emitted by less-visible code paths. A broad statement that the application “uses JSONB” does not supply enough information for that decision. ^[1][6]

These distinctions suggest a compact review artifact: record the SQL emitted by the application, the expression the index stores, and the operator connecting that expression to its comparison value. If any of those change, rerun the semantic and plan checks. Capture the SQL emitted by the ORM, including its casts and parameter types.

What proves the selected access path is useful?#

A representative execution plan and workload comparison establish usefulness; the presence of an index alone does not. EXPLAIN shows the plan chosen by PostgreSQL. Start by reading which predicate appears as an index condition and which remains a filter. Then consider the amount of work the plan performs for realistic parameters. ^[4]

Run ANALYZE after a representative scratch-data load so the planner has statistics. PostgreSQL uses those statistics in its plan decisions. Keep the semantic fixture small and the performance dataset representative: they serve different purposes, and a tiny correctness table is not a convincing index benchmark. ^[5]

When using EXPLAIN (ANALYZE, BUFFERS) on a read query, remember that the analyzed query executes. Choose a safe environment and a query whose work you understand. Record the PostgreSQL version, data distribution, parameters and cache conditions alongside the output. No PostgreSQL engine benchmark was run for this article, and no timing or buffer result is claimed. ^[4]

A sequential scan is not automatically a failed repair. A small table or a predicate matching much of the table can make a scan reasonable. Conversely, an index appearing in a plan does not prove that the complete request is inexpensive; row retrieval, sorting and remaining filters still perform work. Judge the whole plan and the application's acceptance conditions. ^[4]

The release record should therefore contain a result-set comparison, the chosen predicate contract, the index definition, a representative plan and the observed write cost. If the result changes unexpectedly, stop before celebrating a faster plan. If the result is correct but the access path is unhelpful, investigate selectivity and expression fit instead of rewriting semantics again.

What must a parameterized query preserve?#

A parameterized query must preserve the same value type and indexed expression that the reviewed SQL uses. The application may bind a text value, construct a JSON document or cast an extracted value; those choices are part of the predicate. Capture the SQL and binding types from the actual query path before comparing its plan with a hand-written console query. ^[1][2][3]

In the fixture, the text predicate compares extracted text with text. The numeric containment predicate compares the document with a JSON number inside an object. Moving a parameter from one form to the other requires defining how it becomes JSON. Concatenating a string into a JSON fragment adds an avoidable parsing and correctness problem. Use your database driver's supported parameter binding and JSON construction path, then include its emitted types in the review.

Apply the same scrutiny to nested paths. A query that indexes the entire document and a query that operates on an extracted tags value do not become identical because they share a key name. Keep the left-hand expression visible in the proposed index and in the captured statement. ^[1]

Finally, distinguish result correctness from representation cleanup. If the team decides mixed actor identifiers are invalid, document what should happen to old strings before changing production reads. Rewriting the query first can make those records disappear from application behavior before anyone repairs them. A migration report should count or enumerate affected records through an approved data-quality process, without presenting that count as an index-performance result.

What should a JSONB optimization review ask?#

Does a whole-document GIN index cover extracted-text equality?

Do not assume it does. GIN eligibility depends on the supported operator and indexed expression. For a hot extracted-text comparison, an expression index is a candidate that preserves the predicate. Verify the resulting plan rather than treating JSONB storage as an access-method contract. ^[1][3][6]

Are JSON number and string identifiers interchangeable?

Only if your application defines them that way. Text extraction can erase the distinction that typed containment preserves. Include both representations in a fixture before changing predicates, and review active writers and stored records before relying on a single-type assumption. ^[1][2]

Does a matching row count prove equivalent queries?

No. Different result sets can have equal counts. Compare stable row identifiers in both directions and inspect the differences. Use a stable data boundary for the comparison so concurrent writes do not become misleading evidence about the predicates.

Should you force an index to avoid a sequential scan?

Treat that as a diagnostic experiment rather than proof of an improvement. The planner may reasonably choose a scan for the actual table and predicate. Establish eligibility, statistics and representative workload evidence before changing application or planner settings. ^[4][5]