Query Rewrite

SUMMARY

Query rewrite is a database optimization technique that transforms SQL queries into semantically equivalent but more efficient forms before execution. This process occurs during query optimization and can significantly improve query performance by simplifying expressions, eliminating redundancies, and leveraging database-specific optimizations.

How query rewrite works

Query rewrite operates as an early phase of query optimization, applying a series of transformation rules to the query's logical plan. These transformations can include:

Predicate pushdown to reduce data processing early
View merging to eliminate unnecessary intermediate results
Common Table Expression inlining
Constant folding and expression simplification
Subquery flattening and decorrelation

Next generation time-series database

QuestDB is an open-source time-series database optimized for market and heavy industry data. Built from scratch in Java and C++, it offers high-throughput ingestion and fast SQL queries with time-series extensions.

Try live demo Read documentation

Common rewrite patterns

Predicate simplification

Query rewrite often simplifies complex

WHERE

conditions by applying logical transformations:

-- ⚠️ ANSI (requires QuestDB adaptation)
-- Before rewrite
SELECT * FROM trades
WHERE price > 100 AND price > 200

-- After rewrite
SELECT * FROM trades
WHERE price > 200

Subquery flattening

Complex nested queries can often be rewritten as simpler joins:

-- ⚠️ ANSI (requires QuestDB adaptation)
-- Before rewrite
SELECT * FROM trades t1
WHERE EXISTS (
    SELECT 1 FROM trades t2 
    WHERE t2.symbol = t1.symbol 
    AND t2.price > 100
)

-- After rewrite
SELECT DISTINCT t1.* FROM trades t1
JOIN trades t2 ON t2.symbol = t1.symbol
WHERE t2.price > 100

Next generation time-series database

Try live demo Read documentation

Time-series specific rewrites

In time-series databases, query rewrite often focuses on optimizing temporal operations:

Window function optimization

The optimizer may rewrite window functions to leverage time-based partitioning:

SELECT symbol, 
    price,
    LAG(price) OVER (PARTITION BY symbol ORDER BY timestamp)
FROM trades

Time-based join optimization

Complex temporal joins might be rewritten to use more efficient ASOF join operations:

SELECT trades.symbol, trades.price, quotes.bid
FROM trades ASOF JOIN quotes

Benefits and considerations

Query rewrite provides several advantages:

Improved query performance through logical optimization
Reduced I/O and processing requirements
Better utilization of database-specific features
Automatic query improvement without application changes

However, developers should consider:

Rewrite rules may vary between database systems
Complex queries might have multiple valid rewrite options
Some rewrites may affect result ordering
Materialized views can affect available rewrites

Performance impact

Query rewrite can significantly impact performance by:

Reducing the amount of data processed
Eliminating redundant operations
Enabling better use of indexes
Allowing for parallel execution
Optimizing partition pruning

Next generation time-series database

Try live demo Read documentation

Best practices

To maximize the benefits of query rewrite:

Write queries in a clear, standard form
Avoid overly complex expressions that might limit rewrite options
Use database-native features when available
Monitor query plans to understand applied rewrites
Consider materializing complex views for frequently used queries

Query Planner - Works with query rewrite to optimize execution
Predicate Pushdown - Common rewrite optimization
Expression Simplification - Part of query rewrite process
Cost-based Optimizer - Uses rewritten queries for plan selection