In an earlier blog post, I managed to confuse myself as to
when a subquery was executed. It is not very clear from the
output of EXPLAIN where the execution of a subquery takes place.
Let's take a look at the following example query (Query 17 in the
DBT-3 benchmark):
select sum(l_extendedprice) / 7.0 as avg_yearly
from lineitem, part
where p_partkey = l_partkey
and p_brand = 'Brand#33' and p_container = 'LG CAN'
and l_quantity < (
select 0.2 * avg(l_quantity)
from lineitem
where l_partkey = p_partkey
);
If you run EXPLAIN on this query, you will see the following
execution plan:
+----+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+---------+-------------+[Read more]
| id | select_type …
