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!
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.
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.
- Is there anything smaller than an electron?
- How could a single world economy function?
- Can we eat blue berry seeds?
- Instagram was created with Python only
- What are your favorite sitcoms
- Who are some famous self-taught
- How long is your hair 1
- What are the effects of HGH
- What exactly is lead generation marketing
- When did Amazon make its first profit?
- Why is RO water not good
- Why is skipping breakfast bad
- Why is Quora not popular in Malaysia
- What all photons can do or create
- NASA builds new spaceships
- How do you learn 3 languages easily
- Greet Qatar tourists
- If 18x 1 18 what is x
- In what year was Antarctica discovered?
- Which cities in the US have no racism?
- Which is healthier IQOS or E-Cigs
- Is web content actually stolen content
- Highly intelligent people hesitate
- Is Canada a happy country?
- Who makes the most reliable solar panels
- Is a mental illness treatable, but incurable
- What are the best examples of branding power
- What illness did Augustus suffer from?
- 15 of which number is 60
- Why do teachers think they know everything
- What are the problems with gun rights
- What can be causing my strange breath odor
- What is an inexplicable love