Optimizing PostgreSQL Queries with Functional Indexes – A Real-World Case Study

Modern applications rely heavily on databases to power real-time workloads. But as usage scales, even the most robust systems like AWS Aurora PostgreSQL can face serious performance bottlenecks. One of the most common symptoms is high CPU utilization, which directly impacts query response times, application stability, and ultimately, user experience.

Recently, one of our clients faced exactly this challenge. Their Aurora PostgreSQL primary instance was under severe stress — CPU spikes were frequent, queries were slowing down, and scaling up the hardware only provided short-term relief. The real problem lay deeper in the way queries, views, and indexes were designed.

In this blog, we’ll walk you through how our PostgreSQL team diagnosed the bottlenecks, optimized queries, introduced indexing strategies (including functional indexes), and reduced query execution time from over 10 minutes to under a second. More importantly, we’ll show you how these optimizations not only stabilized CPU usage but also delivered sustainable performance improvements without the constant need for costly infrastructure upgrades.

The Problem

Our investigation revealed three key issues:

• High tuple scans — more than 1.9 billion tuples fetched and ~5 billion returned on the primary.
• Expensive VIEWs — one VIEW alone had an execution cost of ~6.9M, repeatedly causing CPU spikes.
• Underutilized replicas — read queries were directed to the primary, leaving replicas idle.

This combination led to frequent CPU spikes, degraded performance, and a poor end-user experience.

Step 1: Diagnosing the Pain Point

The client had a reporting dashboard built on top of complex PostgreSQL views. These views joined multiple tables and applied transformations for filtering and reporting.

Users reported that running certain reports would cause the system to spike CPU to 90–100%, slowing down other workloads. Running EXPLAIN ANALYZE on a few problem queries confirmed our suspicion: PostgreSQL was performing sequential scans across hundreds of millions of rows.

At this point, the obvious culprit was inefficient query patterns inside the views.

Step 2: Optimizing the View

Our first attempt was to rewrite the view. We reduced redundant joins, removed unused columns, and added a few plain indexes to support filtering.

While this optimization shaved off some milliseconds, the core issue remained. Queries were still triggering sequential scans, which made them unbearably slow at scale.

In short:

• View optimization improved performance for some queries.
• But certain conditions still forced full scans.

Step 3: Why We Couldn’t Just Rewrite Queries

The next logical step was to examine why those full scans persisted.

We discovered that the application layer was issuing queries with transformations applied to columns. In this case:

• The app allowed user input in mixed cases (e.g., “Summer_Sale”, “summer_sale”, “SUMMER_SALE”).
• To ensure case-insensitive comparisons, queries used the LOWER() function in conditions.
• Because of this, PostgreSQL ignored normal indexes on the column.
• Refactoring the application to normalize inputs wasn’t an option — it would have required weeks of development, testing, and downtime.

So, while we had found the root cause, we needed a database-side fix that worked with the existing queries.

Step 4: The Breakthrough with Functional Indexes

This is where functional indexes came in.
Unlike a regular index, which is created on raw column values, a functional index lets you index the result of an expression. Exactly what we needed for LOWER(campaign_name).

-- Functional index to support case-insensitive lookups
CREATE INDEX idx_campaign_name_lower 
      ON campaigns (LOWER(campaign_name));

Now, when a query applied LOWER(campaign_name) in the condition, PostgreSQL could use the index directly.

Step 5: Before vs After

Before (no functional index):

EXPLAIN ANALYZE
SELECT * 
FROM ……….
WHERE LOWER(campaign_name) = 'summer_sale';
Seq Scan on campaigns (cost=6940386.83..7267677.19 rows=2909221 width=977)
Execution Time: 614,532 ms [11+ mins]

After (with functional index):

EXPLAIN ANALYZE
SELECT * 
FROM ……… 
WHERE LOWER(campaign_name) = 'summer_sale';
Index Scan using idx_campaign_name_lower on campaigns (cost=150587.49..150587.51 rows=10 width=568)
Execution Time: 696.315 ms

The difference is dramatic — from full sequential scans on ~190M rows to a direct index scan.

The Results in Numbers

Here’s what changed with the functional index in place:

• Execution cost dropped from 6.9 million → 3.2.
• Tuples fetched reduced from 1.9B → ~24M.
• Execution time went from 10+ minutes → <1 second.
• CPU utilization stabilized, even during reporting peaks.

Key Takeaways

• Always review read/write query routing in PostgreSQL.
• Poorly written VIEWs can silently cause massive performance issues.
• Indexing strategy (including functional indexes) plays a crucial role in query optimization.
• Scaling hardware helps temporarily but fixing the root cause at the query level ensures long-term stability.

With our PostgreSQL team’s expertise, we not only fixed the client’s immediate CPU problem but also helped them adopt best practices for sustainable database performance.

How Mafiree Can Help You:

At Mafiree, we provide expert support on:

• Database performance tuning (query optimization, indexing, execution plan analysis)
• High availability & replication setup (Patroni, HAProxy, etc.)
• Database migration & upgrades
• Monitoring, automation, and proactive health checks
• End-to-end PostgreSQL support for production workloads

If your organization is facing performance challenges, our team of PostgreSQL experts can help you stabilize, optimize, and future-proof your database infrastructure.