IntermediatePerformance & Indexes

GIN index for full-text and array search

The query

SQL

-- Full-text search index
CREATE INDEX idx_products_fts
ON products USING gin(TO_TSVECTOR('english', name));

-- JSONB index
CREATE INDEX idx_events_data
ON events USING gin(data);

-- Array index
CREATE INDEX idx_tags
ON articles USING gin(tags);

-- Check index usage
EXPLAIN SELECT * FROM products
WHERE TO_TSVECTOR('english', name) @@ TO_TSQUERY('bluetooth');

Tested against PostgreSQL 16

Note

“GIN (Generalized Inverted Index) is optimal for array, JSONB, and full-text search. Slower to build, faster to query.

Tables referenced

products6 cols