$ lexprog.com

// notes from an old coder -- php, databases, and the occasional rant

[October 19, 2026] PostgreSQL

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)

────────────────────────────────────────────────────────
<-- back to posts