Two Ways to Find a Row

Searching for "chicken soup" among thousands of documents can take two very different paths.

1 A pile of documents, one search

📄 📄 📄 📄 📄 ...thousands more 🔎 "chicken soup"

Only a handful of documents actually mention chicken soup. The question is how Postgres finds them.

2 Plan A: check every single row

reads rows one at a time, computing to_tsvector() for each ⚙️ ❌ ⚙️ ❌ ⚙️ ✅ ⚙️ ❌ ⚙️ ❌ ⚙️ ❌ ⚙️ ✅ ⚙️ ❌ ...and every other row in the table, the same way every row gets visited and computed, even the ones thrown away

This is a sequential scan: Postgres computes a tsvector for every row, checks it against the query, and discards most of the work.

3 Plan B: look it up in an index

GIN index "chicken" → rows 3, 7 "soup" → rows 3, 7 "rice" → rows 2, 5 "beef" → rows 1, 6 ✅ ✅ the faded rows are never touched at all

A GIN index already maps every word to the rows that contain it. Postgres jumps straight to rows 3 and 7, no computing, no checking, no wasted work.

4 Why it matters as the table grows

work done table size sequential scan grows with every row GIN index grows with the matches, not the table
~283ms
sequential scan
~2.4ms
GIN index scan
~118×
faster
Add more rows to the table, and a sequential scan gets slower. Do the same to a GIN-indexed column, and the query barely notices, because it only ever visits the rows that actually match.