SQL Rewriting Patterns: Transforming Slow Queries for Sub-Millisecond Execution

When optimizing relational database performance, database administrators and software engineers often turn first to hardware upgrades or index creation. However, in high-throughput enterprise applications, the root cause of query latency is frequently non-sargable SQL expressions, subquery anti-patterns, and misuse of CTEs (Common Table Expressions).

Even with an optimal indexing strategy, a poorly written SQL query can force the PostgreSQL Cost-Based Optimizer (CBO) to abandon index scans in favor of full table sequential scans, generating millions of unnecessary disk reads and CPU cycles.

This guide explores four high-impact SQL rewriting patterns designed to transform slow, resource-heavy queries into execution paths running in sub-millisecond times.

1. Transforming Non-Sargable Predicates (SARGability)

The Anti-Pattern

SARGable stands for Search Argument Able. A query predicate is non-sargable when an expression, function, or data type conversion is applied directly to an indexed column in the WHERE clause. This prevents PostgreSQL from using a B-Tree index lookup, forcing a sequential scan across the entire table.

-- SLOW: Function applied directly to indexed column 'created_at'
SELECT * FROM orders 
WHERE DATE(created_at) = '2026-01-15';

-- SLOW: String transformation on indexed column 'email'
SELECT * FROM users 
WHERE LOWER(email) = 'admin@example.com';

The Rewrite Pattern

Move all functions and mathematical operations to the target literal side of the expression so the indexed column remains isolated.

-- FAST: Sargable range condition leverages B-Tree index on 'created_at'
SELECT * FROM orders 
WHERE created_at >= '2026-01-15 00:00:00' 
  AND created_at <  '2026-01-16 00:00:00';

-- FAST: Case-exact match, or build Expression Index if case-insensitive search is mandatory
-- Option A: Exact Match
SELECT * FROM users WHERE email = 'admin@example.com';

-- Option B: Functional Index Alternative
-- CREATE INDEX idx_users_lower_email ON users (LOWER(email));

Non-Sargable: [ Table Scan (1M Rows) ] ---> [ Apply DATE() Function ] ---> Filter Match
Sargable:     [ B-Tree Index Probe ] ---> Direct Block Lookup (10 Rows)

2. Eliminating IN (SELECT ...) Subquery Bottlenecks

The Anti-Pattern

Using IN (SELECT ...) with correlated subqueries forces the database optimizer to evaluate the inner query repeatedly or build temporary arrays, leading to high CPU usage and potential nested loop explosions.

-- SLOW: IN subquery with potential duplicate evaluations
SELECT * FROM customers 
WHERE id IN (
    SELECT customer_id FROM orders WHERE total_amount > 1000
);

The Rewrite Pattern

Rewrite using EXISTS or explicit INNER JOIN with early grouping to allow the query planner to choose efficient Hash Join or Semi-Join execution paths.

-- FAST: EXISTS uses Semi-Join semantics and halts at first matching row
SELECT c.* 
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
      AND o.total_amount > 1000
);

3. Controlling CTE Materialization (MATERIALIZED vs. NOT MATERIALIZED)

The Anti-Pattern

Prior to PostgreSQL 12, Common Table Expressions (CTEs / WITH clauses) acted as strict optimization barriers. The database executed the CTE separately and wrote results to an in-memory or disk-backed temporary table, discarding outer WHERE clause filters.

In modern PostgreSQL, while CTEs are inline by default, complex CTE structures or explicit MATERIALIZED flags can still inadvertently trigger unnecessary temp table writes.

SQL

-- SLOW if CTE returns 500,000 rows before outer filter is applied
WITH recent_orders AS MATERIALIZED (
    SELECT id, customer_id, total_amount, created_at 
    FROM orders
)
SELECT * FROM recent_orders 
WHERE created_at >= '2026-01-01' AND total_amount > 500;

The Rewrite Pattern

Force inline execution using NOT MATERIALIZED or replace the CTE with a standard subquery or view so the optimizer can push filters down directly to the underlying indexes.

SQL

-- FAST: Pushes outer predicates into the CTE evaluation tree
WITH recent_orders AS NOT MATERIALIZED (
    SELECT id, customer_id, total_amount, created_at 
    FROM orders
)
SELECT * FROM recent_orders 
WHERE created_at >= '2026-01-01' AND total_amount > 500;

4. Converting Correlated Subqueries to LATERAL JOIN

The Anti-Pattern

Fetching top $N$ records per parent row (e.g., getting the 3 most recent orders for every customer) using correlated subqueries in the SELECT projection forces iterative row-by-row lookups.

SQL

-- SLOW: Evaluates inner subquery repeatedly per customer
SELECT c.id, c.name,
    (SELECT o.total_amount FROM orders o WHERE o.customer_id = c.id ORDER BY o.created_at DESC LIMIT 1) as latest_order
FROM customers c;

The Rewrite Pattern

Leverage LEFT JOIN LATERAL, which allows the inner subquery to reference columns from the preceding outer relation while enabling index-accelerated LIMIT evaluations.

-- FAST: LATERAL join evaluates using index scans per outer row
SELECT c.id, c.name, latest_order.total_amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.total_amount 
    FROM orders o 
    WHERE o.customer_id = c.id 
    ORDER BY o.created_at DESC 
    LIMIT 1
) latest_order ON true;

Summary Refactoring Cheat Sheet

Slow Anti-Pattern	Faster Rewrite Pattern	Underlying Performance Impact
WHERE DATE(col) = '2026-01-01'	WHERE col >= '2026-01-01' AND col < '2026-01-02'	Restores B-Tree index scan (SARGable)
WHERE id IN (SELECT ...)	WHERE EXISTS (SELECT 1 ...)	Enables Hash Semi-Join execution
Heavy WITH cte AS (...)	WITH cte AS NOT MATERIALIZED (...)	Allows predicate pushdown into CTE
Correlated SELECT Subquery	LEFT JOIN LATERAL (...) ON true	Accelerated Top-N index evaluation