## TL;DR
Match the index to the operator: the default `jsonb_ops` GIN index serves `@>`, `?`, `?&`, and `?|`; `jsonb_path_ops` is smaller and faster but only serves `@>`. If EXPLAIN still seq-scans, the query is probably not sargable or the table is too small for the planner to care.

```text
postgres jsonb gin index slow queries
```

## Use this when
- A GIN index exists but queries still sequential-scan
- JSONB containment or key-existence queries are slow
- You are choosing a JSONB index strategy

## Not for this skill when
- Autovacuum is falling behind
- You need upsert syntax
- Materialized view refreshes block readers

## Steps

1. Confirm the planner's actual choice with EXPLAIN:

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE payload @> '{"type": "click"}';
```
Expected output: either `Bitmap Heap Scan` with a `Bitmap Index Scan` on your GIN index (good) or `Seq Scan` (the problem). Do not tune on assumptions; read the plan.

2. Create the right GIN index for your operators:

```sql
-- general purpose: supports @>, ?, ?&, ?|
CREATE INDEX ON events USING gin (payload);
-- smaller and faster, but ONLY for @> containment:
CREATE INDEX ON events USING gin (payload jsonb_path_ops);
```
Expected output: the index builds. Pick `jsonb_path_ops` when every query is `@>`; it indexes only hashed paths and is noticeably smaller. Pick default `jsonb_ops` when you also query `?` (key exists) or `?&`/`?|`.

3. If the plan still seq-scans, check selectivity. The planner is often right:

```sql
SELECT count(*) FILTER (WHERE payload @> '{"type": "click"}') AS matches,
       count(*) AS total FROM events;
```
Expected output: if matches are a large fraction of total, a seq scan genuinely is faster and the index is correctly ignored. Indexes help selective queries.

4. For queries on one hot key, an expression index beats a general GIN:

```sql
CREATE INDEX ON events ((payload ->> 'user_id'));
SELECT * FROM events WHERE payload ->> 'user_id' = '42';
```
Expected output: a small btree on exactly the extracted value. When 90% of queries filter on the same two keys, targeted expression indexes outperform a GIN on the whole document.

5. Keep the index healthy: GIN indexes bloat under write churn:

```sql
ALTER INDEX events_payload_idx SET (fastupdate = off);
-- or schedule REINDEX for write-heavy tables
```
Expected output: with `fastupdate=off`, entries go straight to the main index instead of the pending list, trading slower writes for consistently fast reads. For read-mostly analytics tables the default is fine.

## Variant phrasings

### postgres gin index not used jsonb
Usually wrong operator for the opclass (step 2), non-selective predicate (step 3), or stale statistics. Run ANALYZE after creating the index.

### jsonb_path_ops vs jsonb_ops
`jsonb_path_ops` is smaller/faster but only accelerates `@>`. If any query uses `?`, `?&`, or `?|`, you need `jsonb_ops`.

### postgres jsonb query slow no index
Add the GIN index (step 2), but first check with EXPLAIN whether the query shape is even indexable. Functions wrapping the column (e.g. `lower(payload->>'x')`) defeat plain indexes; use expression indexes.

## Why it happens
GIN indexes accelerate specific JSONB operators, not JSONB in general. A query using `->>` extraction gets nothing from a GIN on the whole document, and a `jsonb_path_ops` index is invisible to `?` queries. The planner then correctly falls back to seq scan, which looks like "the index does not work."

## Edge cases
- GIN pending-list bloat makes freshly-written data slow to query; `fastupdate=off` or periodic VACUUM clears it.
- Very large JSONB documents make GIN entries huge; consider extracting hot fields to real columns.
- `jsonb_path_ops` does not support indexing the existence operator at all, queries silently seq-scan.
- Partial GIN indexes (`WHERE payload ? 'type'`) shrink the index when only a subset of rows carry JSONB.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_cNOhKkQFG0Hk2AqqMGnzcw