Learning PostgreSQL 11 - Third Edition

Reusing SQL code with CTE

When the execution of a subquery takes a lot of time, and the subquery is used in the whole SQL statement more than once, it makes sense to put it into a WITH clause to reuse its results. This makes the query faster because PostgreSQL executes the subqueries from the WITH clause only once, caches the results in memory or on disk—depending on their size—and then reuses them.

For example, let's take the car_portal database. Suppose it's required to find newer car models. This would require it to calculate the average age of the cars of each model and then select the models with an average age lower than the average age of all the models.

This can be done in the following way:

car_portal=> SELECT make, model, avg_age ...