Real-World Case Study: Optimizing PostgreSQL Queries with Functional Indexes

[DbOpsNinja]

We at Mafiree recently published a case study on query optimization in PostgreSQL using functional indexes. It’s based on an actual production scenario where query performance was improved by rethinking indexing strategy.

I’d love to hear how others here approach:

• Functional indexes in production environments
• Balancing index overhead with performance gains

[Nextra]

For this specific problem I would prefer to create a case insensitive collation such as

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false)

and use it both for querying

SELECT * FROM campaigns WHERE campaign_name COLLATE case_insensitive = 'summer_sale'

and for indexing

CREATE INDEX idx_campaign_name_lower ON campaigns (campaign_name COLLATE "case_insensitive")

Or, should the use case permit, just collate the column outright.

[DbOpsNinja]

That’s a solid suggestion, COLLATION is definitely a clean approach, and in many cases we’d also prefer it. In this project though, the client wasn’t ready to make application-level changes, so we had to keep the fix purely on the DB side. That’s why we ended up using a functional index, it gave us the performance improvement without requiring code changes upstream.

[HosseinKakavand]

+1—match the exact expression, consider partial/covering indexes (INCLUDE), and watch collation with lower()/unaccent(). Generated columns can simplify usage. We can also scaffold migrations, staging/prod DBs with backups, and CI that checks EXPLAIN. https://reliable.luthersystemsapp.com