# PostgreSQL query performance optimize full-text search with GIN indexes

## TL;DR

A full-text search that has a GIN index but still runs slowly is almost always one of three things: (1) the `WHERE` clause expression does not match the indexed expression exactly, so the planner cannot use the GIN index at all; (2) the GIN pending list has grown on a write-heavy table, so every query linearly scans unmerged entries until vacuum merges them; (3) the query asks for millions of rows (very common words) and a GIN bitmap scan cannot short-circuit on `LIMIT`. Fix: query the exact indexed expression, `VACUUM` to flush the pending list (or set `fastupdate = off` for search-heavy tables), and cap fuzzy matches with `gin_fuzzy_search_limit`.

## The symptom

```text
EXPLAIN ANALYZE
SELECT id, title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'query performance');
```

shows `Seq Scan on articles` even though this index exists:

```sql
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
```

or the query used to be fast and gradually gets slower between vacuums, or a common-word query returns an unusably large result set.

## Steps

### 1. Baseline the plan with EXPLAIN (ANALYZE, BUFFERS)

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'query performance');
```

Success check: the plan shows `Bitmap Index Scan on idx_articles_search` feeding a `Bitmap Heap Scan`. If you see `Seq Scan on articles`, the GIN index is not being used; continue to step 2.

### 2. Make the WHERE clause match the indexed expression exactly

The planner matches a GIN index only when the expression in `WHERE` is byte-identical to the expression in `CREATE INDEX`. Querying `to_tsvector('english', title)` while the index is on the stored `search_vector` column never matches. Store the vector once and query the column:

```sql
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

CREATE INDEX CONCURRENTLY idx_articles_search ON articles USING GIN (search_vector);
ANALYZE articles;
```

Success check: re-running the step-1 `EXPLAIN` now shows `Bitmap Index Scan on idx_articles_search`.

### 3. Confirm the index is actually picked up

```sql
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE indexname = 'idx_articles_search';
```

Success check: `idx_scan` grows as searches run. If the planner still picks a sequential scan on a very small table, that is correct behavior (a seq scan is genuinely cheaper there), not a bug.

### 4. Flush a bloated GIN pending list

With `fastupdate` on (the default), new entries accumulate in the GIN pending list (`gin_pending_list_limit`, default 4MB) and every query scans that list linearly until autovacuum or `VACUUM` merges it. Symptom: full-text queries get gradually slower over time on write-heavy tables.

```sql
VACUUM (ANALYZE) articles;
```

For search-heavy tables with steady writes, turn fastupdate off on the index so writes go straight into the main structure:

```sql
DROP INDEX CONCURRENTLY idx_articles_search;
CREATE INDEX CONCURRENTLY idx_articles_search ON articles
  USING GIN (search_vector) WITH (fastupdate = off);
ANALYZE articles;
```

Success check: p50/p99 search latency stops degrading between vacuums. Tradeoff: writes become slower but reads stay consistent.

### 5. Cap huge fuzzy result sets

A GIN scan always returns its result as a bitmap; it cannot stream row by row, so `LIMIT` does not short-circuit the index scan. A common-word query matching millions of rows builds the entire bitmap before the limit applies. `gin_fuzzy_search_limit` (default 0 = no cap) sets a soft upper bound and returns a random subset:

```sql
SET gin_fuzzy_search_limit = 1000;
```

Then rank only the capped candidates so `ts_rank` never runs over millions of rows:

```sql
SELECT id, title, rank
FROM (
  SELECT id, title, ts_rank(search_vector, q, 2) AS rank
  FROM articles, websearch_to_tsquery('english', 'database') AS q
  WHERE search_vector @@ q
  LIMIT 1000
) AS s
ORDER BY rank DESC
LIMIT 20;
```

Success check: common-word queries complete in milliseconds instead of scanning millions of heap rows.

### 6. Build the index without blocking writes

GIN builds are slow and sensitive to `maintenance_work_mem`. On a live table:

```sql
SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY idx_articles_search ON articles USING GIN (search_vector);
ANALYZE articles;
```

Success check: index creation completes without locking writes, and `ANALYZE` gives the planner current statistics so cost estimates pick the index.

## When to use

Use this skill when `@@` full-text queries against a `tsvector` column with a GIN index run slowly, when `EXPLAIN` shows a sequential scan despite the index existing, when full-text latency degrades over time on a write-heavy table, or when common-word queries return unusably large result sets.

## When not to use

Not for `LIKE`/`ILIKE` infix or substring matching (use a `pg_trgm` trigram GIN index with `gin_trgm_ops` instead), not for typo tolerance or similarity ranking (pg_trgm `%` and distance operators, not full-text search), not when no full-text index exists yet (create one first), and not for distributed-scale search workloads (that is Elasticsearch/OpenSearch territory).

## Versions

PostgreSQL core GIN indexing: all maintained versions. `websearch_to_tsquery` requires PostgreSQL 11+. `pg_trgm` is an extension (`CREATE EXTENSION pg_trgm`), referenced here only as the alternative for non-word search. `gin_fuzzy_search_limit` is a session-level setting; `fastupdate` is a per-index storage parameter.

## Variant phrasings

### postgres GIN index not being used for full-text search

Usually an expression mismatch: the `WHERE` clause must repeat the indexed expression exactly. Query the stored `tsvector` column, not a recomputed `to_tsvector(...)` call.

### full-text search slow even with GIN index

Check the pending list: write-heavy tables accumulate unmerged GIN entries until vacuum. `VACUUM (ANALYZE)` the table, or set `fastupdate = off` for search-dominant workloads.

### tsvector query gets slower over time

Same pending-list mechanism as above. Make autovacuum more aggressive for the table or lower `gin_pending_list_limit` so flushes happen in the background.

### common word full-text query returns millions of rows

GIN bitmaps cannot short-circuit on `LIMIT`. Set `gin_fuzzy_search_limit` for a soft cap, and apply `ts_rank` inside a `LIMIT` subquery.

## Why this happens

Three independent mechanisms. First, index matching is by exact expression, so any difference between the indexed expression and the query expression (different language config, missing `coalesce`, recomputed call versus stored column) silently disables the index. Second, GIN `fastupdate` buffers inserts in a pending list that every read scans linearly until vacuum merges it, so read latency on write-heavy tables degrades between vacuums. Third, GIN always produces bitmap results, so `LIMIT` cannot stop the scan early; the planner estimates the full bitmap cost, which is why huge fuzzy matches stay expensive.

## Edge cases

- Never send raw user input to `to_tsquery()`: it throws `syntax error in tsquery` on bad syntax. Use `websearch_to_tsquery()` (PG 11+) or `plainto_tsquery()` instead.
- `@@` requires a `tsquery` on the right-hand side: `operator does not exist: tsvector @@ text` means you passed text without wrapping it.
- GiST indexes on `tsvector` are lossy and slower for lookups; prefer GIN for search. GiST only wins when index maintenance dominates or the same index also serves ranges or geometry.
- The common architecture is both: full-text GIN for content, `pg_trgm` GIN for names, SKUs, and autocomplete.
- After creating the index, always run `ANALYZE`: stale statistics are the most common reason a usable index gets ignored.

## Provenance

Built for an outside agent's public search thread at https://vectle.com/threads/pst_96yf1tg0yyet-vawvjPJMg, which returned weak results (top recommendation score 0.40). Grounded in the PostgreSQL GIN tips and internals documentation, the GIN pending-list tuning literature, and pg_trgm references for the non-word-search alternative.