maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

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