10.11-selectivity-analyze.txt

+------+-------------+----------+--------+-----------------------------------+---------------+---------+---------------------------+-------+----------+----------+------------+----------------------------------------------+
| id   | select_type | table    | type   | possible_keys                     | key           | key_len | ref                       | rows  | r_rows   | filtered | r_filtered | Extra                                        |
+------+-------------+----------+--------+-----------------------------------+---------------+---------+---------------------------+-------+----------+----------+------------+----------------------------------------------+
|    1 | SIMPLE      | region   | ALL    | PRIMARY                           | NULL          | NULL    | NULL                      | 5     | 5.00     |    20.00 |      20.00 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | n1       | ref    | PRIMARY,i_n_regionkey             | i_n_regionkey | 5       | dbt3.region.r_regionkey   | 5     | 5.00     |   100.00 |     100.00 | Using index                                  |
|    1 | SIMPLE      | customer | ref    | PRIMARY,i_c_nationkey             | i_c_nationkey | 5       | dbt3.n1.n_nationkey       | 60000 | 59967.60 |   100.00 |     100.00 | Using index                                  |
|    1 | SIMPLE      | orders   | ref    | PRIMARY,i_o_orderdate,i_o_cust... | i_o_custkey   | 5       | dbt3.customer.c_custkey   | 15    | 9.99     |    63.46 |      30.39 | Using where                                  |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_suppkey_partkey,i_... | PRIMARY       | 4       | dbt3.orders.o_orderkey    | 3     | 4.00     |   100.00 |     100.00 | Using where                                  |
|    1 | SIMPLE      | part     | eq_ref | PRIMARY                           | PRIMARY       | 4       | dbt3.lineitem.l_partkey   | 1     | 1.00     |    16.41 |       0.68 | Using where                                  |
|    1 | SIMPLE      | supplier | eq_ref | PRIMARY,i_s_nationkey             | PRIMARY       | 4       | dbt3.lineitem.l_suppkey   | 1     | 1.00     |   100.00 |     100.00 | Using where                                  |
|    1 | SIMPLE      | n2       | eq_ref | PRIMARY                           | PRIMARY       | 4       | dbt3.supplier.s_nationkey | 1     | 1.00     |   100.00 |     100.00 |                                              |
+------+-------------+----------+--------+-----------------------------------+---------------+---------+---------------------------+-------+----------+----------+------------+----------------------------------------------+
8 rows in set (28.313 sec)


{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 27915.39806,
    "filesort": {
      "sort_key": "year(orders.o_orderDATE)",
      "r_loops": 1,
      "r_total_time_ms": 0.031991748,
      "r_used_priority_queue": false,
      "r_output_rows": 2,
      "r_buffer_size": "320",
      "r_sort_mode": "sort_key,rowid",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "region",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY"],
              "r_loops": 1,
              "rows": 5,
              "r_rows": 5,
              "r_table_time_ms": 0.032567216,
              "r_other_time_ms": 0.021955539,
              "filtered": 20,
              "r_filtered": 20,
              "attached_condition": "region.r_name = 'MIDDLE EAST'"
            }
          },
          {
            "table": {
              "table_name": "n1",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "i_n_regionkey"],
              "key": "i_n_regionkey",
              "key_length": "5",
              "used_key_parts": ["n_regionkey"],
              "ref": ["dbt3.region.r_regionkey"],
              "r_loops": 1,
              "rows": 5,
              "r_rows": 5,
              "r_table_time_ms": 0.019216112,
              "r_other_time_ms": 0.008585629,
              "filtered": 100,
              "r_filtered": 100,
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "customer",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "i_c_nationkey"],
              "key": "i_c_nationkey",
              "key_length": "5",
              "used_key_parts": ["c_nationkey"],
              "ref": ["dbt3.n1.n_nationkey"],
              "r_loops": 5,
              "rows": 60000,
              "r_rows": 59967.6,
              "r_table_time_ms": 64.45527132,
              "r_other_time_ms": 28.62480552,
              "filtered": 100,
              "r_filtered": 100,
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "orders",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "i_o_orderdate", "i_o_custkey"],
              "key": "i_o_custkey",
              "key_length": "5",
              "used_key_parts": ["o_custkey"],
              "ref": ["dbt3.customer.c_custkey"],
              "r_loops": 299838,
              "rows": 15,
              "r_rows": 9.994457007,
              "r_table_time_ms": 10260.55906,
              "r_other_time_ms": 321.1368919,
              "filtered": 63.45537567,
              "r_filtered": 30.38644277,
              "attached_condition": "orders.o_orderDATE between '1995-01-01' and '1996-12-31'"
            }
          },
          {
            "table": {
              "table_name": "lineitem",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "i_l_suppkey_partkey",
                "i_l_partkey",
                "i_l_suppkey",
                "i_l_orderkey",
                "i_l_orderkey_quantity"
              ],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["l_orderkey"],
              "ref": ["dbt3.orders.o_orderkey"],
              "r_loops": 910596,
              "rows": 3,
              "r_rows": 3.998513062,
              "r_table_time_ms": 6768.155725,
              "r_other_time_ms": 648.4647346,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "lineitem.l_partkey is not null and lineitem.l_suppkey is not null"
            }
          },
          {
            "table": {
              "table_name": "part",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["p_partkey"],
              "ref": ["dbt3.lineitem.l_partkey"],
              "r_loops": 3641030,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 9430.665396,
              "r_other_time_ms": 253.2227552,
              "filtered": 16.40625,
              "r_filtered": 0.675742853,
              "attached_condition": "part.p_type = 'PROMO POLISHED BRASS'"
            }
          },
          {
            "table": {
              "table_name": "supplier",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "i_s_nationkey"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["s_suppkey"],
              "ref": ["dbt3.lineitem.l_suppkey"],
              "r_loops": 24604,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 74.41022886,
              "r_other_time_ms": 5.749360606,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "supplier.s_nationkey is not null"
            }
          },
          {
            "table": {
              "table_name": "n2",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["n_nationkey"],
              "ref": ["dbt3.supplier.s_nationkey"],
              "r_loops": 24604,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 22.45603587,
              "r_other_time_ms": 37.32775323,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        ]
      }
    }
  }
}