...

********* Some benchmarking ************* Execution times: - FirstMatch is ~15 sec - Materialization: ~5 sec.

With scalar-subquery predicate replaced with TRUE: - Materialization: ~0.20 sec. - FirstMatch: 0.19 sec.

Number of times the scalar subquery was executed: - DEP. SUBQUERY: ... 24055 times

- MATERIALIZATION: 9555 times

If we take the cost of scalar-subquery predicate evaluation into account, the difference should be around:

(24055 - 9555) * 5 = 72K

which is sufficient.

Then, we've tried to imitate the effects of "cost-based predicate pushdown" (mdev-83), by making the scalar subquery predicate depend on both tables part and partsupp. In order to do that, we've also had to convert the subquery (select p_partkey from part where p_name like 'forest%') into join. This change doesn't affect the optimizer because this subquery would be converted into an inner join anyway. == Modified query == explain select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp, part where ps_partkey = p_partkey and p_name like 'forest%' and ps_availqty + p_retailprice*0 > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; == Benchmarking == Number of times scalar-subquery was executed: Materialization: 9552 times (same as before) First-Match: 299 times (A LOT LESS!) Execution times: Materialization: 5.30 sec (same as before) First-Match: 1.72 sec (a lot less, used to be 15 sec! Now it beats materialization) BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog