Indexing Strategy and Design Questions

Direct answer

The query optimizer decides whether to use an index (and which join order to pick) mostly by estimating how many rows each step of a plan will produce, and it gets those estimates from stored statistics: a null fraction, a distinct-value count, a most-common-values list, and a histogram of the rest of the distribution, all captured the last time ANALYZE ran (Postgres also runs this automatically via autovacuum's analyze component). When those statistics are stale, meaning the data has changed shape since the last ANALYZE but the stored numbers haven't been refreshed, the optimizer's row estimates become wrong, and a wrong estimate can flip its choice away from an index it should be using, or toward one it shouldn't. Diagnosing it starts with EXPLAIN ANALYZE, comparing the planner's ESTIMATED row count against the ACTUAL row count the query really returned: a large gap is the signature of stale (or entirely absent) statistics, and the fix is almost always to run ANALYZE on the affected table.

Structured elaboration

What statistics feed the optimizer. For each column, Postgres's planner statistics (queryable via pg_stats) include: null_frac (share of NULLs), n_distinct (estimated distinct values), a most-common-values array with their frequencies, and a histogram of the remaining value distribution. Multi-table plans also use these to estimate join output sizes, which is how a bad single-column estimate can cascade into a bad join order for an entire query.

Why staleness causes a full scan specifically. If the true, current distribution of a column has drifted so that a value the optimizer THINKS is common (from old statistics) is now actually rare, the optimizer will underestimate how selective a predicate on that value really is, and it may prefer a sequential (full-table) scan, reasoning that "this predicate barely narrows anything, so scanning everything and filtering is cheaper than the overhead of an index probe." The index was available the whole time; the optimizer's belief about the data was wrong.

What actually goes stale, precisely. It's worth being exact here: Postgres refreshes a table's approximate row COUNT and page count automatically at planning time from the relation's live size on disk (so gross growth in ROW COUNT is usually reflected even without a fresh ANALYZE), but the DISTRIBUTION statistics (the most-common-values list, the histogram, n_distinct) are frozen exactly as they were at the last ANALYZE until a new one runs. That is the real, specific thing that goes "stale": not "the table looks small" but "the optimizer's belief about which values are common has drifted from reality."

Worked example

Seeded on Postgres 16: a 1,000,000-row table where status = 'flagged' originally covered 50 percent of rows (a deliberately unselective value, so the optimizer correctly avoids using the index and instead does a bitmap-assisted scan reading most of the table, which is the right call at 50 percent selectivity):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM stale_demo WHERE status = 'flagged';
-- Bitmap Heap Scan, actual rows=499946
-- Execution Time: 65.352 ms          (correct plan for 50% selectivity)

Then almost all of the 'flagged' rows were deleted, leaving only 1,000 out of 1,000,000 (0.1 percent), WITHOUT running ANALYZE afterward:

DELETE FROM stale_demo WHERE status = 'flagged' AND id NOT IN (
  SELECT id FROM stale_demo WHERE status='flagged' ORDER BY random() LIMIT 1000
);
-- status  | count
-- flagged | 1000
-- normal  | 500054

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM stale_demo WHERE status = 'flagged';
-- Bitmap Heap Scan, cost=5618.43..18288.42 rows=504000  <- estimate still says 504,000!
--   actual rows=1000                                     <- but only 1,000 really matched
-- Execution Time: 39.138 ms, Buffers: shared hit=6793     <- touched nearly the whole table

The plan SHAPE and the COST ESTIMATE are unchanged from before the delete (still rows=504000, the stale most-common-values entry), even though the real data is now 500x more selective than the optimizer believes. Because the plan still assumes half the table matches, it scans nearly the whole thing (6,793 buffers touched) to find 1,000 rows, and it's carrying dead index entries from the deleted rows on top of that.

ANALYZE stale_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM stale_demo WHERE status = 'flagged';
-- Index Scan, cost=0.42..68.72 rows=952   <- now close to the real 1,000
-- Execution Time: 21.267 ms, Buffers: shared hit=7214

The cost estimate collapsed from 18,288 to 68.7 and the row estimate corrected from 504,000 to 952 (close to the true 1,000), and the plan switched from a bitmap scan to a plain, cheap Index Scan. Execution time is still elevated at 21.267 ms because the index itself is carrying bloat from the mass delete (dead entries ANALYZE doesn't clean up); running VACUUM afterward brought it down to 1.980 ms with the same correct plan, which is the separate, complementary fix: ANALYZE corrects the PLAN CHOICE, VACUUM reclaims the PHYSICAL SPACE.

Trade-offs & pitfalls

Diagnostic checklist for "the plan is doing a full scan and I think it shouldn't be":

1. Run EXPLAIN ANALYZE (not just EXPLAIN) and compare the estimated row count in the plan against the actual row count it reports. A large mismatch (an order of magnitude or more) is the tell.
2. Check pg_stats for the column in question: does the most-common-values list or histogram look like it reflects the CURRENT data, or an old distribution?
3. Check last_analyze / last_autoanalyze in pg_stat_user_tables for that table: if it's old relative to how much the data has changed, that confirms staleness.
4. Run ANALYZE on the table and re-check the plan. If the plan corrects itself, staleness was the cause.
5. If the table changes shape often and rapidly (heavy deletes, bulk loads, skewed updates), consider lowering autovacuum_analyze_scale_factor for that table so autovacuum re-analyzes more eagerly, rather than relying on manual intervention every time.

A gap this large between estimated and actual rows will often mislead the optimizer on JOIN ORDER too, not just single-table scan choice: an underestimated row count for one side of a join can make the optimizer pick a nested-loop join it thinks will only execute a handful of times, when the real row count makes that loop run far more often than planned, which is a much more expensive failure mode than a single missed index.

Direct answer

Treat this as a measurement problem before it is a deletion problem. Pull real usage statistics for every index on the table (scan counts, tuples read, index size), group the single-column indexes by which queries actually use them together, and only fold a group into one composite index when the leftmost-prefix rule still serves every query that used to hit the separate indexes. Roll it out on a staging replica with production-shaped data, add the new composite index first and leave the old ones in place for an observation window, then drop the old indexes one at a time (never all at once) while watching both query plans and write-side metrics.

A consolidation methodology

1. Inventory usage, not just existence. pg_stat_user_indexes (idx_scan, idx_tup_read, idx_tup_fetch) tells you which indexes are actually touched by reads; pg_relation_size tells you what each one costs to maintain. An index with idx_scan near zero over a full business cycle (including month-end, quarter-end, whatever your peak reporting window is) is a drop candidate on its own, independent of any consolidation work.
2. Cluster indexes by co-occurring predicates. Use pg_stat_statements or a slow-query log to find WHERE-clause shapes. If columns a, b, and c each have their own single-column index but every query that touches any of them actually filters on a AND b, or on a AND b AND c together, they are consolidation candidates.
3. Order the composite by the leftmost-prefix rule. A composite index on (a, b, c) still serves a query that filters on a alone, or on a and b, because a B-tree (a balanced, sorted tree structure that backs most indexes) stores entries sorted by the first column first, then the second within ties on the first, and so on. It does NOT serve a query filtering on b alone or c alone with no a predicate, because there is no way to jump into the sorted order without first fixing a. Put the column with equality filters first, range-filtered columns next, and any column only used for sorting last. Before you drop the single-column index on b, confirm nothing in production filters on b without a.
4. Prove the write-side win with a real measurement, not intuition. WAL (write-ahead log, the record every durable change is written to before the change is considered committed) volume for a fixed batch of writes is a clean, engine-level proxy for the extra maintenance cost that indexes add, and it does not depend on how warm your cache happens to be.
5. Roll out add-before-drop. Build the new composite index with CREATE INDEX CONCURRENTLY (avoids taking a lock that blocks writes), let it run alongside the old single-column indexes for an observation window, replay real query samples against it and check EXPLAIN confirms the plans you expect, then drop the redundant single-column indexes one at a time with DROP INDEX CONCURRENTLY, watching error rates and p95 latency (the 95th-percentile response time: 95% of requests finish at or below this value, a common way to watch a worsening tail without one-off outliers skewing an average) between each drop. Keep the exact CREATE INDEX statements for everything you drop in a rollback script, checked in next to the migration, not just remembered.

Worked example

I measured this on Postgres 16 rather than estimating it. First, the problem statement itself: a table with six single-column indexes (one primary key plus five independent single-column indexes) against the same table with only the primary key, inserting the same 200,000 rows into each, measuring WAL bytes generated via pg_current_wal_lsn() before and after (a page-accurate, engine-native measure of write volume, immune to caching effects that would confound a wall-clock timing comparison):

CREATE TABLE writeamp_1idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE TABLE writeamp_5idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE INDEX ON writeamp_5idx (a);
CREATE INDEX ON writeamp_5idx (b);
CREATE INDEX ON writeamp_5idx (c);
CREATE INDEX ON writeamp_5idx (d);
CREATE INDEX ON writeamp_5idx (e);

CHECKPOINT;
SELECT pg_current_wal_lsn() AS lsn_before \gset
INSERT INTO writeamp_1idx (id, a, b, c, d, e)
SELECT g, g%1000, g%1000, g%1000, g%1000, g%1000 FROM generate_series(1, 200000) g;
SELECT pg_current_wal_lsn() AS lsn_after \gset
SELECT pg_size_pretty(pg_wal_lsn_diff(:'lsn_after', :'lsn_before')); -- 29 MB

-- repeat CHECKPOINT + the same INSERT pattern against writeamp_5idx -- 97 MB

Real result: inserting the identical 200,000 rows generated 29 MB of WAL (30,907,856 bytes precisely) against the primary-key-only table and 97 MB (101,636,336 bytes precisely) against the six-index table. At full byte precision that is a 3.29x increase from adding five redundant single-column indexes; the rounded MB figures alone (29 and 97) divide to roughly 3.34x, so verify this ratio from the byte counts above, not from the rounded MB display. That is the concrete shape of "heavy write amplification."

Then the fix, measured the same way: three single-column indexes on (a), (b), (c) consolidated into one composite index on (a, b, c), same 200,000-row insert:

CREATE TABLE writeamp_3single (id bigint PRIMARY KEY, a int, b int, c int);
CREATE INDEX ON writeamp_3single (a);
CREATE INDEX ON writeamp_3single (b);
CREATE INDEX ON writeamp_3single (c);

CREATE TABLE writeamp_1composite (id bigint PRIMARY KEY, a int, b int, c int);
CREATE INDEX ON writeamp_1composite (a, b, c);
-- same CHECKPOINT / pg_current_wal_lsn() / INSERT 200000 rows pattern against each

Real result: 68 MB of WAL for the three-single-index table, 43 MB for the one-composite-index table, a 1.58x reduction. On-disk index size dropped from 9,832 kB (three indexes combined) to 6,264 kB (one composite), also a 1.57x reduction, consistent with the WAL number since less index structure means less to log on every insert.

Trade-offs and pitfalls

Consolidation is not free in the other direction: a composite index only helps queries that match its leftmost-prefix shape, so merging three single-column indexes into one composite silently breaks any query that filtered on the second or third column alone. That is exactly why step 2 of the methodology (cluster by real co-occurring predicates from production query logs) has to come before step 3 (pick column order), not after. CREATE INDEX CONCURRENTLY avoids blocking writes, but it can still fail partway through (a uniqueness violation encountered during the scan, for instance) and leave an INVALID index behind that consumes write overhead while being ignored for reads, so the rollout script needs a check for pg_index.indisvalid after each build, with DROP INDEX CONCURRENTLY and retry as the documented recovery path. Finally, do not chase "fewer indexes" as a goal in itself: a single wide composite index that now serves five different query shapes, several of them only through a non-leading column that used to have its own dedicated index, can become a bigger, hotter piece of structure than the sum of the indexes it replaced, with its own write and cache-locality cost. Consolidate where the leftmost-prefix rule and real usage data support it; leave the rest alone.

Direct answer

An index-only scan lets the database answer a query straight from the index, skipping the table (heap) entirely, but only when two conditions both hold: every column the query needs is present in the index, AND the database can prove, for each candidate row, that its current version is visible to the query without checking the table. In Postgres, that second check is done via the visibility map, a small per-table bitmap that marks which data pages contain only rows every current transaction is allowed to see. Right after a bulk load or heavy write activity, the visibility map is not yet set for the new or changed pages, so even a genuinely covering index falls back to fetching from the heap row by row, silently, until a VACUUM catches up. Two other common causes: the query selects a column the index doesn't include (classically SELECT *), or the predicate itself isn't sargable so the planner never even considers that index.

Structured elaboration

What "covering" actually requires. A covering index must contain every column the query reads, not just the ones in the WHERE clause. In Postgres this usually means an INCLUDE clause carrying non-key payload columns alongside the indexed predicate columns.

The visibility map, in one sentence. Every heap page has a visibility-map bit that says "every row on this page is visible to every transaction, so a scan of the index alone doesn't need to double-check the heap for this page." Bulk-loaded or recently-updated pages start with that bit clear, so the planner may still CHOOSE an index-only scan (the query shape supports it), but the EXECUTOR still has to fetch from the heap for every row on a not-yet-visible page. EXPLAIN ANALYZE reports this directly as Heap Fetches: N. A healthy index-only scan shows Heap Fetches: 0.

What actually clears the visibility map. VACUUM (including autovacuum) is what sets visibility-map bits. Nothing else does automatically at any useful cadence.

Worked example

Seeded on Postgres 16: accounts(id, account_status, region, balance_cents, updated_at), 800,000 rows, account_status = 'suspended' matching about 2 percent of rows (16,002 rows), with a genuinely covering index:

CREATE INDEX idx_accounts_status_balance ON accounts (account_status) INCLUDE (balance_cents);

Right after the bulk load and index build, before any VACUUM:

EXPLAIN (ANALYZE, BUFFERS)
SELECT account_status, balance_cents FROM accounts WHERE account_status = 'suspended';
-- Index Only Scan using idx_accounts_status_balance
--   Heap Fetches: 16002        <- every single matched row still hit the heap
--   Execution Time: 6.152 ms

The planner correctly PICKED an index-only scan (the plan node says so), but Heap Fetches: 16002 proves every row still needed the table, because the visibility map was not yet set for these pages.

VACUUM accounts;

EXPLAIN (ANALYZE, BUFFERS)
SELECT account_status, balance_cents FROM accounts WHERE account_status = 'suspended';
-- Index Only Scan using idx_accounts_status_balance
--   Heap Fetches: 0
--   Execution Time: 1.054 ms

After VACUUM, Heap Fetches drops to 0 and execution time falls from 6.15 ms to 1.05 ms, roughly 6x, purely from the visibility map catching up. The plan's estimated cost fell in step too (2816.41 before, 545.30 after), because the planner's own cost model accounts for expected heap fetches.

Now the "select an uncovered column" trap, run against the SAME, already-vacuumed index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM accounts WHERE account_status = 'suspended';
-- Index Scan using idx_accounts_status_balance   <- note: plain Index Scan, not Index Only Scan
--   Execution Time: 6.402 ms

SELECT * needs id, region and updated_at, none of which are in the index, so Postgres falls back to an ordinary index scan (heap lookup for every row) regardless of how well-vacuumed the table is. This is the realistic complicating factor a candidate cannot always fix: if the query comes from a client library or an ORM (object-relational mapping) tool that always emits SELECT * and cannot be changed, no amount of index tuning recovers the index-only path; the only real fixes are changing the query (list only the needed columns) or broadening the index's INCLUDE list to cover everything that client actually touches.

Trade-offs & pitfalls

Diagnostic checklist for "why isn't my index-only scan happening":

1. Confirm the index actually covers every column the query reads (check the SELECT list, not just the WHERE clause), including columns hidden behind SELECT *.
2. Run EXPLAIN (ANALYZE, BUFFERS) and read Heap Fetches, not just the plan node name. A non-zero, especially a 100 percent, heap-fetch rate on an Index Only Scan node is the visibility-map signature.
3. Run (or wait for autovacuum to run) VACUUM on the table and re-check. If Heap Fetches drops to 0, that confirms visibility map staleness was the cause.
4. If the table is write-heavy enough that autovacuum can't keep the visibility map current between bulk changes, that's a tuning signal on its own (lower autovacuum_vacuum_scale_factor for that table, or schedule explicit VACUUMs after bulk loads).
5. If Heap Fetches is already 0 and the plan still isn't an Index Only Scan, re-check for an uncovered column, or a predicate the planner can't push into the index at all (a function-wrapped column without a matching expression index, for example).

The cost of a covering index is not free: every extra INCLUDEd column widens each index entry, so covering indexes trade some write and storage cost for the read-side win of skipping the heap. Widening an INCLUDE list to cover a SELECT * client is a real fix, but it's worth first asking whether the client actually needs every column, since a narrower, explicit column list is both cheaper to index and clearer about what the code depends on.

Direct answer

An index scan (often called an "index seek" in some database products) finds rows by first consulting a small, sorted lookup structure, then going straight to only the rows that match. A sequential scan reads every single row in the table, in order, and checks each one against the filter, whether or not it matches. To a non-technical stakeholder: imagine looking up a name in a phone book (index scan, jump straight to the right page) versus reading every entry in the phone book cover to cover to find every instance of that name (sequential scan). A report that takes 30 seconds and filters on a column with no supporting index is doing the second thing, checking every row in the table one at a time, and adding an index is often the difference between "read every row" and "read only the ones that matter."

Structured elaboration

The non-technical explanation, in full. A database table with millions of rows is like a massive, unsorted filing cabinet. Asking "find me the orders for this one product" without an index means checking every single folder in the cabinet, one at a time, to see if it's a match. An index is a separate, sorted card catalog that says exactly where in the cabinet each product's orders live, so the search becomes "look up the card, go straight to the right drawer" instead of "check every folder." The bigger the filing cabinet, the bigger that difference gets: doubling the table roughly doubles the sequential-scan search time, but barely changes the index-lookup time at all, because the index only grows by a few extra "levels" of sorting.

The honest counter-case: adding an index does NOT always help. This is the part that's easy to oversell to a stakeholder who just heard "indexes make things fast." If a filter matches a large share of the rows (not "one specific product" but "all orders placed in the last two years," when the table only goes back three), the card catalog doesn't save much: you still end up pulling most of the folders anyway, plus you now paid for the extra step of consulting the catalog first. Worse, every index has to be kept up to date every time a new folder is added to the cabinet or an existing one is changed, so an index that rarely helps searches is pure ongoing cost with no offsetting benefit: slower writes, more storage, for a search pattern it was never actually good at speeding up.

Worked example

The "adding an index helps" case: order_items(order_item_id, order_id, product_id, quantity, order_date), 800,000 rows, searching for one specific product out of 5,000 distinct products (a highly selective, narrow-matching filter):

EXPLAIN (ANALYZE, BUFFERS) SELECT order_item_id, quantity FROM order_items WHERE product_id = 3141;

Before an index: Parallel Seq Scan on order_items (actual execution time 10.722 ms, reading 5,883 buffer pages, most of the table, to find 162 matching rows out of 800,000). After CREATE INDEX idx_order_items_product_id ON order_items (product_id);: Bitmap Heap Scan using the new index (actual execution time 1.555 ms, reading only 164 buffer pages). Roughly a 7x reduction in this run, by going straight to the matching rows instead of checking all 800,000.

The counter-case: the same table's quantity column has only 5 distinct values (1 through 5), so WHERE quantity = 3 matches roughly 160,000 of the 800,000 rows, about 20%, a filter that is NOT narrow:

CREATE INDEX idx_order_items_quantity ON order_items (quantity);
EXPLAIN (ANALYZE, BUFFERS) SELECT order_item_id FROM order_items WHERE quantity = 3;

With the index present and chosen by the optimizer: 90.916 ms. Forcing a plain scan of the whole table for direct comparison: 28.008 ms, meaning in this run the "have an index and use it" plan was actually SLOWER than reading the entire table straight through. This is the real, measured proof of the counter-case: a card catalog that has to point you to a fifth of the filing cabinet anyway isn't saving much, and the extra step of consulting it can cost more than it saves.

Trade-offs & pitfalls

When explaining this to a non-technical stakeholder, resist the urge to promise "we'll just add an index and it'll be fast," since that promise is only true for narrow, highly selective searches (find one specific thing among many), not for broad ones (find a large chunk of everything). The more useful, honestly-framed message is: how specific is the thing we're searching for, relative to the whole dataset? The more specific, the more an index helps; the broader the search, the less it helps, and sometimes it isn't worth the ongoing cost of maintaining it at all.

Direct answer

For arbitrary substring ("contains") search over a text column at scale, a trigram index is the right default: it breaks each value into overlapping 3-character sequences and indexes those, so it can serve LIKE '%bluetooth%'-style queries (with the match anywhere in the string, not just at the start) far faster than a sequential scan. Full-text search (word-based, with ranking and language-aware stemming) is a related but different tool, better suited to "does this document contain these words" than to raw substring matching. An inverted index is the general structural idea both are built on: map each small unit (a trigram, or a word) to the set of rows containing it, so a search becomes a lookup instead of a scan. Under frequent writes, this class of index defers most of its maintenance work into background batches rather than updating the full structure synchronously on every write, which keeps individual insert/update latency from paying the full indexing cost up front.

Structured elaboration

Why a plain B-tree cannot serve this. A B-tree index only helps a LIKE pattern anchored to the start of the string ('foo%'), because it can jump to that prefix in sorted order. A pattern with a leading wildcard ('%foo%') could match anywhere in the string, so there's no sorted starting point a B-tree can jump to; it degrades to a full scan regardless of whether an index exists.

Trigram indexing (Postgres's pg_trgm extension, backed by GIN, short for Generalized Inverted Index). Break 'Bluetooth Headphones' into overlapping 3-character chunks: 'blu', 'lue', 'uet', 'eto', and so on. Index every chunk, for every row, in that GIN structure. A search for %bluetooth% also gets trigrammed, and the index can quickly find rows sharing enough of the same trigrams, then verify the exact match (this is exactly the "Recheck" step a bitmap-style scan performs). This approach works for arbitrary substrings, typos, and fuzzy similarity search, at the cost of index size (trigram indexes are usually a meaningful fraction of the indexed text's own size) and per-write overhead (every insert/update touches several trigram entries, not just one index entry).

Full-text search, for comparison. Postgres's full-text search converts text into a tsvector (a normalized, stemmed, stop-word-filtered representation: "running" and "runs" both reduce toward "run", common words like "the" are dropped) and indexes THAT with GIN. It answers "which documents contain these words" with relevance ranking, and handles language variation well, but it is not built for arbitrary substring matching (searching for a fragment of a word, or a non-word token) the way trigram search is. For "contains" search over arbitrary substrings specified by users, trigram is the closer fit; for "search these words with ranking," full text is.

Maintaining these indexes during frequent writes. GIN indexes (which back both trigram and full-text search in Postgres) use a "fastupdate" pending list by default: new entries land in a small, unsorted staging area first, deferring the expensive, sorted insertion into the main index structure. This keeps individual write latency down, at the cost of queries having to also scan the pending list until it's flushed. The pending list flushes automatically once it exceeds gin_pending_list_limit (4 MB by default, configurable per-index), either inline (blocking the triggering write) or, preferably, in the background via VACUUM/autovacuum. For a genuinely write-heavy table, tuning gin_pending_list_limit upward and making sure autovacuum is aggressive enough to keep the pending list from growing unbounded is the standard operational lever.

Worked example

products(product_id, description), 300,000 rows, searching for 'bluetooth' anywhere in the description:

EXPLAIN (ANALYZE, BUFFERS)
SELECT product_id, description FROM products WHERE description ILIKE '%bluetooth%';

Before any supporting index:

Seq Scan on products (cost=0.00..6956.00 rows=30303 width=55) (actual time=0.090..161.986 rows=30017 loops=1)
  Filter: (description ~~* '%bluetooth%'::text)
  Rows Removed by Filter: 269983
Execution Time: 162.599 ms

After:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_description_trgm ON products USING gin (description gin_trgm_ops);

Bitmap Heap Scan on products (cost=269.37..3854.16 rows=30303 width=55) (actual time=3.477..20.674 rows=30017 loops=1)
  Recheck Cond: (description ~~* '%bluetooth%'::text)
  Heap Blocks: exact=3206
  ->  Bitmap Index Scan on idx_products_description_trgm (actual time=3.271..3.271 rows=30017 loops=1)
        Index Cond: (description ~~* '%bluetooth%'::text)
Execution Time: 21.246 ms

Roughly a 7.6x reduction in this run (162.6 ms to 21.2 ms), and the Recheck Cond line confirms the index only narrowed candidates down (via trigram overlap), the exact substring match was then verified against the real value, exactly the two-phase behavior described above. Measured storage cost: the trigram index came to 20 MB against a 25 MB table, a substantial fraction of the table's own size, the direct, honest cost of indexing every overlapping 3-character chunk of every row.

Trade-offs & pitfalls

Trigram indexes are large relative to the data they cover and add real per-write overhead (mitigated, not eliminated, by GIN's fastupdate batching); reach for one when substring search genuinely needs to happen at query time, not as a default on every text column. Very short search terms (one or two characters) barely benefit, since a 2-character or shorter pattern doesn't even produce a usable trigram, and the index degrades toward a full scan for those. For word-based search where users are looking for documents matching whole terms rather than arbitrary substrings, full-text search's ranking and stemming will usually give better results than trigram matching would, even though both are GIN-backed and share the same write-maintenance trade-offs.