Query Rewriting: Your Database's Unseen Optimizer

Why it exists

The way a human writes a query is often not the most efficient way for a computer to execute it. Databases needed an automatic way to transform logically correct but slow queries into faster equivalents without user intervention, ensuring better performance by default.

The mental model

Think of query rewriting like a GPS for your data. You give it a destination (the results you want), and it analyzes the map (your tables, indices, and stats) to find the fastest route (the execution plan). It might re-route you (reorder operations) to avoid traffic jams (expensive computations on large datasets).

How it works

The rewriter uses the mathematical rules of relational algebra to transform your query into an equivalent but more performant version. It knows that certain operations are commutative and associative. For example, filtering a table before joining it to another massive table is usually much faster than joining first and then filtering the huge result. The rewriter can prove these transformations are safe—they always produce the same final result—and then chooses the one it predicts will be fastest based on table statistics.

When to use it

You don't "use" it directly; the database does it for you. It's a core feature of most SQL and many NoSQL database query planners. Understanding that it exists helps you interpret EXPLAIN plans and write queries that the optimizer can work with effectively, rather than fighting against it.

When not to use it

You can't turn it off, but you can write "optimizer-unfriendly" queries that prevent it from working well. For instance, using complex functions on columns inside a WHERE clause can prevent the database from using an index, as the rewriter cannot easily reason about the function's output. This forces a full table scan that the rewriter could have otherwise avoided.

One canonical example

Imagine you write SELECT * FROM orders CROSS JOIN customers WHERE orders.date > '2023-01-01' AND customers.country = 'USA'. A naive execution would first create a massive intermediate table from the CROSS JOIN. The query rewriter knows this is inefficient. It will transform the query to filter orders and customers first based on their respective WHERE clauses, and only then join the much smaller, pre-filtered result sets. The result is identical, but the execution is orders of magnitude faster.