[MDEV-21408] Performances testing for ORDER BY with LIMIT optimization - Jira

Varun Gupta (Inactive) added a comment - 2020-01-07 11:19 - edited

The queries for DBT3 are changed so that they use the ORDER BY with LIMIT optimization implemented in MDEV-8306.
This is the link to the queries https://github.com/varunraiko/mariadb-benchmarks/tree/master/dbt3_order_limit

The queries that are performing worse after the optimization are

query02
query03
query09
query15
query17
query19

here are the times

Without optimization

+-------+------+------+---------+--------+---------------+

| query | min  | max  | avg     | stddev | stddef [%avg] |

+-------+------+------+---------+--------+---------------+

|     2 |    1 |    2 |  1.3333 | 0.4714 |   35.35533907 |

|     3 |    6 |    7 |  6.3333 | 0.4714 |    7.44322928 |

|     9 |    4 |    7 |  5.3333 | 1.2472 |   23.38535867 |

|    15 |    3 |    4 |  3.6667 | 0.4714 |   12.85648693 |

|    17 |    0 |    1 |  0.6667 | 0.4714 |   70.71067819 |

|    19 |    0 |    1 |  0.3333 | 0.4714 |  141.42135638 |

+-------+------+------+---------+--------+---------------+

With optimization

+-------+------+------+---------+--------+---------------+

| query | min  | max  | avg     | stddev | stddef [%avg] |

+-------+------+------+---------+--------+---------------+

|     2 |    1 |    2 |  1.6667 | 0.4714 |   28.28427126 |

|     3 |   20 |   20 | 20.0000 | 0.0000 |    0.00000000 |

|     9 |   68 |   78 | 73.3333 | 4.1096 |    5.60401273 |

|    15 |   34 |   36 | 34.6667 | 0.9428 |    2.71964147 |

|    17 |    3 |    3 |  3.0000 | 0.0000 |    0.00000000 |

|    19 |    2 |    4 |  2.6667 | 0.9428 |   35.35533907 |

+-------+------+------+---------+--------+---------------+

Varun Gupta (Inactive) added a comment - 2020-01-07 11:19 - edited The queries for DBT3 are changed so that they use the ORDER BY with LIMIT optimization implemented in MDEV-8306 . This is the link to the queries https://github.com/varunraiko/mariadb-benchmarks/tree/master/dbt3_order_limit The queries that are performing worse after the optimization are query02 query03 query09 query15 query17 query19 here are the times Without optimization +-------+------+------+---------+--------+---------------+ | query | min | max | avg | stddev | stddef [%avg] | +-------+------+------+---------+--------+---------------+ | 2 | 1 | 2 | 1.3333 | 0.4714 | 35.35533907 | | 3 | 6 | 7 | 6.3333 | 0.4714 | 7.44322928 | | 9 | 4 | 7 | 5.3333 | 1.2472 | 23.38535867 | | 15 | 3 | 4 | 3.6667 | 0.4714 | 12.85648693 | | 17 | 0 | 1 | 0.6667 | 0.4714 | 70.71067819 | | 19 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | +-------+------+------+---------+--------+---------------+ With optimization +-------+------+------+---------+--------+---------------+ | query | min | max | avg | stddev | stddef [%avg] | +-------+------+------+---------+--------+---------------+ | 2 | 1 | 2 | 1.6667 | 0.4714 | 28.28427126 | | 3 | 20 | 20 | 20.0000 | 0.0000 | 0.00000000 | | 9 | 68 | 78 | 73.3333 | 4.1096 | 5.60401273 | | 15 | 34 | 36 | 34.6667 | 0.9428 | 2.71964147 | | 17 | 3 | 3 | 3.0000 | 0.0000 | 0.00000000 | | 19 | 2 | 4 | 2.6667 | 0.9428 | 35.35533907 | +-------+------+------+---------+--------+---------------+

Varun Gupta (Inactive) added a comment - 2020-01-09 06:42 - edited

For query17

select *

from

	lineitem,

	part

where

	p_partkey = l_partkey

	and p_brand = 'Brand#13' and p_container = 'SM CASE'

	and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey)

order by l_shipdate

limit 20;

FAST PLAN

+------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+--------+-----------+----------+------------+----------------------------------------------+

| id   | select_type        | table    | type | possible_keys                   | key                 | key_len | ref                 | rows   | r_rows    | filtered | r_filtered | Extra                                        |

+------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+--------+-----------+----------+------------+----------------------------------------------+

|    1 | PRIMARY            | part     | ALL  | PRIMARY                         | NULL                | NULL    | NULL                | 200000 | 200000.00 |     0.79 |       0.10 | Using where; Using temporary; Using filesort |

|    1 | PRIMARY            | lineitem | ref  | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5       | dbt3.part.p_partkey | 30     | 30.20     |   100.00 |       8.39 | Using where                                  |

|    2 | DEPENDENT SUBQUERY | lineitem | ref  | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5       | dbt3.part.p_partkey | 30     | 30.20     |   100.00 |     100.00 |                                              |

+------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+--------+-----------+----------+------------+----------------------------------------------+

SLOW PLAN

+------+--------------------+----------+--------+---------------------------------+---------------------+---------+-------------------------+------+-----------+----------+------------+-------------+

| id   | select_type        | table    | type   | possible_keys                   | key                 | key_len | ref                     | rows | r_rows    | filtered | r_filtered | Extra       |

+------+--------------------+----------+--------+---------------------------------+---------------------+---------+-------------------------+------+-----------+----------+------------+-------------+

|    1 | PRIMARY            | lineitem | index  | i_l_suppkey_partkey,i_l_partkey | i_l_shipdate        | 4       | NULL                    | 2520 | 340677.00 |   100.00 |     100.00 | Using where |

|    1 | PRIMARY            | part     | eq_ref | PRIMARY                         | PRIMARY             | 4       | dbt3.lineitem.l_partkey | 1    | 1.00      |     0.79 |       0.01 | Using where |

|    2 | DEPENDENT SUBQUERY | lineitem | ref    | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5       | dbt3.part.p_partkey     | 30   | 30.29     |   100.00 |     100.00 |             |

+------+--------------------+----------+--------+---------------------------------+---------------------+---------+-------------------------+------+-----------+----------+------------+-------------+

The reason for this happening is we don't have the selectivity for the predicate
involving the dependent subquery. The predicate is

l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey)

If I remove the predicate involving the DEPENDENT SUBQUERY, then the query performs better with the optimization

Varun Gupta (Inactive) added a comment - 2020-01-09 06:42 - edited For query17 select * from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#13' and p_container = 'SM CASE' and l_quantity < ( select 0.2 * avg (l_quantity) from lineitem where l_partkey = p_partkey) order by l_shipdate limit 20; FAST PLAN +------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+--------+-----------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+--------+-----------+----------+------------+----------------------------------------------+ | 1 | PRIMARY | part | ALL | PRIMARY | NULL | NULL | NULL | 200000 | 200000.00 | 0.79 | 0.10 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | lineitem | ref | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5 | dbt3.part.p_partkey | 30 | 30.20 | 100.00 | 8.39 | Using where | | 2 | DEPENDENT SUBQUERY | lineitem | ref | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5 | dbt3.part.p_partkey | 30 | 30.20 | 100.00 | 100.00 | | +------+--------------------+----------+------+---------------------------------+---------------------+---------+---------------------+--------+-----------+----------+------------+----------------------------------------------+ SLOW PLAN +------+--------------------+----------+--------+---------------------------------+---------------------+---------+-------------------------+------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+--------+---------------------------------+---------------------+---------+-------------------------+------+-----------+----------+------------+-------------+ | 1 | PRIMARY | lineitem | index | i_l_suppkey_partkey,i_l_partkey | i_l_shipdate | 4 | NULL | 2520 | 340677.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | part | eq_ref | PRIMARY | PRIMARY | 4 | dbt3.lineitem.l_partkey | 1 | 1.00 | 0.79 | 0.01 | Using where | | 2 | DEPENDENT SUBQUERY | lineitem | ref | i_l_suppkey_partkey,i_l_partkey | i_l_suppkey_partkey | 5 | dbt3.part.p_partkey | 30 | 30.29 | 100.00 | 100.00 | | +------+--------------------+----------+--------+---------------------------------+---------------------+---------+-------------------------+------+-----------+----------+------------+-------------+ The reason for this happening is we don't have the selectivity for the predicate involving the dependent subquery. The predicate is l_quantity < ( select 0.2 * avg (l_quantity) from lineitem where l_partkey = p_partkey) If I remove the predicate involving the DEPENDENT SUBQUERY, then the query performs better with the optimization

Varun Gupta (Inactive) added a comment - 2020-01-09 19:37

For query_09

select

	n_name,

	year(o_orderdate) as o_year,

	l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount

from

    part,

	supplier,

	lineitem,

	partsupp,

	orders,

	nation

where

	s_suppkey = l_suppkey

	and ps_suppkey = l_suppkey

	and ps_partkey = l_partkey

	and p_partkey = l_partkey

	and o_orderkey = l_orderkey

	and s_nationkey = n_nationkey

	and p_name like '%sky%'

order by

	n_name,

	o_year desc

limit 10;

FAST PLAN

+------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+--------+----------+------------+---------------------------------+

| id   | select_type | table    | type   | possible_keys                                                                          | key                 | key_len | ref                                              | rows | r_rows | filtered | r_filtered | Extra                           |

+------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+--------+----------+------------+---------------------------------+

|    1 | SIMPLE      | nation   | ALL    | PRIMARY                                                                                | NULL                | NULL    | NULL                                             | 25   | 25.00  |   100.00 |     100.00 | Using temporary; Using filesort |

|    1 | SIMPLE      | supplier | ref    | PRIMARY,i_s_nationkey                                                                  | i_s_nationkey       | 5       | dbt3.nation.n_nationkey                          | 400  | 400.00 |   100.00 |     100.00 | Using index                     |

|    1 | SIMPLE      | partsupp | ref    | PRIMARY,i_ps_partkey,i_ps_suppkey                                                      | i_ps_suppkey        | 4       | dbt3.supplier.s_suppkey                          | 80   | 80.00  |   100.00 |     100.00 |                                 |

|    1 | SIMPLE      | part     | eq_ref | PRIMARY                                                                                | PRIMARY             | 4       | dbt3.partsupp.ps_partkey                         | 1    | 1.00   |   100.00 |       4.34 | Using where                     |

|    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 | 7    | 7.52   |   100.00 |     100.00 |                                 |

|    1 | SIMPLE      | orders   | eq_ref | PRIMARY                                                                                | PRIMARY             | 4       | dbt3.lineitem.l_orderkey                         | 1    | 1.00   |   100.00 |     100.00 |                                 |

+------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+--------+----------+------------+---------------------------------+

SLOW PLAN

+------+-------------+-------------+--------+----------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+--------+----------+------------+----------------+

| id   | select_type | table       | type   | possible_keys                                                                          | key           | key_len | ref                                     | rows | r_rows | filtered | r_filtered | Extra          |

+------+-------------+-------------+--------+----------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+--------+----------+------------+----------------+

|    1 | SIMPLE      | nation      | ALL    | PRIMARY                                                                                | NULL          | NULL    | NULL                                    | 25   | 25.00  |   100.00 |     100.00 |                |

|    1 | SIMPLE      | supplier    | ref    | PRIMARY,i_s_nationkey                                                                  | i_s_nationkey | 5       | dbt3.nation.n_nationkey                 | 400  | 400.00 |   100.00 |     100.00 | Using index    |

|    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   | 5       | dbt3.supplier.s_suppkey                 | 600  | 600.12 |   100.00 |     100.00 |                |

|    1 | SIMPLE      | orders      | eq_ref | PRIMARY                                                                                | PRIMARY       | 4       | dbt3.lineitem.l_orderkey                | 1    | 1.00   |   100.00 |     100.00 |                |

|    1 | SIMPLE      | <sort-nest> | ALL    | NULL                                                                                   | NULL          | NULL    | NULL                                    | 9    | 180.00 |   100.00 |     100.00 | Using filesort |

|    1 | SIMPLE      | partsupp    | eq_ref | PRIMARY,i_ps_partkey,i_ps_suppkey                                                      | PRIMARY       | 8       | sort-nest.l_partkey,sort-nest.s_suppkey | 1    | 1.00   |   100.00 |     100.00 |                |

|    1 | SIMPLE      | part        | eq_ref | PRIMARY                                                                                | PRIMARY       | 4       | sort-nest.l_partkey                     | 1    | 1.00   |   100.00 |       5.56 | Using where    |

+------+-------------+-------------+--------+----------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+--------+----------+------------+----------------+

The issue here again is that for predicate

 p_name like '%sky%'

we don't have any selectivity.

Varun Gupta (Inactive) added a comment - 2020-01-09 19:37 For query_09 select n_name, year (o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%sky%' order by n_name, o_year desc limit 10; FAST PLAN +------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+--------+----------+------------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+--------+----------+------------+---------------------------------+ | 1 | SIMPLE | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 25.00 | 100.00 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3.nation.n_nationkey | 400 | 400.00 | 100.00 | 100.00 | Using index | | 1 | SIMPLE | partsupp | ref | PRIMARY,i_ps_partkey,i_ps_suppkey | i_ps_suppkey | 4 | dbt3.supplier.s_suppkey | 80 | 80.00 | 100.00 | 100.00 | | | 1 | SIMPLE | part | eq_ref | PRIMARY | PRIMARY | 4 | dbt3.partsupp.ps_partkey | 1 | 1.00 | 100.00 | 4.34 | Using where | | 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 | 7 | 7.52 | 100.00 | 100.00 | | | 1 | SIMPLE | orders | eq_ref | PRIMARY | PRIMARY | 4 | dbt3.lineitem.l_orderkey | 1 | 1.00 | 100.00 | 100.00 | | +------+-------------+----------+--------+----------------------------------------------------------------------------------------+---------------------+---------+--------------------------------------------------+------+--------+----------+------------+---------------------------------+ SLOW PLAN +------+-------------+-------------+--------+----------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+--------+----------+------------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------------+--------+----------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+--------+----------+------------+----------------+ | 1 | SIMPLE | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 25.00 | 100.00 | 100.00 | | | 1 | SIMPLE | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3.nation.n_nationkey | 400 | 400.00 | 100.00 | 100.00 | Using index | | 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 | 5 | dbt3.supplier.s_suppkey | 600 | 600.12 | 100.00 | 100.00 | | | 1 | SIMPLE | orders | eq_ref | PRIMARY | PRIMARY | 4 | dbt3.lineitem.l_orderkey | 1 | 1.00 | 100.00 | 100.00 | | | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL | NULL | 9 | 180.00 | 100.00 | 100.00 | Using filesort | | 1 | SIMPLE | partsupp | eq_ref | PRIMARY,i_ps_partkey,i_ps_suppkey | PRIMARY | 8 | sort-nest.l_partkey,sort-nest.s_suppkey | 1 | 1.00 | 100.00 | 100.00 | | | 1 | SIMPLE | part | eq_ref | PRIMARY | PRIMARY | 4 | sort-nest.l_partkey | 1 | 1.00 | 100.00 | 5.56 | Using where | +------+-------------+-------------+--------+----------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+--------+----------+------------+----------------+ The issue here again is that for predicate p_name like '%sky%' we don't have any selectivity.

Varun Gupta (Inactive) added a comment - 2020-01-29 19:16

Without optimization

+-------+------+------+---------+--------+---------------+

| query | min  | max  | avg     | stddev | stddef [%avg] |

+-------+------+------+---------+--------+---------------+

|     1 |   40 |   48 | 43.0000 | 3.5590 |    8.27680485 |

|     2 |    0 |    1 |  0.3333 | 0.4714 |  141.42135638 |

|     3 |    9 |   10 |  9.3333 | 0.4714 |    5.05076272 |

|     4 |    0 |    1 |  0.3333 | 0.4714 |  141.42135638 |

|     5 |    3 |    3 |  3.0000 | 0.0000 |    0.00000000 |

|     6 |    0 |    0 |  0.0000 | 0.0000 |          NULL |

|     7 |    2 |    3 |  2.6667 | 0.4714 |   17.67766953 |

|     8 |    5 |    5 |  5.0000 | 0.0000 |    0.00000000 |

|     9 |    6 |    9 |  7.3333 | 1.2472 |   17.00753358 |

|    10 |    1 |    2 |  1.6667 | 0.4714 |   28.28427126 |

|    11 |    0 |    1 |  0.3333 | 0.4714 |  141.42135638 |

|    12 |    6 |    7 |  6.3333 | 0.4714 |    7.44322928 |

|    13 |   10 |   15 | 11.6667 | 2.3570 |   20.20305089 |

|    14 |    0 |    0 |  0.0000 | 0.0000 |          NULL |

|    15 |    3 |    4 |  3.6667 | 0.4714 |   12.85648693 |

|    16 |    1 |    1 |  1.0000 | 0.0000 |    0.00000000 |

|    17 |    0 |    0 |  0.0000 | 0.0000 |          NULL |

|    18 |    8 |   11 |  9.0000 | 1.4142 |   15.71348403 |

|    19 |    0 |    1 |  0.3333 | 0.4714 |  141.42135638 |

|    20 |    1 |    1 |  1.0000 | 0.0000 |    0.00000000 |

|    21 |    3 |    4 |  3.6667 | 0.4714 |   12.85648693 |

|    22 |    0 |    1 |  0.6667 | 0.4714 |   70.71067819 |

+-------+------+------+---------+--------+---------------+

With optimization

+-------+------+------+----------+--------+---------------+

| query | min  | max  | avg      | stddev | stddef [%avg] |

+-------+------+------+----------+--------+---------------+

|     1 |   41 |   44 |  42.0000 | 1.4142 |    3.36717515 |

|     2 |    0 |    1 |   0.3333 | 0.4714 |  141.42135638 |

|     3 |    9 |   10 |   9.3333 | 0.4714 |    5.05076272 |

|     4 |    0 |    1 |   0.3333 | 0.4714 |  141.42135638 |

|     5 |    0 |    0 |   0.0000 | 0.0000 |          NULL |

|     6 |    0 |    0 |   0.0000 | 0.0000 |          NULL |

|     7 |    6 |    7 |   6.3333 | 0.4714 |    7.44322928 |

|     8 |    1 |    2 |   1.3333 | 0.4714 |   35.35533907 |

|     9 |   96 |  110 | 102.0000 | 5.8878 |    5.77239272 |

|    10 |    2 |    3 |   2.3333 | 0.4714 |   20.20305089 |

|    11 |    0 |    0 |   0.0000 | 0.0000 |          NULL |

|    12 |    8 |   10 |   9.0000 | 0.8165 |    9.07218423 |

|    13 |    9 |   13 |  11.0000 | 1.6330 |   14.84539238 |

|    14 |    0 |    0 |   0.0000 | 0.0000 |          NULL |

|    15 |   43 |   45 |  44.0000 | 0.8165 |    1.85567405 |

|    16 |    0 |    1 |   0.6667 | 0.4714 |   70.71067819 |

|    17 |    4 |    6 |   4.6667 | 0.9428 |   20.20305089 |

|    18 |    8 |   10 |   8.6667 | 0.9428 |   10.87856587 |

|    19 |    0 |    1 |   0.6667 | 0.4714 |   70.71067819 |

|    20 |    0 |    1 |   0.6667 | 0.4714 |   70.71067819 |

|    21 |    0 |    1 |   0.3333 | 0.4714 |  141.42135638 |

|    22 |    0 |    0 |   0.0000 | 0.0000 |          NULL |

+-------+------+------+----------+--------+---------------+

Varun Gupta (Inactive) added a comment - 2020-01-29 19:16 Without optimization +-------+------+------+---------+--------+---------------+ | query | min | max | avg | stddev | stddef [%avg] | +-------+------+------+---------+--------+---------------+ | 1 | 40 | 48 | 43.0000 | 3.5590 | 8.27680485 | | 2 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 3 | 9 | 10 | 9.3333 | 0.4714 | 5.05076272 | | 4 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 5 | 3 | 3 | 3.0000 | 0.0000 | 0.00000000 | | 6 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 7 | 2 | 3 | 2.6667 | 0.4714 | 17.67766953 | | 8 | 5 | 5 | 5.0000 | 0.0000 | 0.00000000 | | 9 | 6 | 9 | 7.3333 | 1.2472 | 17.00753358 | | 10 | 1 | 2 | 1.6667 | 0.4714 | 28.28427126 | | 11 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 12 | 6 | 7 | 6.3333 | 0.4714 | 7.44322928 | | 13 | 10 | 15 | 11.6667 | 2.3570 | 20.20305089 | | 14 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 15 | 3 | 4 | 3.6667 | 0.4714 | 12.85648693 | | 16 | 1 | 1 | 1.0000 | 0.0000 | 0.00000000 | | 17 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 18 | 8 | 11 | 9.0000 | 1.4142 | 15.71348403 | | 19 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 20 | 1 | 1 | 1.0000 | 0.0000 | 0.00000000 | | 21 | 3 | 4 | 3.6667 | 0.4714 | 12.85648693 | | 22 | 0 | 1 | 0.6667 | 0.4714 | 70.71067819 | +-------+------+------+---------+--------+---------------+ With optimization +-------+------+------+----------+--------+---------------+ | query | min | max | avg | stddev | stddef [%avg] | +-------+------+------+----------+--------+---------------+ | 1 | 41 | 44 | 42.0000 | 1.4142 | 3.36717515 | | 2 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 3 | 9 | 10 | 9.3333 | 0.4714 | 5.05076272 | | 4 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 5 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 6 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 7 | 6 | 7 | 6.3333 | 0.4714 | 7.44322928 | | 8 | 1 | 2 | 1.3333 | 0.4714 | 35.35533907 | | 9 | 96 | 110 | 102.0000 | 5.8878 | 5.77239272 | | 10 | 2 | 3 | 2.3333 | 0.4714 | 20.20305089 | | 11 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 12 | 8 | 10 | 9.0000 | 0.8165 | 9.07218423 | | 13 | 9 | 13 | 11.0000 | 1.6330 | 14.84539238 | | 14 | 0 | 0 | 0.0000 | 0.0000 | NULL | | 15 | 43 | 45 | 44.0000 | 0.8165 | 1.85567405 | | 16 | 0 | 1 | 0.6667 | 0.4714 | 70.71067819 | | 17 | 4 | 6 | 4.6667 | 0.9428 | 20.20305089 | | 18 | 8 | 10 | 8.6667 | 0.9428 | 10.87856587 | | 19 | 0 | 1 | 0.6667 | 0.4714 | 70.71067819 | | 20 | 0 | 1 | 0.6667 | 0.4714 | 70.71067819 | | 21 | 0 | 1 | 0.3333 | 0.4714 | 141.42135638 | | 22 | 0 | 0 | 0.0000 | 0.0000 | NULL | +-------+------+------+----------+--------+---------------+

Varun Gupta (Inactive) added a comment - 2020-01-29 19:34

ANALYZE FORMAT=JSOn for Query 9:

With optimization

| {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 129634,

    "table": {

      "table_name": "nation",

      "access_type": "ALL",

      "r_loops": 1,

      "rows": 25,

      "r_rows": 25,

      "r_table_time_ms": 0.1777,

      "r_other_time_ms": 0.1815,

      "filtered": 100,

      "r_filtered": 100

},

    "table": {

      "table_name": "supplier",

      "access_type": "ref",

      "key": "i_s_nationkey",

      "key_length": "5",

      "used_key_parts": ["s_nationkey"],

      "ref": ["dbt3.nation.n_nationkey"],

      "r_loops": 25,

      "rows": 400,

      "r_rows": 400,

      "r_table_time_ms": 22.13,

      "r_other_time_ms": 14.66,

      "filtered": 100,

      "r_filtered": 100,

      "using_index": true

},

    "table": {

      "table_name": "lineitem",

      "access_type": "ref",

      "key": "i_l_suppkey",

      "key_length": "5",

      "used_key_parts": ["l_suppkey"],

      "ref": ["dbt3.supplier.s_suppkey"],

      "r_loops": 10000,

      "rows": 600,

      "r_rows": 600.12,

      "r_table_time_ms": 41852,

      "r_other_time_ms": 2737,

      "filtered": 100,

      "r_filtered": 100

},

    "table": {

      "table_name": "orders",

      "access_type": "eq_ref",

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["o_orderkey"],

      "ref": ["dbt3.lineitem.l_orderkey"],

      "r_loops": 6001215,

      "rows": 1,

      "r_rows": 1,

      "r_table_time_ms": 39444,

      "r_other_time_ms": 27219,

      "filtered": 100,

      "r_filtered": 100

},

    "read_sorted_file": {

      "r_rows": 180,

      "filesort": {

        "sort_key": "`sort-nest`.n_name, year(`sort-nest`.o_orderDATE) desc",

        "r_loops": 1,

        "r_total_time_ms": 17924,

        "r_used_priority_queue": false,

        "r_output_rows": 6001215,

        "r_sort_passes": 59,

        "r_buffer_size": "2047Kb",

        "table": {

          "table_name": "<sort-nest>",

          "access_type": "ALL",

          "r_loops": 1,

          "rows": 9,

          "r_rows": 6e6,

          "r_table_time_ms": 105.61,

          "r_other_time_ms": 281.6,

          "filtered": 100,

          "r_filtered": 100

},

    "table": {

      "table_name": "partsupp",

      "access_type": "eq_ref",

      "key": "PRIMARY",

      "key_length": "8",

      "used_key_parts": ["ps_partkey", "ps_suppkey"],

      "ref": ["sort-nest.l_partkey", "sort-nest.s_suppkey"],

      "r_loops": 180,

      "rows": 1,

      "r_rows": 1,

      "r_table_time_ms": 4.6553,

      "r_other_time_ms": 0.162,

      "filtered": 100,

      "r_filtered": 100

},

    "table": {

      "table_name": "part",

      "access_type": "eq_ref",

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["p_partkey"],

      "ref": ["sort-nest.l_partkey"],

      "r_loops": 180,

      "rows": 1,

      "r_rows": 1,

      "r_table_time_ms": 4.5496,

      "r_other_time_ms": 24.368,

      "filtered": 100,

      "r_filtered": 5.5556,

      "attached_condition": "part.p_name like '%sky%'"

} |

Without optimzation

| {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 21339,

    "filesort": {

      "sort_key": "nation.n_name, year(orders.o_orderDATE) desc",

      "r_loops": 1,

      "r_total_time_ms": 81.132,

      "r_limit": 10,

      "r_used_priority_queue": true,

      "r_output_rows": 11,

      "temporary_table": {

        "table": {

          "table_name": "nation",

          "access_type": "ALL",

          "r_loops": 1,

          "rows": 25,

          "r_rows": 25,

          "r_table_time_ms": 0.0773,

          "r_other_time_ms": 0.0673,

          "filtered": 100,

          "r_filtered": 100

},

        "table": {

          "table_name": "supplier",

          "access_type": "ref",

          "key": "i_s_nationkey",

          "key_length": "5",

          "used_key_parts": ["s_nationkey"],

          "ref": ["dbt3.nation.n_nationkey"],

          "r_loops": 25,

          "rows": 400,

          "r_rows": 400,

          "r_table_time_ms": 23.075,

          "r_other_time_ms": 9.0392,

          "filtered": 100,

          "r_filtered": 100,

          "using_index": true

},

        "table": {

          "table_name": "partsupp",

          "access_type": "ref",

          "key": "i_ps_suppkey",

          "key_length": "4",

          "used_key_parts": ["ps_suppkey"],

          "ref": ["dbt3.supplier.s_suppkey"],

          "r_loops": 10000,

          "rows": 80,

          "r_rows": 80,

          "r_table_time_ms": 8040.9,

          "r_other_time_ms": 323.78,

          "filtered": 100,

          "r_filtered": 100

},

        "table": {

          "table_name": "part",

          "access_type": "eq_ref",

          "key": "PRIMARY",

          "key_length": "4",

          "used_key_parts": ["p_partkey"],

          "ref": ["dbt3.partsupp.ps_partkey"],

          "r_loops": 800000,

          "rows": 1,

          "r_rows": 1,

          "r_table_time_ms": 5193.8,

          "r_other_time_ms": 489.08,

          "filtered": 100,

          "r_filtered": 4.3395,

          "attached_condition": "part.p_name like '%sky%'"

},

        "table": {

          "table_name": "lineitem",

          "access_type": "ref",

          "key": "i_l_suppkey_partkey",

          "key_length": "10",

          "used_key_parts": ["l_partkey", "l_suppkey"],

          "ref": ["dbt3.partsupp.ps_partkey", "dbt3.supplier.s_suppkey"],

          "r_loops": 34716,

          "rows": 7,

          "r_rows": 7.5199,

          "r_table_time_ms": 4396,

          "r_other_time_ms": 154.21,

          "filtered": 100,

          "r_filtered": 100

},

        "table": {

          "table_name": "orders",

          "access_type": "eq_ref",

          "key": "PRIMARY",

          "key_length": "4",

          "used_key_parts": ["o_orderkey"],

          "ref": ["dbt3.lineitem.l_orderkey"],

          "r_loops": 261060,

          "rows": 1,

          "r_rows": 1,

          "r_table_time_ms": 2061.8,

          "r_other_time_ms": 543.06,

          "filtered": 100,

          "r_filtered": 100

} |

Varun Gupta (Inactive) added a comment - 2020-01-29 19:34 ANALYZE FORMAT=JSOn for Query 9: With optimization | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 129634, "table": { "table_name": "nation", "access_type": "ALL", "r_loops": 1, "rows": 25, "r_rows": 25, "r_table_time_ms": 0.1777, "r_other_time_ms": 0.1815, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "supplier", "access_type": "ref", "key": "i_s_nationkey", "key_length": "5", "used_key_parts": ["s_nationkey"], "ref": ["dbt3.nation.n_nationkey"], "r_loops": 25, "rows": 400, "r_rows": 400, "r_table_time_ms": 22.13, "r_other_time_ms": 14.66, "filtered": 100, "r_filtered": 100, "using_index": true }, "table": { "table_name": "lineitem", "access_type": "ref", "key": "i_l_suppkey", "key_length": "5", "used_key_parts": ["l_suppkey"], "ref": ["dbt3.supplier.s_suppkey"], "r_loops": 10000, "rows": 600, "r_rows": 600.12, "r_table_time_ms": 41852, "r_other_time_ms": 2737, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "orders", "access_type": "eq_ref", "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3.lineitem.l_orderkey"], "r_loops": 6001215, "rows": 1, "r_rows": 1, "r_table_time_ms": 39444, "r_other_time_ms": 27219, "filtered": 100, "r_filtered": 100 }, "read_sorted_file": { "r_rows": 180, "filesort": { "sort_key": "`sort-nest`.n_name, year(`sort-nest`.o_orderDATE) desc", "r_loops": 1, "r_total_time_ms": 17924, "r_used_priority_queue": false, "r_output_rows": 6001215, "r_sort_passes": 59, "r_buffer_size": "2047Kb", "table": { "table_name": "<sort-nest>", "access_type": "ALL", "r_loops": 1, "rows": 9, "r_rows": 6e6, "r_table_time_ms": 105.61, "r_other_time_ms": 281.6, "filtered": 100, "r_filtered": 100 } } }, "table": { "table_name": "partsupp", "access_type": "eq_ref", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["ps_partkey", "ps_suppkey"], "ref": ["sort-nest.l_partkey", "sort-nest.s_suppkey"], "r_loops": 180, "rows": 1, "r_rows": 1, "r_table_time_ms": 4.6553, "r_other_time_ms": 0.162, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "part", "access_type": "eq_ref", "key": "PRIMARY", "key_length": "4", "used_key_parts": ["p_partkey"], "ref": ["sort-nest.l_partkey"], "r_loops": 180, "rows": 1, "r_rows": 1, "r_table_time_ms": 4.5496, "r_other_time_ms": 24.368, "filtered": 100, "r_filtered": 5.5556, "attached_condition": "part.p_name like '%sky%'" } } } | Without optimzation | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 21339, "filesort": { "sort_key": "nation.n_name, year(orders.o_orderDATE) desc", "r_loops": 1, "r_total_time_ms": 81.132, "r_limit": 10, "r_used_priority_queue": true, "r_output_rows": 11, "temporary_table": { "table": { "table_name": "nation", "access_type": "ALL", "r_loops": 1, "rows": 25, "r_rows": 25, "r_table_time_ms": 0.0773, "r_other_time_ms": 0.0673, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "supplier", "access_type": "ref", "key": "i_s_nationkey", "key_length": "5", "used_key_parts": ["s_nationkey"], "ref": ["dbt3.nation.n_nationkey"], "r_loops": 25, "rows": 400, "r_rows": 400, "r_table_time_ms": 23.075, "r_other_time_ms": 9.0392, "filtered": 100, "r_filtered": 100, "using_index": true }, "table": { "table_name": "partsupp", "access_type": "ref", "key": "i_ps_suppkey", "key_length": "4", "used_key_parts": ["ps_suppkey"], "ref": ["dbt3.supplier.s_suppkey"], "r_loops": 10000, "rows": 80, "r_rows": 80, "r_table_time_ms": 8040.9, "r_other_time_ms": 323.78, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "part", "access_type": "eq_ref", "key": "PRIMARY", "key_length": "4", "used_key_parts": ["p_partkey"], "ref": ["dbt3.partsupp.ps_partkey"], "r_loops": 800000, "rows": 1, "r_rows": 1, "r_table_time_ms": 5193.8, "r_other_time_ms": 489.08, "filtered": 100, "r_filtered": 4.3395, "attached_condition": "part.p_name like '%sky%'" }, "table": { "table_name": "lineitem", "access_type": "ref", "key": "i_l_suppkey_partkey", "key_length": "10", "used_key_parts": ["l_partkey", "l_suppkey"], "ref": ["dbt3.partsupp.ps_partkey", "dbt3.supplier.s_suppkey"], "r_loops": 34716, "rows": 7, "r_rows": 7.5199, "r_table_time_ms": 4396, "r_other_time_ms": 154.21, "filtered": 100, "r_filtered": 100 }, "table": { "table_name": "orders", "access_type": "eq_ref", "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3.lineitem.l_orderkey"], "r_loops": 261060, "rows": 1, "r_rows": 1, "r_table_time_ms": 2061.8, "r_other_time_ms": 543.06, "filtered": 100, "r_filtered": 100 } } } } } |

Varun Gupta (Inactive) added a comment - 2020-01-29 19:36

ANALYZE FORMAT=JSON for query 17

With optimzation

| {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 5528.3,

    "table": {

      "table_name": "lineitem",

      "access_type": "index",

      "key": "i_l_shipdate",

      "key_length": "4",

      "used_key_parts": ["l_shipDATE"],

      "r_loops": 1,

      "rows": 2520,

      "r_rows": 340677,

      "r_table_time_ms": 2868.2,

      "r_other_time_ms": 149.59,

      "filtered": 100,

      "r_filtered": 100,

      "attached_condition": "lineitem.l_partkey is not null"

},

    "table": {

      "table_name": "part",

      "access_type": "eq_ref",

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["p_partkey"],

      "ref": ["dbt3.lineitem.l_partkey"],

      "r_loops": 340677,

      "rows": 1,

      "r_rows": 1,

      "r_table_time_ms": 2298.4,

      "r_other_time_ms": 168.03,

      "filtered": 0.7935,

      "r_filtered": 0.0059,

      "attached_condition": "part.p_brand = 'Brand#13' and part.p_container = 'SM CASE' and lineitem.l_quantity < (subquery#2)"

},

    "subqueries": [

        "expression_cache": {

          "r_loops": 336,

          "r_hit_ratio": 52.381,

          "query_block": {

            "select_id": 2,

            "r_loops": 160,

            "r_total_time_ms": 42.669,

            "table": {

              "table_name": "lineitem",

              "access_type": "ref",

              "key": "i_l_suppkey_partkey",

              "key_length": "5",

              "used_key_parts": ["l_partkey"],

              "ref": ["dbt3.part.p_partkey"],

              "r_loops": 160,

              "rows": 30,

              "r_rows": 30.294,

              "r_table_time_ms": 38.077,

              "r_other_time_ms": 2.7502,

              "filtered": 100,

              "r_filtered": 100

} |

Without optimzation

| {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 413.24,

    "filesort": {

      "sort_key": "lineitem.l_shipDATE",

      "r_loops": 1,

      "r_total_time_ms": 0.1829,

      "r_limit": 20,

      "r_used_priority_queue": true,

      "r_output_rows": 21,

      "temporary_table": {

        "table": {

          "table_name": "part",

          "access_type": "ALL",

          "r_loops": 1,

          "rows": 200000,

          "r_rows": 200000,

          "r_table_time_ms": 256.17,

          "r_other_time_ms": 37.802,

          "filtered": 0.7935,

          "r_filtered": 0.0995,

          "attached_condition": "part.p_brand = 'Brand#13' and part.p_container = 'SM CASE'"

},

        "table": {

          "table_name": "lineitem",

          "access_type": "ref",

          "key": "i_l_suppkey_partkey",

          "key_length": "5",

          "used_key_parts": ["l_partkey"],

          "ref": ["dbt3.part.p_partkey"],

          "r_loops": 199,

          "rows": 30,

          "r_rows": 30.201,

          "r_table_time_ms": 68.715,

          "r_other_time_ms": 5.7547,

          "filtered": 100,

          "r_filtered": 8.386,

          "attached_condition": "lineitem.l_quantity < (subquery#2)"

},

        "subqueries": [

            "expression_cache": {

              "r_loops": 6010,

              "r_hit_ratio": 96.689,

              "query_block": {

                "select_id": 2,

                "r_loops": 199,

                "r_total_time_ms": 43.679,

                "table": {

                  "table_name": "lineitem",

                  "access_type": "ref",

                  "key": "i_l_suppkey_partkey",

                  "key_length": "5",

                  "used_key_parts": ["l_partkey"],

                  "ref": ["dbt3.part.p_partkey"],

                  "r_loops": 199,

                  "rows": 30,

                  "r_rows": 30.201,

                  "r_table_time_ms": 39.952,

                  "r_other_time_ms": 2.3364,

                  "filtered": 100,

                  "r_filtered": 100

} |

Varun Gupta (Inactive) added a comment - 2020-01-29 19:36 ANALYZE FORMAT=JSON for query 17 With optimzation | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 5528.3, "table": { "table_name": "lineitem", "access_type": "index", "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "r_loops": 1, "rows": 2520, "r_rows": 340677, "r_table_time_ms": 2868.2, "r_other_time_ms": 149.59, "filtered": 100, "r_filtered": 100, "attached_condition": "lineitem.l_partkey is not null" }, "table": { "table_name": "part", "access_type": "eq_ref", "key": "PRIMARY", "key_length": "4", "used_key_parts": ["p_partkey"], "ref": ["dbt3.lineitem.l_partkey"], "r_loops": 340677, "rows": 1, "r_rows": 1, "r_table_time_ms": 2298.4, "r_other_time_ms": 168.03, "filtered": 0.7935, "r_filtered": 0.0059, "attached_condition": "part.p_brand = 'Brand#13' and part.p_container = 'SM CASE' and lineitem.l_quantity < (subquery#2)" }, "subqueries": [ { "expression_cache": { "r_loops": 336, "r_hit_ratio": 52.381, "query_block": { "select_id": 2, "r_loops": 160, "r_total_time_ms": 42.669, "table": { "table_name": "lineitem", "access_type": "ref", "key": "i_l_suppkey_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3.part.p_partkey"], "r_loops": 160, "rows": 30, "r_rows": 30.294, "r_table_time_ms": 38.077, "r_other_time_ms": 2.7502, "filtered": 100, "r_filtered": 100 } } } } ] } } | Without optimzation | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 413.24, "filesort": { "sort_key": "lineitem.l_shipDATE", "r_loops": 1, "r_total_time_ms": 0.1829, "r_limit": 20, "r_used_priority_queue": true, "r_output_rows": 21, "temporary_table": { "table": { "table_name": "part", "access_type": "ALL", "r_loops": 1, "rows": 200000, "r_rows": 200000, "r_table_time_ms": 256.17, "r_other_time_ms": 37.802, "filtered": 0.7935, "r_filtered": 0.0995, "attached_condition": "part.p_brand = 'Brand#13' and part.p_container = 'SM CASE'" }, "table": { "table_name": "lineitem", "access_type": "ref", "key": "i_l_suppkey_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3.part.p_partkey"], "r_loops": 199, "rows": 30, "r_rows": 30.201, "r_table_time_ms": 68.715, "r_other_time_ms": 5.7547, "filtered": 100, "r_filtered": 8.386, "attached_condition": "lineitem.l_quantity < (subquery#2)" }, "subqueries": [ { "expression_cache": { "r_loops": 6010, "r_hit_ratio": 96.689, "query_block": { "select_id": 2, "r_loops": 199, "r_total_time_ms": 43.679, "table": { "table_name": "lineitem", "access_type": "ref", "key": "i_l_suppkey_partkey", "key_length": "5", "used_key_parts": ["l_partkey"], "ref": ["dbt3.part.p_partkey"], "r_loops": 199, "rows": 30, "r_rows": 30.201, "r_table_time_ms": 39.952, "r_other_time_ms": 2.3364, "filtered": 100, "r_filtered": 100 } } } } ] } } } } |

Varun Gupta (Inactive) added a comment - 2020-02-02 09:23

After having discussion with serg and igor, it was suggested to make the ORDER BY LIMIT optimization conservative in nature. This means that we should only be using the optimization if we can guarantee that the optimization would improve the performance of ORDER BY LIMIT queries.

A suggestion was to allow the optimization only when selectivities of all the predicates is provided to the optimizer. If that is not the case take a conservative approach and don't enable the optimization.

Varun Gupta (Inactive) added a comment - 2020-02-02 09:23 After having discussion with serg and igor , it was suggested to make the ORDER BY LIMIT optimization conservative in nature. This means that we should only be using the optimization if we can guarantee that the optimization would improve the performance of ORDER BY LIMIT queries. A suggestion was to allow the optimization only when selectivities of all the predicates is provided to the optimizer. If that is not the case take a conservative approach and don't enable the optimization.

MariaDB Server

Performances testing for ORDER BY with LIMIT optimization

Details

Description

Attachments

Issue Links

Activity

For query17

For query_09

ANALYZE FORMAT=JSOn for Query 9:

People

Dates

Git Integration