GIN for Full-Text Search and JSONB
LIKE '%word%' cannot use a B-tree index no matter how it is built. GIN indexes a completely different structure — and actually can.
A regular B-tree index is sorted by whole column values, so it can jump directly to a range of rows for an equality or prefix match — but "contains this substring anywhere" has no sorted structure to exploit, which is exactly why LIKE '%word%' with a leading wildcard forces a full sequential scan regardless of what indexes exist on the column. GIN (Generalized Inverted Index) solves a structurally different problem: it indexes the individual elements inside a composite value — every distinct lexeme in a tsvector, every key and value inside a JSONB document — mapping each element back to every row containing it, the same idea as a book's index mapping each topic to the pages it appears on, rather than the pages' contents being sorted by topic.\n\nPostgreSQL's full-text search converts a document into a tsvector — normalized, stemmed lexemes — and a search phrase into a tsquery, then matches them with @@. A GIN index on the tsvector column makes that match genuinely fast. The identical mechanism, GIN over inverted structure, works for JSONB too: jsonb_path_ops indexes JSONB values specifically for the containment operator @>, letting "does this document contain this key/value" become an index lookup instead of a per-row check.
The Baseline: A Leading-Wildcard LIKE Scan
EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM idx_articles WHERE body LIKE '%indexing%';
Seq Scan on idx_articles (actual rows=4000.00 loops=1)
Filter: (body ~~ '%indexing%'::text)
Rows Removed by Filter: 16000
Execution Time: 379.777 ms
No index — B-tree or otherwise — helps a leading-wildcard LIKE. Every one of the 20,000 rows has to be read and checked individually.
GIN Full-Text Search, Measured
CREATE INDEX idx_articles_tsv ON idx_articles USING GIN(tsv);
EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM idx_articles WHERE tsv @@ to_tsquery('english', 'indexing');
Bitmap Heap Scan on idx_articles (actual rows=4000.00 loops=1)
Recheck Cond: (tsv @@ '''index'''::tsquery)
-> Bitmap Index Scan on idx_articles_tsv
Index Cond: (tsv @@ '''index'''::tsquery)
Execution Time: 159.385 ms
The identical logical search, roughly 2.4x faster at this table's 20,000-row scale — and the gap widens dramatically as table size grows, since the GIN lookup cost scales with the number of matching lexeme entries, not the number of rows scanned.
Multi-Word Queries, Two Ways to Build Them
EXPLAIN SELECT id FROM idx_articles WHERE tsv @@ to_tsquery('english', 'postgresql & indexing');
-- Bitmap Heap Scan, rows=800 — both terms required, index-backed
EXPLAIN SELECT id FROM idx_articles WHERE tsv @@ plainto_tsquery('english', 'database performance');
-- Bitmap Heap Scan, rows=800 — same GIN index, simpler input syntax
to_tsquery expects operator syntax (& for AND, | for OR) and is meant for search boxes that build structured queries; plainto_tsquery takes plain text and ANDs the resulting lexemes together automatically — a better fit for a raw user search string. Both compile down to the same kind of tsquery and use the same GIN index.
GIN for JSONB Containment
CREATE INDEX idx_articles_jsonb ON idx_articles USING GIN(metadata jsonb_path_ops);
EXPLAIN SELECT id FROM idx_articles WHERE metadata @> '{"tag": "postgres"}';
Bitmap Heap Scan on idx_articles (cost=42.41..1425.01 rows=4848 width=4)
Recheck Cond: (metadata @> '{"tag": "postgres"}'::jsonb)
-> Bitmap Index Scan on idx_articles_jsonb
Index Cond: (metadata @> '{"tag": "postgres"}'::jsonb)
The exact same GIN mechanism, applied to JSONB: jsonb_path_ops builds inverted entries specifically for containment checks, so "does this document contain this key/value pair" becomes an index lookup instead of parsing and checking every row's JSON individually.
GIN (Generalized Inverted Index)
An index type that maps individual elements found inside a composite value — lexemes inside a tsvector, keys and values inside a JSONB document, elements inside an array — back to every row containing that element. This is the opposite structure of a B-tree, which sorts by whole values; a GIN index is built for "does this row contain X" queries rather than "what rows fall in this range" queries.
tsvector / tsquery
A tsvector is a normalized, stemmed representation of a document's searchable text — "indexing" and "indexes" both reduce to the same lexeme, for instance. A tsquery represents a search expression built from the same normalization. The @@ operator matches them; a GIN index on the tsvector column makes that match an index lookup instead of a per-row text scan.
🐌 Measure the Leading-Wildcard LIKE Baseline
Run a substring search with LIKE and confirm no index helps it at all.
psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM idx_articles WHERE body LIKE '%indexing%';"student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM idx_articles WHERE body LIKE '%indexing%';" SET QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on idx_articles (cost=0.00..1162.00 rows=4000 width=4) (actual rows=4000.00 loops=1) Filter: (body ~~ '%indexing%'::text) Rows Removed by Filter: 16000 Buffers: shared hit=912 Planning: Buffers: shared hit=51 Planning Time: 18.012 ms Execution Time: 379.777 ms (8 rows)
⚡ Build a GIN Index and Measure the Real Difference
Create a GIN index on the tsvector column and run the equivalent search through full-text search instead.
psql -U postgres -d beer_db -c "CREATE INDEX idx_articles_tsv ON idx_articles USING GIN(tsv);"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM idx_articles WHERE tsv @@ to_tsquery('english', 'indexing');"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_articles_tsv ON idx_articles USING GIN(tsv);" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT id FROM idx_articles WHERE tsv @@ to_tsquery('english', 'indexing');" SET QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on idx_articles (cost=37.95..999.95 rows=4000 width=4) (actual rows=4000.00 loops=1) Recheck Cond: (tsv @@ '''index'''::tsquery) Heap Blocks: exact=580 Buffers: shared hit=584 -> Bitmap Index Scan on idx_articles_tsv (cost=0.00..36.95 rows=4000 width=0) (actual rows=4000.00 loops=1) Index Cond: (tsv @@ '''index'''::tsquery) Index Searches: 1 Buffers: shared hit=4 Planning: Buffers: shared hit=61 read=1 Planning Time: 23.100 ms Execution Time: 159.385 ms (12 rows)
🔤 Compare to_tsquery and plainto_tsquery on the Same Index
Run a two-word AND search two different ways — structured operator syntax and plain text — confirming both use the same GIN index.
psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM idx_articles WHERE tsv @@ to_tsquery('english', 'postgresql & indexing');"psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM idx_articles WHERE tsv @@ plainto_tsquery('english', 'database performance');"student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM idx_articles WHERE tsv @@ to_tsquery('english', 'postgresql & indexing');" SET QUERY PLAN ------------------------------------------------------------------------------------ Bitmap Heap Scan on idx_articles (cost=25.73..957.84 rows=800 width=4) Recheck Cond: (tsv @@ '''postgresql'' & ''index'''::tsquery) -> Bitmap Index Scan on idx_articles_tsv (cost=0.00..25.53 rows=800 width=0) Index Cond: (tsv @@ '''postgresql'' & ''index'''::tsquery) (4 rows) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM idx_articles WHERE tsv @@ plainto_tsquery('english', 'database performance');" SET QUERY PLAN ------------------------------------------------------------------------------------ Bitmap Heap Scan on idx_articles (cost=25.73..957.84 rows=800 width=4) Recheck Cond: (tsv @@ '''databas'' & ''perform'''::tsquery) -> Bitmap Index Scan on idx_articles_tsv (cost=0.00..25.53 rows=800 width=0) Index Cond: (tsv @@ '''databas'' & ''perform'''::tsquery) (4 rows)
🗂️ Build a GIN Index on JSONB for Containment Queries
Create a GIN index on the metadata column using jsonb_path_ops, then run a containment query with @>.
psql -U postgres -d beer_db -c "CREATE INDEX idx_articles_jsonb ON idx_articles USING GIN(metadata jsonb_path_ops);"psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM idx_articles WHERE metadata @> '{\"tag\": \"postgres\"}';"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_articles_jsonb ON idx_articles USING GIN(metadata jsonb_path_ops);" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT id FROM idx_articles WHERE metadata @> '{\"tag\": \"postgres\"}';" SET QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on idx_articles (cost=42.41..1425.01 rows=4848 width=4) Recheck Cond: (metadata @> '{"tag": "postgres"}'::jsonb) -> Bitmap Index Scan on idx_articles_jsonb (cost=0.00..41.19 rows=4848 width=0) Index Cond: (metadata @> '{"tag": "postgres"}'::jsonb) (4 rows)
Lab 3.2.4 complete. GIN indexes for full-text search and JSONB, measured directly:\n\n\n LIKE '%word%' baseline : ✅ Seq Scan, 379.777 ms, no index possible\n GIN tsvector index : ✅ Bitmap Index Scan, 159.385 ms — real speedup\n to_tsquery vs plainto_tsquery : ✅ same GIN index, two input styles\n GIN jsonb_path_ops + @> : ✅ Bitmap Index Scan on JSONB containment\n
Enable JavaScript to run the live terminal and track your progress.