DBT3 Запросы эталонных тестов

Известные факты о бенчмарке DBT-3 и его запросах

Q1

Простой запрос к одной таблице.

select
        l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price,
        avg(l_discount) as avg_disc,
        count(*) as count_order
from
        lineitem
where
        l_shipdate <= date_sub('1998-12-01', interval 79 day)
group by
        l_returnflag,
        l_linestatus
order by
        l_returnflag,
        l_linestatus;

План запроса:

+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+
|    1 | SIMPLE      | lineitem | ALL  | i_l_shipdate  | NULL | NULL    | NULL | 59711977 | Using where; Using temporary; Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+

• l_shipdate < date_sub('1998-12-01', interval 79 day) удовлетворяется 59 334 576 строками.
• В таблице всего 59 986 052 строки.
• Существует всего 4 различных значения (l_returnflag,l_linestatus). Это означает, что сортировка не важна, а временная таблица представляет собой очень маленькую кучевую таблицу.

Q2

Планы, начинающиеся с таблицы "part" ... - 8 сек на холодном кэше. масштаб=10 (масштаб=30 прогнозируемое время 1 мин)

Q3

select
       l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue,
       o_orderdate, o_shippriority
from 
      customer, 
      orders, 
      lineitem
where 
      c_mktsegment = 'BUILDING' and c_custkey = o_custkey
      and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15'
      and l_shipdate > date '1995-03-15'
group by l_orderkey, o_orderdate, o_shippriority
order by revenue desc, o_orderdate
limit 10;

По-видимому, есть улучшение в mysql-5.6: http://jorgenloland.blogspot.ru/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

(ускорение можно наблюдать только когда запрос имеет форму как выше (TODO: понять, откуда берутся разные формы запросов?))

EXPLAIN (масштаб=10):

+------+-------------+----------+--------+---------------------------------------------------------+---------+---------+----------------------------+----------+----------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                           | key     | key_len | ref                        | rows     | Extra                                        |
+------+-------------+----------+--------+---------------------------------------------------------+---------+---------+----------------------------+----------+----------------------------------------------+
|    1 | SIMPLE      | orders   | ALL    | NULL                                                    | NULL    | NULL    | NULL                       | 15115145 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | customer | eq_ref | PRIMARY                                                 | PRIMARY | 4       | dbt3sf10.orders.o_custkey  |        1 | Using where                                  |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4       | dbt3sf10.orders.o_orderkey |        2 | Using where                                  |
+------+-------------+----------+--------+---------------------------------------------------------+---------+---------+----------------------------+----------+----------------------------------------------+

+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                           | key            | key_len | ref                        | rows    | Extra                                                               |
+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+
|    1 | SIMPLE      | orders   | range  | PRIMARY,i_o_date_clerk                                  | i_o_date_clerk | 4       | NULL                       | 7557572 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | customer | eq_ref | PRIMARY                                                 | PRIMARY        | 4       | dbt3sf10.orders.o_custkey  |       1 | Using where                                                         |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity | PRIMARY        | 4       | dbt3sf10.orders.o_orderkey |       2 | Using where                                                         |
+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+

При статистических данных по 'building': мы получаем план customer, orders, lineitem. Он на 5% хуже. Похоже, других возможностей нет.

Q5

Пока ничего хорошего.

следите за условием "c_nationkey = s_nationkey". Это "побочное" условие (т. е. оно не из "естественных" отношений между таблицами). Неясно, приведёт ли учёт его селективности к чему-либо

Q8

Timour is analyzing this query.

Q9

Timour is analyzing this query.

+------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+----------+---------------------------------------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                                                          | key                 | key_len | ref                                              | rows | filtered | Extra                                                                     |
+------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+----------+---------------------------------------------------------------------------+
|    1 | SIMPLE      | nation   | ALL    | PRIMARY                                                                                | NULL                | NULL    | NULL                                             |   25 |   100.00 | Using temporary; Using filesort                                           |
|    1 | SIMPLE      | supplier | ref    | PRIMARY,i_s_nationkey                                                                  | i_s_nationkey       | 5       | dbt3.nation.n_nationkey                          | 4077 |   100.00 | Using index                                                               |
|    1 | SIMPLE      | partsupp | ref    | PRIMARY,i_ps_partkey,i_ps_suppkey                                                      | i_ps_suppkey        | 4       | dbt3.supplier.s_suppkey                          |   38 |   100.00 | Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan        |
|    1 | SIMPLE      | part     | eq_ref | PRIMARY                                                                                | PRIMARY             | 4       | dbt3.partsupp.ps_partkey                         |    1 |   100.00 | Using where; Using join buffer (incremental, BKA join); Key-ordered scan  |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity | i_l_suppkey_partkey | 10      | dbt3.partsupp.ps_partkey,dbt3.supplier.s_suppkey |    3 |   100.00 | Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan |
|    1 | SIMPLE      | orders   | eq_ref | PRIMARY                                                                                | PRIMARY             | 4       | dbt3.lineitem.l_orderkey                         |    1 |   100.00 | Using join buffer (incremental, BKA join); Key-ordered scan               |
+------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+----------+---------------------------------------------------------------------------+

Следите за условием "p_name like ...". А что, если мы принудительно сделаем таблицу part первой?

Q10

Q13

SergeyP is analyzing this query.

select
	c_count,
	count(*) as custdist
from
	(
		select
			c_custkey,
			count(o_orderkey) as c_count
		from
			customer left outer join orders on
				c_custkey = o_custkey
				and o_comment not like '%express%requests%'
		group by
			c_custkey
	) as c_orders
group by
	c_count
order by
	custdist desc,
	c_count desc;

Q15

SergeyP is analyzing this query.

Q17

Timour is analyzing this query.

• Q17 не может получить выгоду от MDEV-89, потому что предикат '<' зависит от обеих таблиц запроса. Независимо от порядка объединения, подзапрос должен быть присоединён к последней таблице в плане.

• Для 'lineitem' нет условий саргабельности, она больше, чем 'part', и селективность (p_brand = 'Brand#43' и p_container = 'WRAP DRUM') = 0,1%. Поэтому таблица part должна стоять первой в плане объединения.

• селективность (p_partkey = l_partkey) << селективность (l_quantity < (select ...)), поэтому дорогой подзапрос будет правильно размещён после дешёвого условия объединения.

• Одно из возможных улучшений для этого запроса — остановить выполнение подзапроса, как только выбранное выражение станет ложным. В этом случае останавливайтесь, когда (l_quantity >= 0.2 * avg(l_quantity)).

select sum(l_extendedprice) / 7.0 as avg_yearly
from lineitem, part
where
  p_partkey = l_partkey
  and p_brand = 'Brand#43'
  and p_container = 'WRAP DRUM'
  and l_quantity < (select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey);

+------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+---------+----------+------------------------------------------------------------------------------------------------+
| id   | select_type        | table    | type | possible_keys                   | key                 | key_len | ref                 | rows    | filtered | Extra                                                                                          |
+------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+---------+----------+------------------------------------------------------------------------------------------------+
|    1 | PRIMARY            | part     | ALL  | PRIMARY                         | NULL                | NULL    | NULL                | 6000000 |     1.39 | Using where                                                                                    |
|    1 | PRIMARY            | lineitem | ref  | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5       | dbt3.part.p_partkey |      29 |   100.00 | Using where; Subqueries: 2; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
|    2 | DEPENDENT SUBQUERY | lineitem | ref  | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5       | dbt3.part.p_partkey |      29 |   100.00 |                                                                                                |
+------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+---------+----------+------------------------------------------------------------------------------------------------+

Q20

• Этот запрос получит выгоду от MWL#253 (точные статистические данные индексов) и MDEV-83 (определение стоимости для продвижения подзапросов в объединённые таблицы).

explain extended
select sql_calc_found_rows 
  s_name, s_address
from 
  supplier, nation
where s_suppkey in (select ps_suppkey from partsupp
                    where ps_partkey in (select p_partkey from part where p_name like 'g%')
                          and ps_availqty >
                              (select 0.5 * sum(l_quantity)
                               from lineitem
                               where l_partkey = ps_partkey and l_suppkey = ps_suppkey
                                     and l_shipdate >= date('1993-01-01') and l_shipdate < date('1993-01-01') + interval '1' year ))
and s_nationkey = n_nationkey
and n_name = 'UNITED STATES'
order by s_name
limit 10;