[Maria-developers] Cost analysis: checking how fast cost-based predicate pushdown can be

9 Aug 2012

      ...
...
********* 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