Query rewriting

The query rewriting subsystem improves the performance of certain queries by rewriting the query into a better performing variant.

A rewritten query is semantically equivalent to its original. This is enforced by means of strong conditions that the query has to fulfill.

Query optimizations

The rewriting of a query is performed via several independent optimizations. You can apply more than one of the following optimizations to a single query, and you can enable or disable each optimization independently:

swarm64da.enable_unnesting (boolean)

Enable the decorrelation of single aggregation subqueries.

The following queries are equivalent:

SELECT s.name FROM students s, exams e WHERE s.id = e.student_id AND
                      e.grade = (SELECT MIN(e2.grade) FROM exams e2 WHERE s.id =
                      e2.student_id);

SELECT s.name FROM students s, exams e, (SELECT e2.student_id AS id,
                      MIN(e2.grade) AS best FROM exams e2 GROUP BY e2.student_id) m WHERE
                      s.id=e.student_id AND m.id=s.id AND e.grade=m.best;

An equijoin executes faster than the original subquery that has the correlation s.id = e2.student_id. The default value is on.

swarm64da.enable_not_in_to_not_exists (boolean)

Enable the transformation of NOT INs into NOT EXISTS.

The following queries are equivalent, when the id and the output of the subquery each do not emit NULL values:

SELECT student FROM class WHERE id NOT IN (SELECT student_id FROM
                      exams);

SELECT student FROM class WHERE NOT EXISTS (SELECT 1 FROM exams WHERE
                      student_id = id);

A query with NOT EXISTS executes faster than a query with NOT IN. The default value is on.