Why is subquery so useful

WITH clause: performance impact

The non-recursive clause, views, and subqueries in the “derived tables” clause offer very similar functionality. The biggest difference is visibility. Hence, the principle of least surprise suggests that a change in shape - e.g. B. from a subquery to a query - should not have any side effects on the performance.

The following sections outline the most important optimizations that interact with the clause. Finally, an overview shows which databases apply these optimizations to clauses, views, and subqueries.

Predicate pushdown - pass filter down

Predicate pushdown is an optimization to apply filter conditions (predicates) as early as possible. Ideally, lines that are not required are not loaded at all.

The following example shows the predicate pushdown on a subquery in the clause:

When run in isolation, the subquery returns all the rows in the table. Even if there is an index on, it cannot be used because the filter on is not visible to the subquery.

Predicate Pushdown passes the filter down to the subquery so that the query looks de facto as follows:

In this example, of course, it's fair to say that the clause belongs in the subquery anyway. However, this argument cannot be used when accessing a view instead of a subquery.

Predicate pushdown has some logical restrictions. For example, it is not possible to extend join predicates further than the first join they concern. On the other hand, predicates can also be passed through and window functions if they only affect the group or partitioning key.

Predicate pushdown is especially important to enable better indexing.

Projection pushdown - hand down columns

Projection pushdown - passing down columns - discards unused columns as soon as possible. Ideally, they will not be charged at all.

The following example shows projection pushdown when accessing a view:

Note that no table columns are required.0 If the database passes down the information about which columns are required, it can avoid loading unneeded columns.

In the example above, the projection pushdown is particularly important when there is an index on the column. In this case, the query can be carried out simply by accessing the index (the so-called index-only scan). Access to the table itself can be omitted. This can make the query faster by factors. You can find out more about the index-only scan on Use The Index, Luke!

Eliminate sorting

By eliminating sorts, redundant sorts are avoided. Redudant sorts can arise, for example, from several clauses:

Of course, there is no need to sort the result twice. Even if the two clauses were different, only the outermost clause determines the order in the final result. With a few exceptions1, all but the outermost clause can be eliminated.

Redundant sorts are less obvious when they come from a combination of (sometimes sort-based) and join operations (sort-merge). In addition, indexes could return the rows in a useful order and sorting is no longer necessary.

In the following example, a database could sort the rows according to the and clauses - that is, according to - in order to resolve the Window function r.2 In this case, the query in the -clause already returns the rows according to the outer -clause. The outer sorting can thus be eliminated.

Note that the outer clause is still required. The clause only defines the order in which the window function is calculated. If the database calculates the window function differently - e.g. B. dividing the partitions into parallel processes - the outer sorting cannot be eliminated. If you omit the outer clause, the result in this case comes in an undefined order.

Product comparison

All tested databases optimize clauses as good (or bad) as they optimize views and subqueries.

PostgreSQL behaved differently before version 12 and optimized the queries separately from the main query.

Prior to version 5.7, MySQL did not optimize subqueries as well as Views.3

About the author

Markus Winand is the SQL Renaissance Ambassador on a mission to alert developers to the evolution of SQL in the 21st century. Markus can be hired as a trainer, speaker and consultant on winand.at.