[MDEV-21408] Performances testing for ORDER BY with LIMIT optimization Created: 2019-12-31  Updated: 2023-12-22

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Tests
Fix Version/s: 11.5

Type: Task Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: benchmarking

Issue Links:
Relates
relates to MDEV-8306 Complete cost-based optimization for ... Stalled

 Description   

This tasks relates to analyzing performance of ORDER BY with LIMIT queries after the optimization implemented in MDEV-8306.

First part of this would to run dbt3 queries(with scale factor=1) and change the queries(if needed) so that the new optimization is used.

 Comments   
Comment by Varun Gupta (Inactive) [ 2020-01-07 ]

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 |
 
+-------+------+------+---------+--------+---------------+

Comment by Varun Gupta (Inactive) [ 2020-01-09 ]

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

Comment by Varun Gupta (Inactive) [ 2020-01-09 ]

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.

Comment by Varun Gupta (Inactive) [ 2020-01-29 ] 

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 |
 
+-------+------+------+----------+--------+---------------+

Comment by Varun Gupta (Inactive) [ 2020-01-29 ]

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
 
        }
 
      }
 
    }
 
  }
 
} |

Comment by Varun Gupta (Inactive) [ 2020-01-29 ]

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
 
                }
 
              }
 
            }
 
          }
 
        ]
 
      }
 
    }
 
  }
 
} |

Comment by Varun Gupta (Inactive) [ 2020-02-02 ]

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.
Generated at Thu Feb 08 09:06:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.