Home

Using This Unknown SQL Pattern, We Reduce Our Query Time by 80%

We avoided a serious scalability catastrophe thanks to a straightforward CTE trick.


Under load, our system was buckling. Dashboards were clocking out, pages were sluggish, and customer service was beginning to notice.

The offender? A crucial query that had become more complicated and was utilized by several services. It became the slowest component of our stack as we added additional joins, filters, and computations over time.

We experimented with query hints, denormalization, and indexing. Before one developer updated it using an unexpected, little-known SQL approach, nothing worked:

Common Table Expressions, or CTEs, but utilized repeatedly and inline.

This single modification reduced our query time by more than 80%.

The Issue: An Excessive Analytical Question
A condensed summary of what we were running is as follows:
SELECT
    users.id,
    users.name,
    COUNT(DISTINCT orders.id) AS order_count,
    SUM(orders.amount) AS total_spent,
    MAX(logins.timestamp) AS last_login
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN logins ON users.id = logins.user_id
WHERE users.created_at >= '2023-01-01'
GROUP BY users.id;
On a table alongside:
  • Five million users
  • 50 million orders
  • 80 million logins
On average, it took more than 9.3 seconds.

Reusable inline CTEs are the solution.
The issue was with redundant joins and scans, not merely the amount of data. We were combining big tables, such as orders and logins, for each user in order to calculate aggregates.

Therefore, we refactored utilizing CTEs in order to pre-aggregate and reuse results in addition to simplifying logic.
WITH recent_users AS (
    SELECT id, name
    FROM users
    WHERE created_at >= '2023-01-01'
),
order_stats AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
),
last_logins AS (
    SELECT user_id, MAX(timestamp) AS last_login
    FROM logins
    GROUP BY user_id
)
SELECT
    u.id,
    u.name,
    o.order_count,
    o.total_spent,
    l.last_login
FROM recent_users u
LEFT JOIN order_stats o ON u.id = o.user_id
LEFT JOIN last_logins l ON u.id = l.user_id;
Each join now affects smaller, aggregated, and pre-computed datasets rather than raw tables.

Benchmark Findings
We compared the two versions to our production clone. This is what we observed:
| Query Version | Avg Time | % Improvement    |
| ------------- | -------- | ---------------- |
| Original      | 9.3s     | -                |
| CTE Refactor  | 1.8s     | **80.6% faster** |

Prior to this CTE restructure, indexing had a minor positive impact.

The Reason It Works
This speedup is based on two main ideas:

1. Steer clear of redundant work
In the original query, each LEFT JOIN recalculated aggregates per row after scanning whole tables. The query planner requires significantly less data when pre-aggregated CTEs are used.

2. Dismantling Complexity
Optimizers get confused by large requests. The database has more control over parallelization and subplan optimization when logic is divided into CTEs.

Things to Look Out for
  • CTEs used to serve as optimization barriers in PostgreSQL. They would not be in line as a result. Unless MATERIALIZED is used, non-materialized CTEs are inlined as of PostgreSQL 12.
  • This works best when you can reuse the output or minimize scan sizes, therefore don't use it for basic filters.
  • Keep an eye on the execution plan (EXPLAIN ANALYZE) to confirm the benefit; depending on the database, a subquery may occasionally outperform a CTE.
 
What We Discovered
  • When utilized for pre-aggregation, CTEs can significantly improve performance and are not merely for readability.
  • Silently, query complexity increases; without our knowledge, a quick query became a scalability block.
  • Both the query planner and humans benefit from better planning when reasoning is broken up.
Prior to Optimizing...
  • Examine your slowest pg_stat_statements queries.
  • Find duplicated logic and redundant joins.
  • Think about transferring bulky materials into reusable CTEs.
  • Final Thoughts: Always adjust benchmarks because what works in one schema might not work in another.
Even without modifying indexes or schema, CTEs can yield significant performance gains when used to pre-aggregate data and segregate logic. This is why they are frequently taught as a readability feature.

We were able to avoid a significant re-architecture thanks to this method. Try using CTEs for restructuring if you're dealing with slow analytics queries and long-running joins.

It could save you thousands of dollars in computation as well as days of irritation.
Hi There, I'm Yahya, and I enjoy sharing knowledge and experiences.