GIN, GiST & BRIN — Series 2

Preview — 3 of 10 questions

How can you make a substring search like WHERE name LIKE '%lap%' actually use an index, given that it's not a left-anchored prefix pattern?

javascript

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT * FROM products WHERE name LIKE '%lap%';       -- can use the trigram index
SELECT * FROM products WHERE name % 'laptop';           -- similarity search, also indexed

AEnable the pg_trgm extension and create a GIN (or GiST) index using gin_trgm_ops — it indexes overlapping 3-character sequences (trigrams) of the text, enabling arbitrary substring and similarity searches to use the index

BA plain B-tree index can already do this efficiently, no changes needed

CSubstring LIKE queries can never use any index type in PostgreSQL

DRewrite the query using SUBSTRING() instead of LIKE, which automatically enables index usage

When is an SP-GiST index preferred over a GiST index?

javascript

-- IP address prefix data is a classic SP-GiST use case
CREATE INDEX idx_blocks_range ON ip_blocks USING SPGIST (ip_range);

ASP-GiST is always faster than GiST for every use case and should be the default

BFor data with a natural non-balanced, space-partitioning structure — like IP address prefixes, phone number prefixes, or quad-tree-style spatial data — where the partitioning doesn't need rebalancing the way a GiST tree does

CSP-GiST only works for integer columns

DSP-GiST is a synonym for a partial GiST index

Why does a full-text search query typically use the @@ operator against a tsquery, rather than a plain LIKE '%word%'?

javascript

CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('english', body));

SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & performance');

A@@ is just a faster synonym for LIKE with no functional difference

Bto_tsvector() disables index usage entirely, forcing a sequential scan

Ctsvector/tsquery with @@ supports linguistic features — stemming (matching "running" to "run"), stop-word removal, and ranking — none of which plain LIKE substring matching provides, and the GIN index accelerates it

Dtsquery only supports single-word searches, never multi-word boolean queries

Answer all 10 questions (7 more), see explanations for every answer, and track your score.