When to Use GIN Over B-Tree: Execution Plan Diagnostics #

When EXPLAIN (ANALYZE, BUFFERS) reveals a Seq Scan or a Bitmap Heap Scan with high Heap Blocks: lossy counts on a column storing arrays, JSONB documents, or tsvector data, a B-tree index is the wrong tool. This page provides the specific planner conditions and EXPLAIN evidence that tell you to switch to a GIN (Generalized Inverted Index), then walks through a validated migration workflow. For the full taxonomy of index access paths, see Specialized Index Types (GIN/GiST).

The Planner Condition That Triggers the Problem #

B-tree indexes store one entry per indexed row — a direct (value → row) mapping. That structure works well for scalar equality and range predicates, which is why B-tree index optimization covers the majority of standard tuning scenarios. It breaks down when a single row contains many values and the predicate asks “does this row contain a given element?”

The containment operators that expose this gap are:

• @> (array/JSONB contains)
• && (array overlap)
• @@ (full-text match)

PostgreSQL cannot use a B-tree to satisfy any of these operators. When the planner encounters WHERE tags @> ARRAY['production'] on a B-tree-indexed column, it falls back to a sequential scan or constructs an imprecise bitmap that must recheck every candidate row. The cost model signals this via high rows estimates relative to the actual match count, and Heap Blocks: lossy in the bitmap scan node.

Annotated EXPLAIN Evidence #

The following two snippets show what to look for before and after switching to GIN. The B-tree case uses a fabricated but representative plan; run these on your own schema to obtain real timings.

B-tree plan — containment predicate on an array column:

-- B-tree cannot serve @>; planner falls back to Seq Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload FROM events WHERE tags @> ARRAY['production'];

Seq Scan on events  (cost=0.00..4821.00 rows=241 width=72)
                    (actual time=0.041..38.7 rows=312 loops=1)
  Filter: (tags @> '{production}'::text[])
  Rows Removed by Filter: 99688           -- nearly all rows scanned
  Buffers: shared hit=2211                -- full table in buffer cache
Planning Time: 0.3 ms
Execution Time: 38.9 ms

Key signals: Seq Scan with Rows Removed by Filter near the table size, and Buffers: shared hit equal to the table’s page count. The B-tree index on tags is completely ignored because no B-tree operator class supports @> on arrays.

GIN plan — same predicate after index creation:

-- GIN index for array containment
CREATE INDEX idx_gin_tags ON events USING gin (tags);
ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload FROM events WHERE tags @> ARRAY['production'];

Bitmap Heap Scan on events  (cost=18.4..284.6 rows=312 width=72)
                             (actual time=1.2..3.1 rows=312 loops=1)
  Recheck Cond: (tags @> '{production}'::text[])
  Heap Blocks: exact=214                  -- precise TID list; no lossy pages
  Buffers: shared hit=217                 -- 217 vs 2211 — 10× fewer pages read
  ->  Bitmap Index Scan on idx_gin_tags  (cost=0.00..18.3 rows=312 width=0)
        (actual time=1.1..1.1 rows=312 loops=1)
        Index Cond: (tags @> '{production}'::text[])
        Buffers: shared hit=3
Planning Time: 0.4 ms
Execution Time: 3.3 ms

Confirm the switch worked by verifying: Bitmap Index Scan names the GIN index, Heap Blocks: exact replaces lossy, and Buffers: shared hit is a fraction of the baseline. Understanding the exact cost units these fields use is covered in how PostgreSQL calculates node costs.

Step-by-Step Migration Workflow #

1. Capture a baseline. Run EXPLAIN (ANALYZE, BUFFERS) on the target query and record Execution Time, Buffers: shared hit, and whether Heap Blocks: lossy appears. This is your comparison anchor.

2. Choose the right operator class. The operator class determines which predicates the index can satisfy:

   -- Text arrays: default GIN operator class
   CREATE INDEX idx_gin_tags ON events USING gin (tags);
   
   -- JSONB containment only (3–5× smaller than jsonb_ops)
   CREATE INDEX idx_gin_metadata ON logs USING gin (metadata jsonb_path_ops);
   
   -- JSONB with key-existence operators (@>, ?, ?|, ?&)
   CREATE INDEX idx_gin_meta_ops ON logs USING gin (metadata jsonb_ops);
   
   -- Full-text search on a tsvector column
   CREATE INDEX idx_gin_fts ON articles USING gin (search_vector);

   Use jsonb_path_ops when your queries only use @>. Switch to jsonb_ops only if you also need ? (key exists) or ?|/?& (any/all keys exist) operators.

3. Update statistics before re-testing. PostgreSQL’s planner uses stale statistics until you run ANALYZE:

   ANALYZE events;

4. Re-run the query and compare. Re-execute the exact EXPLAIN (ANALYZE, BUFFERS) statement from step 1. Expect Bitmap Index Scan on <gin_index> to appear and Heap Blocks: exact to replace lossy.

5. Verify index utilization in pg_stat_user_indexes:

   SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
   FROM   pg_stat_user_indexes
   WHERE  relname = 'events'
   ORDER  BY idx_scan DESC;
   -- idx_scan should climb on the GIN index after production traffic

6. Tune write behavior if amplification is unacceptable. GIN buffers inserts in a pending list controlled by gin_pending_list_limit (default 4 MB). Raise maintenance_work_mem to accelerate merges, or disable fastupdate on tables where consistent read latency matters more than insert throughput:

   -- Disable pending list buffering for predictable read latency
   CREATE INDEX idx_gin_tags ON events USING gin (tags) WITH (fastupdate = off);

Before/After Plan Comparison #

The most important metrics to diff:

The Heap Blocks: lossy line is the single most reliable signal. A lossy count above zero means the bitmap overflowed work_mem and PostgreSQL cannot track exact TIDs — it must recheck every page in the lossy blocks. A GIN index with adequate work_mem produces exact counts only.

Common Pitfalls #

Mismatched operator class causes planner to fall back to Seq Scan. If you create a jsonb_path_ops index but your query uses ? (key-exists), the index is invisible to the planner. Check that the operator class supports every operator in your WHERE clause before concluding the index is broken. The filter pushdown mechanics page explains how the planner matches predicates to access paths.

Using jsonb_ops when jsonb_path_ops would suffice. jsonb_ops indexes every key and value individually; jsonb_path_ops indexes only containment paths. For pure @> workloads, jsonb_ops inflates index size by 3–5× with no query benefit.

Ignoring gin_pending_list_limit during bulk loads. Bulk inserts accumulate in the pending list until it hits gin_pending_list_limit (4 MB by default), triggering a synchronous merge. During that merge, readers must scan both the main tree and the pending list, causing latency spikes. Raise the limit or flush the list explicitly with VACUUM:

VACUUM (INDEX_CLEANUP on) events;

Applying GIN to scalar equality queries. GIN is strictly for multi-value or inverted lookups. For WHERE status = 'active' on a single-value column, a B-tree index or a partial index is the correct choice and will outperform GIN in every measurable dimension.

Frequently Asked Questions #

How do I confirm a GIN index is being used in the execution plan? #

Run EXPLAIN (ANALYZE, BUFFERS) and verify Bitmap Index Scan targets the GIN index name. Check that Buffers: shared hit increases while Heap Blocks: lossy drops to zero compared to the B-tree or sequential scan baseline.

Should I use GIN for exact equality matches on single-value columns? #

No. B-tree indexes are strictly superior for scalar equality and range queries. GIN introduces unnecessary overhead and larger storage footprints for single-value lookups. Reserve GIN for arrays, JSONB, full-text search, and network address types like inet.

How does fastupdate impact query latency during bulk writes? #

With fastupdate = on, inserts are buffered in a pending list, reducing average write latency but requiring periodic cleanup. During cleanup, the planner must scan both the main GIN tree and the pending list on reads, causing transient latency spikes. Monitor pg_stat_user_indexes for spikes and schedule periodic VACUUM to flush the pending list off-peak.

Related

• Specialized Index Types (GIN/GiST) — parent: full GIN and GiST architecture, operator class reference, and index sizing
• B-Tree Index Optimization — when B-tree remains the correct choice; composite key ordering and include columns
• Partial Index Implementation — reduce GIN overhead with a predicate-scoped index on high-selectivity subsets
• Index Tuning & Strategy — pillar: complete index access path taxonomy and diagnostic workflow