PostgreSQL Expression Indexes
PostgreSQL Expression Indexes
PostgreSQL Expression Indexes
Tip: Lowercase Index
CREATE INDEX idx_posts_lower_title ON posts (LOWER(title));
Gotcha: Query Must Match Expression
SELECT * FROM posts WHERE LOWER(title) = 'hello';
The query must use the same expression as the index.
Tip: Trigram Index
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_posts_title_trgm ON posts USING gin (title gin_trgm_ops);
Enables fast LIKE '%term%' queries.
Gotcha: Expression Index Size
Expression indexes are larger than simple column indexes. They store the computed value.
Tip: Date Expression Index
CREATE INDEX idx_posts_created_date ON posts (DATE(created_at));
Gotcha: Write Overhead
Every insert/update must compute and store the expression. Balance read vs write performance.
Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend
For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.
Tip: Partial Indexes Are Underutilized
CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.
Gotcha: NULL Sorting Is Non-Obvious
By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.
Senior Insight
Expression indexes in PostgreSQL index the result of a function or expression, not the column value itself. I've used them to index lowercased email addresses (LOWER(email)), JSONB field extractions, and computed columns. The critical rule: use the exact same expression in your queries that you used in the index definition. Even a whitespace difference can make PostgreSQL ignore the index. Test with EXPLAIN to verify index usage.
Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)