# Should I use a GIN index or a generated column to filter on fields inside a JSONB column?

> Create one expression index per fixed key and confirm the plan drops to an Index Scan; promote to a generated column if you join on the value.

- Asked: 2026-10-02
- Answered: 2026-10-06
- Asked by: Ekin
- Tags: postgresql, veritabani
- Source: https://muhammetsafak.com/just-ask/gin-index-generated-column-filter-inside-jsonb-column/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** In PostgreSQL I keep a JSONB column called `metadata` on a table, and most of my queries filter on a couple of fixed keys inside that blob, e.g. `WHERE metadata->>'status' = 'active'` and `WHERE metadata->>'region' = 'eu'`.

As the table grows, these queries keep getting slower. To speed up filtering on these fields, should I put a GIN index over the whole JSONB, or extract those keys into a generated column and index that? Which is the right call?


Short answer: if you filter on a couple of known keys, the answer isn't GIN.

## Short answer

For fixed keys the lightest option is an expression index; if you also want the value as a real column, a generated column. Reserve GIN for dynamic keys and containment queries.

## Why

1. **Two different query shapes, two different tools.** GIN shines on operators like `@>` containment and `?` existence, and on keys you don't know ahead of time or that are numerous. Generated columns and expression indexes are the right tool for equality and range on a fixed, known set of keys. Your case is the second one.

2. **Your case is "a couple of fixed keys" → GIN is overkill.** A GIN index is large, expensive to update on writes, and useless for range/ordering (`BETWEEN`, `ORDER BY`). Indexing the whole blob for just two keys builds a structure bigger and slower than you need.

## What to do

1. **The lightest option: an expression index.** You index the value straight off the expression — no schema change, no extra column. As long as your query uses the exact same expression as the index, the planner will use it.

   ```sql
   CREATE INDEX idx_meta_status ON items ((metadata->>'status'));
   CREATE INDEX idx_meta_region ON items ((metadata->>'region'));
   -- The query must match the expression exactly:
   SELECT * FROM items WHERE metadata->>'status' = 'active';
   ```

2. **When to use a generated column: you want the value as a real column too.** A STORED generated column + b-tree index gives healthier planner statistics (n_distinct, histograms), reads more naturally, and lets you reference the column directly in joins. The cost: a schema change per key and duplicated storage.

   ```sql
   ALTER TABLE items
     ADD COLUMN status text GENERATED ALWAYS AS (metadata->>'status') STORED;
   CREATE INDEX ON items (status);
   ```

3. **Mind the cast trap.** `->>'` always returns `text`. For range queries on a numeric field (`(metadata->>'age')::int > 18`), the cast must be written identically in both the index definition and the query — otherwise the index won't be used. Watch out for NULLs and empty values too.

4. **Keep GIN in reserve, don't dismiss it.** If keys are dynamic, [users type in free-form metadata](/just-ask/nosql-vs-relational-for-flexible-product-attributes/), or you do containment like `metadata @> '{"tags":["x"]}'`, GIN is the right tool — and if you only do containment, the `jsonb_path_ops` variant is smaller and faster.

**Bottom line:** personally I'd create two expression indexes for the two fixed keys and confirm with `EXPLAIN (ANALYZE, BUFFERS)` that the plan drops from a Seq Scan to an Index Scan. If I also use those values heavily in reports or joins, I'd promote them to a generated column. I'd only bring in GIN if the keys became unpredictable or I moved to containment queries. On fixed keys, GIN is cracking a nut with a sledgehammer.

## Related Reading

- [When should I switch to a covering index with INCLUDE to get an index-only scan?](https://muhammetsafak.com/just-ask/switch-covering-index-include-get-index-only-scan/) — Just Ask
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://muhammetsafak.com/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask
- [How do I rewind to seconds before a disaster with WAL archiving and PITR?](https://muhammetsafak.com/just-ask/point-in-time-recovery-with-wal-archiving/) — Just Ask