better plan for job-11D

## Enhancement
- step1
Restore data from `s3://benchmark/imdb/`.
- step 2
drop stats of all tables;
Run query [11D](https://github.com/gregrahn/join-order-benchmark/blob/master/11d.sql)
- step3
Analyze all the tables, and run query 11D.

We'll see, the plan in step2 run much faster than plan in step3.

- plan in step2, costs 5s
``` sql
root@127.0.0.1:4440 [imdb]> SELECT MIN(cn.name) AS from_company, MIN(mc.note) AS production_note, MIN(t.title) AS movie_based_on_book FROM company_name AS cn, company_type AS ct, keyword AS k, link_type AS lt, movie_companies AS mc, movie_keyword AS mk, movie_link AS ml, title AS t WHERE cn.country_code !='[pl]' AND ct.kind
!= 'production companies' and ct.kind is not NULL AND k.keyword in ('sequel', 'revenge', 'based-on-novel') AND mc.note is not NULL AND t.production_year > 1950 AND lt.id = ml.link_type_id AND ml.movie_id = t.id AND t.id = mk.movie_id AND mk.keyword_id = k.id AND t.id = mc.movie_id AND mc.company_type_id = ct.id AND mc.company_id = cn.id AND ml.movie_id = mk.movie_id AND ml.movie_id = mc.movie_id AND mk.movie_id = mc.movie_id;
+-------------------+-------------------+---------------------+
| from_company | production_note | movie_based_on_book |
+-------------------+-------------------+---------------------+
| '84 Entertainment | (!99?) (USA) (TV) | $5 a Day |
+-------------------+-------------------+---------------------+
1 row in set (5.49 sec)

root@127.0.0.1:4440 [imdb]>explain analyze SELECT MIN(cn.name) AS from_company, MI
N(mc.note) AS production_note, MIN(t.title) AS movie_based_on_book FROM company_na
me AS cn, company_type AS ct, keyword AS k, link_type AS lt, movie_companies AS mc
, movie_keyword AS mk, movie_link AS ml, title AS t WHERE cn.country_code !='[pl]
' AND ct.kind != 'production companies' and ct.kind is not NULL AND k.keyword in
('sequel', 'revenge', 'based-on-novel') AND mc.note is not NULL AND t.production
_year > 1950 AND lt.id = ml.link_type_id AND ml.movie_id = t.id AND t.id = mk.mov
ie_id AND mk.keyword_id = k.id AND t.id = mc.movie_id AND mc.company_type_id = ct.
id AND mc.company_id = cn.id AND ml.movie_id = mk.movie_id AND ml.movie_id = mc.mo
vie_id AND mk.movie_id = mc.movie_id;
+-----------------------------------------------------------------+---------+---------+-----------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------------------------------------------+---------+---------+-----------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| HashAgg_34 | 1.00 | 1 | root | | time:5.12s, loops:2, partial_worker:{wall_time:5.116449693s, concurrency:5, task_num:9006, tot_wait:22.839075202s, tot_exec:2.739429253s, tot_time:25.581367941s, max:5.116427497s, p95:5.116427497s}, final_worker:{wall_time:5.116462577s, concurrency:5, task_num:5, tot_wait:25.582165361s, tot_exec:46.669µs, tot_time:25.582215766s, max:5.116445521s, p95:5.116445521s} | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.movie_companies.note)->Column#40, funcs:min(imdb.title.title)->Column#41 | 2.47 MB | N/A |
| └─Projection_36 | 143.05 | 9221275 | root | | time:5.07s, loops:9007, Concurrency:OFF | imdb.company_name.name, imdb.movie_companies.note, imdb.title.title | 4.61 MB | N/A |
| └─IndexJoin_42 | 143.05 | 9221275 | root | | time:5.04s, loops:9007, inner:{total:6.65s, concurrency:5, task:368, construct:6.15s, fetch:490.5ms, build:3.82ms}, probe:3.32s | inner join, inner:TableReader_39, outer key:imdb.movie_link.link_type_id, inner key:imdb.link_type.id, equal cond:eq(imdb.movie_link.link_type_id, imdb.link_type.id) | 28.6 MB | N/A |
| ├─IndexJoin_52(Build) | 114.44 | 9221275 | root | | time:3.22s, loops:9011, inner:{total:1.98s, concurrency:5, task:16, construct:271.2ms, fetch:1.59s, build:124.7ms}, probe:2.12s | inner join, inner:IndexLookUp_51, outer key:imdb.movie_keyword.movie_id, inner key:imdb.movie_link.movie_id, equal cond:eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id), eq(imdb.movie_keyword.movie_id, imdb.movie_link.movie_id), eq(imdb.title.id, imdb.movie_link.movie_id) | 40.1 MB | N/A |
| │ ├─IndexJoin_69(Build) | 91.55 | 202446 | root | | time:1.6s, loops:203, inner:{total:165.6ms, concurrency:5, task:17, construct:150.8ms, fetch:14.7ms, build:45.8µs}, probe:91.8ms | inner join, inner:TableReader_65, outer key:imdb.movie_companies.company_type_id, inner key:imdb.company_type.id, equal cond:eq(imdb.movie_companies.company_type_id, imdb.company_type.id) | 13.1 MB | N/A |
| │ │ ├─IndexJoin_94(Build) | 73.24 | 221313 | root | | time:1.57s, loops:222, inner:{total:1.51s, concurrency:5, task:17, construct:241.1ms, fetch:1.25s, build:20ms}, probe:141.3ms | inner join, inner:TableReader_90, outer key:imdb.movie_companies.company_id, inner key:imdb.company_name.id, equal cond:eq(imdb.movie_companies.company_id, imdb.company_name.id) | 22.0 MB | N/A |
| │ │ │ ├─IndexHashJoin_107(Build) | 58.59 | 224891 | root | | time:1.39s, loops:224, inner:{total:1.98s, concurrency:5, task:10, construct:50.5ms, fetch:1.73s, build:12.3ms, join:199.5ms} | inner join, inner:IndexLookUp_104, outer key:imdb.movie_keyword.movie_id, inner key:imdb.movie_companies.movie_id, equal cond:eq(imdb.movie_keyword.movie_id, imdb.movie_companies.movie_id), eq(imdb.title.id, imdb.movie_companies.movie_id) | 12.3 MB | N/A |
| │ │ │ │ ├─IndexHashJoin_125(Build) | 46.87 | 40873 | root | | time:688.7ms, loops:44, inner:{total:1.3s, concurrency:5, task:10, construct:47.7ms, fetch:1.22s, build:13.2ms, join:31.8ms} | inner join, inner:TableReader_119, outer key:imdb.movie_keyword.movie_id, inner key:imdb.title.id, equal cond:eq(imdb.movie_keyword.movie_id, imdb.title.id) | 4.97 MB | N/A |
| │ │ │ │ │ ├─IndexJoin_152(Build) | 37.50 | 52583 | root | | time:224.2ms, loops:57, inner:{total:204.7ms, concurrency:5, task:1, construct:19.2µs, fetch:196.5ms, build:8.2ms}, probe:7.56ms | inner join, inner:IndexLookUp_151, outer key:imdb.keyword.id, inner key:imdb.movie_keyword.keyword_id, equal cond:eq(imdb.keyword.id, imdb.movie_keyword.keyword_id) | 997.3 KB | N/A |
| │ │ │ │ │ │ ├─IndexLookUp_187(Build) | 30.00 | 3 | root | | time:11.7ms, loops:3, index_task: {total_time: 6.16ms, fetch_handle: 6.15ms, build: 2.28µs, wait: 7.95µs}, table_task: {total_time: 22.6ms, num: 3, concurrency: 5}, next: {wait_index: 2.11ms, wait_table_lookup_build: 95.9µs, wait_table_lookup_resp: 9.42ms} | | 11.0 KB | N/A |
| │ │ │ │ │ │ │ ├─IndexRangeScan_184(Build) | 30.00 | 258 | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | time:6.12ms, loops:6, cop_task: {num: 2, max: 4.29ms, min: 1.77ms, avg: 3.03ms, p95: 4.29ms, max_proc_keys: 224, p95_proc_keys: 224, rpc_num: 2, rpc_time: 5.99ms, copr_cache: disabled, build_task_duration: 50.4µs, max_distsql_concurrency: 1}, tikv_task:{proc max:1ms, min:0s, avg: 500µs, p80:1ms, p95:1ms, iters:5, tasks:2}, scan_detail: {total_process_keys: 258, total_process_keys_size: 11868, total_keys: 262, get_snapshot_time: 136.7µs, rocksdb: {key_skipped_count: 258, block: {cache_hit_count: 32}}} | range:["based","based"], ["reven","reven"], ["seque","seque"], keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ │ │ │ │ └─Selection_186(Probe) | 30.00 | 3 | cop[tikv] | | time:22.2ms, loops:6, cop_task: {num: 3, max: 9.34ms, min: 3.49ms, avg: 7.31ms, p95: 9.34ms, max_proc_keys: 128, p95_proc_keys: 128, tot_proc: 14ms, rpc_num: 3, rpc_time: 21.9ms, copr_cache: disabled, build_task_duration: 108.8µs, max_distsql_concurrency: 1}, tikv_task:{proc max:8ms, min:3ms, avg: 5.33ms, p80:8ms, p95:8ms, iters:8, tasks:3}, scan_detail: {total_process_keys: 258, total_process_keys_size: 16408, total_keys: 265, get_snapshot_time: 155.6µs, rocksdb: {key_skipped_count: 14, block: {cache_hit_count: 1488}}} | in(imdb.keyword.keyword, "sequel", "revenge", "based-on-novel") | N/A | N/A |
| │ │ │ │ │ │ │ └─TableRowIDScan_185 | 30.00 | 258 | cop[tikv] | table:k | tikv_task:{proc max:8ms, min:3ms, avg: 5.33ms, p80:8ms, p95:8ms, iters:8, tasks:3} | keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ │ │ │ └─IndexLookUp_151(Probe) | 37.50 | 52583 | root | | time:196.3ms, loops:53, index_task: {total_time: 61ms, fetch_handle: 60.9ms, build: 7.01µs, wait: 26.4µs}, table_task: {total_time: 479.1ms, num: 6, concurrency: 5}, next: {wait_index: 2.72ms, wait_table_lookup_build: 719.9µs, wait_table_lookup_resp: 189ms} | | 1.50 MB | N/A |
| │ │ │ │ │ │ ├─IndexRangeScan_149(Build) | 37.50 | 52583 | cop[tikv] | table:mk, index:movie_keyword_idx_keywordid(keyword_id) | time:57.2ms, loops:55, cop_task: {num: 15, max: 22.1ms, min: 1.03ms, avg: 4.89ms, p95: 22.1ms, max_proc_keys: 17376, p95_proc_keys: 17376, tot_proc: 53ms, rpc_num: 15, rpc_time: 73.2ms, copr_cache: disabled, build_task_duration: 25.6µs, max_distsql_concurrency: 2}, tikv_task:{proc max:21ms, min:0s, avg: 4ms, p80:7ms, p95:21ms, iters:108, tasks:15}, scan_detail: {total_process_keys: 52583, total_process_keys_size: 2418818, total_keys: 52600, get_snapshot_time: 1.1ms, rocksdb: {key_skipped_count: 52583, block: {cache_hit_count: 178}}} | range: decided by [eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)], keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ │ │ │ └─TableRowIDScan_150(Probe) | 37.50 | 52583 | cop[tikv] | table:mk | time:441.3ms, loops:66, cop_task: {num: 37, max: 120.4ms, min: 4.43ms, avg: 40.5ms, p95: 110.3ms, max_proc_keys: 5237, p95_proc_keys: 5104, tot_proc: 1.33s, tot_wait: 1ms, rpc_num: 37, rpc_time: 1.5s, copr_cache: disabled, build_task_duration: 2.36ms, max_distsql_concurrency: 9}, tikv_task:{proc max:112ms, min:2ms, avg: 36.1ms, p80:55ms, p95:102ms, iters:209, tasks:37}, scan_detail: {total_process_keys: 52583, total_process_keys_size: 2366181, total_keys: 53982, get_snapshot_time: 3.15ms, rocksdb: {key_skipped_count: 3271, block: {cache_hit_count: 276013}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ │ │ └─TableReader_119(Probe) | 12.50 | 40422 | root | | time:1.19s, loops:58, cop_task: {num: 97, max: 153.3ms, min: 445.4µs, avg: 16.7ms, p95: 61.8ms, max_proc_keys: 7136, p95_proc_keys: 2680, tot_proc: 1.2s, tot_wait: 6ms, rpc_num: 97, rpc_time: 1.62s, copr_cache: disabled, build_task_duration: 2.5ms, max_distsql_concurrency: 6} | data:Selection_118 | N/A | N/A |
| │ │ │ │ │ └─Selection_118 | 12.50 | 40422 | cop[tikv] | | tikv_task:{proc max:146ms, min:0s, avg: 12.7ms, p80:17ms, p95:54ms, iters:301, tasks:97}, scan_detail: {total_process_keys: 52104, total_process_keys_size: 5585106, total_keys: 56250, get_snapshot_time: 9.18ms, rocksdb: {key_skipped_count: 12035, block: {cache_hit_count: 246097}}} | gt(imdb.title.production_year, 1950) | N/A | N/A |
| │ │ │ │ │ └─TableRangeScan_117 | 37.50 | 52104 | cop[tikv] | table:t | tikv_task:{proc max:146ms, min:0s, avg: 12.7ms, p80:17ms, p95:53ms, iters:301, tasks:97} | range: decided by [imdb.movie_keyword.movie_id], keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ │ └─IndexLookUp_104(Probe) | 58.59 | 212764 | root | | time:1.68s, loops:222, index_task: {total_time: 1.28s, fetch_handle: 1.28s, build: 76.4µs, wait: 232.3µs}, table_task: {total_time: 1.29s, num: 47, concurrency: 5}, next: {wait_index: 509.9ms, wait_table_lookup_build: 94.7ms, wait_table_lookup_resp: 1.03s} | | 355.6 KB | N/A |
| │ │ │ │ ├─IndexRangeScan_101(Build) | 58.65 | 276840 | cop[tikv] | table:mc, index:movie_companies_idx_mid(movie_id) | time:1.26s, loops:306, cop_task: {num: 134, max: 141ms, min: 690.7µs, avg: 15ms, p95: 42.1ms, max_proc_keys: 33760, p95_proc_keys: 9184, tot_proc: 1.49s, rpc_num: 134, rpc_time: 2.01s, copr_cache: disabled, build_task_duration: 1.43ms, max_distsql_concurrency: 6}, tikv_task:{proc max:134ms, min:0s, avg: 11.6ms, p80:17ms, p95:38ms, iters:757, tasks:134}, scan_detail: {total_process_keys: 276840, total_process_keys_size: 12734640, total_keys: 317060, get_snapshot_time: 3.52ms, rocksdb: {key_skipped_count: 276840, block: {cache_hit_count: 246722}}} | range: decided by [eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id)], keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ │ └─Selection_103(Probe) | 58.59 | 212764 | cop[tikv] | | time:1.15s, loops:288, cop_task: {num: 155, max: 84.7ms, min: 644.4µs, avg: 15.5ms, p95: 67ms, max_proc_keys: 15376, p95_proc_keys: 8990, tot_proc: 2.05s, tot_wait: 12ms, rpc_num: 155, rpc_time: 2.39s, copr_cache: disabled, build_task_duration: 4.9ms, max_distsql_concurrency: 5, max_extra_concurrency: 1}, tikv_task:{proc max:79ms, min:0s, avg: 13.4ms, p80:18ms, p95:62ms, iters:874, tasks:155}, scan_detail: {total_process_keys: 276840, total_process_keys_size: 19871988, total_keys: 312233, get_snapshot_time: 15.9ms, rocksdb: {key_skipped_count: 256116, block: {cache_hit_count: 357204}}} | not(isnull(imdb.movie_companies.note)) | N/A | N/A |
| │ │ │ │ └─TableRowIDScan_102 | 58.65 | 276840 | cop[tikv] | table:mc | tikv_task:{proc max:78ms, min:0s, avg: 13.1ms, p80:18ms, p95:60ms, iters:874, tasks:155} | keep order:false, stats:pseudo | N/A | N/A |
| │ │ │ └─TableReader_90(Probe) | 39.00 | 56140 | root | | time:1.22s, loops:85, cop_task: {num: 78, max: 49.1ms, min: 448µs, avg: 15.6ms, p95: 42.2ms, max_proc_keys: 2998, p95_proc_keys: 2253, tot_proc: 906ms, tot_wait: 1ms, rpc_num: 78, rpc_time: 1.22s, copr_cache: disabled, build_task_duration: 2.34ms, max_distsql_concurrency: 1} | data:Selection_89 | N/A | N/A |
| │ │ │ └─Selection_89 | 39.00 | 56140 | cop[tikv] | | tikv_task:{proc max:45ms, min:0s, avg: 11.8ms, p80:24ms, p95:39ms, iters:307, tasks:78}, scan_detail: {total_process_keys: 58627, total_process_keys_size: 6533696, total_keys: 66355, get_snapshot_time: 3.61ms, rocksdb: {key_skipped_count: 20508, block: {cache_hit_count: 244763}}} | ne(imdb.company_name.country_code, "[pl]") | N/A | N/A |
| │ │ │ └─TableRangeScan_88 | 58.59 | 58627 | cop[tikv] | table:cn | tikv_task:{proc max:45ms, min:0s, avg: 11.7ms, p80:24ms, p95:37ms, iters:307, tasks:78} | range: decided by [imdb.movie_companies.company_id], keep order:false, stats:pseudo | N/A | N/A |
| │ │ └─TableReader_65(Probe) | 58.54 | 17 | root | | time:12.5ms, loops:34, cop_task: {num: 19, max: 774.7µs, min: 394.8µs, avg: 583.8µs, p95: 774.7µs, max_proc_keys: 2, p95_proc_keys: 2, rpc_num: 19, rpc_time: 10.6ms, copr_cache: disabled, build_task_duration: 453.3µs, max_distsql_concurrency: 1, max_extra_concurrency: 1} | data:Selection_64 | N/A | N/A |
| │ │ └─Selection_64 | 58.54 | 17 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:19, tasks:19}, scan_detail: {total_process_keys: 32, total_process_keys_size: 1656, total_keys: 49, get_snapshot_time: 321.9µs, rocksdb: {key_skipped_count: 30, block: {cache_hit_count: 116}}} | ne(imdb.company_type.kind, "production companies"), not(isnull(imdb.company_type.kind)) | N/A | N/A |
| │ │ └─TableRangeScan_63 | 73.24 | 32 | cop[tikv] | table:ct | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:19, tasks:19} | range: decided by [imdb.movie_companies.company_type_id], keep order:false, stats:pseudo | N/A | N/A |
| │ └─IndexLookUp_51(Probe) | 114.44 | 364958 | root | | time:1.55s, loops:381, index_task: {total_time: 1.17s, fetch_handle: 1.17s, build: 114.5µs, wait: 324.9µs}, table_task: {total_time: 965.7ms, num: 61, concurrency: 5}, next: {wait_index: 703.9ms, wait_table_lookup_build: 84.7ms, wait_table_lookup_resp: 733.3ms} | | 8.91 KB | N/A |
| │ ├─IndexRangeScan_49(Build) | 114.44 | 364958 | cop[tikv] | table:ml, index:movie_link_idx_mid(movie_id) | time:1.14s, loops:414, cop_task: {num: 160, max: 52.2ms, min: 760.4µs, avg: 9.91ms, p95: 31ms, max_proc_keys: 17376, p95_proc_keys: 9184, tot_proc: 1.18s, rpc_num: 160, rpc_time: 1.58s, copr_cache: disabled, build_task_duration: 1.47ms, max_distsql_concurrency: 4}, tikv_task:{proc max:48ms, min:0s, avg: 7.76ms, p80:11ms, p95:26ms, iters:948, tasks:160}, scan_detail: {total_process_keys: 364958, total_process_keys_size: 16788068, total_keys: 393102, get_snapshot_time: 3.63ms, rocksdb: {key_skipped_count: 364958, block: {cache_hit_count: 196284}}} | range: decided by [eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id)], keep order:false, stats:pseudo | N/A | N/A |
| │ └─TableRowIDScan_50(Probe) | 114.44 | 364958 | cop[tikv] | table:ml | time:838.6ms, loops:449, cop_task: {num: 94, max: 47ms, min: 675.5µs, avg: 10.4ms, p95: 36.8ms, max_proc_keys: 19639, p95_proc_keys: 16876, tot_proc: 770ms, tot_wait: 45ms, rpc_num: 94, rpc_time: 977.6ms, copr_cache: disabled, build_task_duration: 3.63ms, max_distsql_concurrency: 3, max_extra_concurrency: 1}, tikv_task:{proc max:40ms, min:0s, avg: 8.45ms, p80:15ms, p95:32ms, iters:754, tasks:94}, scan_detail: {total_process_keys: 364958, total_process_keys_size: 18609054, total_keys: 376864, get_snapshot_time: 49.9ms, rocksdb: {key_skipped_count: 361927, block: {cache_hit_count: 90958}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_39(Probe) | 114.44 | 4607 | root | | time:444.4ms, loops:736, cop_task: {num: 522, max: 11.8ms, min: 380µs, avg: 781µs, p95: 1.14ms, max_proc_keys: 16, p95_proc_keys: 15, rpc_num: 522, rpc_time: 391.5ms, copr_cache: disabled, build_task_duration: 6.25ms, max_distsql_concurrency: 1, max_extra_concurrency: 1} | data:TableRangeScan_38 | N/A | N/A |
| └─TableRangeScan_38 | 114.44 | 4607 | cop[tikv] | table:lt | tikv_task:{proc max:1ms, min:0s, avg: 116.9µs, p80:0s, p95:1ms, iters:522, tasks:522}, scan_detail: {total_process_keys: 4607, total_process_keys_size: 132041, total_keys: 5617, get_snapshot_time: 61.3ms, rocksdb: {key_skipped_count: 4213, block: {cache_hit_count: 8398}}} | range: decided by [imdb.movie_link.link_type_id], keep order:false, stats:pseudo | N/A | N/A |
+-----------------------------------------------------------------+---------+---------+-----------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
34 rows in set (5.12 sec)
```

- plan in step3, costs 340s for the first time, and 225s for the second time
``` sql
root@127.0.0.1:4440 [imdb]> SELECT MIN(cn.name) AS from_company, MIN(mc.note) AS production_note, MIN(t.title) AS movie_based_on_book FROM company_name AS cn, company_type AS ct, keyword AS k, link_type AS lt, movie_companies AS mc, movie_keyword AS mk, movie_link AS ml, title AS t WHERE cn.country_code !='[pl]' AND ct.kind != 'production companies' and ct.kind is not NULL AND k.keyword in ('sequel', 'revenge', 'based-on-novel') AND mc.note is not NULL AND t.production_year > 1950 AND lt.id = ml.link_type_id AND ml.movie_id = t.id AND t.id = mk.movie_id AND mk.keyword_id = k.id AND t.id = mc.movie_id AND mc.company_type_id = ct.id AND mc.company_id =
cn.id AND ml.movie_id = mk.movie_id AND ml.movie_id = mc.movie_id AND mk.movie_id = mc.movie_id;
+-------------------+-------------------+---------------------+
| from_company | production_note | movie_based_on_book |
+-------------------+-------------------+---------------------+
| '84 Entertainment | (!99?) (USA) (TV) | $5 a Day |
+-------------------+-------------------+---------------------+
1 row in set (5 min 40.66 sec)

root@127.0.0.1:4440 [imdb]>explain analyze SELECT MIN(cn.name) AS from_company, MIN(mc.note) AS production_note, MIN(t.title) AS movie_based_on_book FROM company_name
AS cn, company_type AS ct, keyword AS k, link_type AS lt, movie_companies AS mc, movie_keyword AS mk, movie_link AS ml, title AS t WHERE cn.country_code !='[pl]' AN
D ct.kind != 'production companies' and ct.kind is not NULL AND k.keyword in ('sequel', 'revenge', 'based-on-novel') AND mc.note is not NULL AND t.production_year
> 1950 AND lt.id = ml.link_type_id AND ml.movie_id = t.id AND t.id = mk.movie_id AND mk.keyword_id = k.id AND t.id = mc.movie_id AND mc.company_type_id = ct.id AND mc.company_id = cn.id AND ml.movie_id = mk.movie_id AND ml.movie_id = mc.movie_id AND mk.movie_id = mc.movie_id;
+-------------------------------------------------+------------+------------+-----------+---------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------------------------+------------+------------+-----------+---------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashAgg_35 | 1.00 | 1 | root | | time:3m45.4s, loops:2, partial_worker:{wall_time:3m45.355406465s, concurrency:5, task_num:9008, tot_wait:18m43.286214823s, tot_exec:3.486646369s, tot_time:18m46.776263152s, max:3m45.355355893s, p95:3m45.355355893s}, final_worker:{wall_time:3m45.355456139s, concurrency:5, task_num:5, tot_wait:18m46.776886047s, tot_exec:90.716µs, tot_time:18m46.776981947s, max:3m45.355410651s, p95:3m45.355410651s} | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.movie_companies.note)->Column#40, funcs:min(imdb.title.title)->Column#41 | 2.38 MB | N/A |
| └─Projection_37 | 5197.43 | 9221275 | root | | time:3m45.3s, loops:9009, Concurrency:5 | imdb.company_name.name, imdb.movie_companies.note, imdb.title.title | 2.23 MB | N/A |
| └─HashJoin_50 | 5197.43 | 9221275 | root | | time:3m45.3s, loops:9009, build_hash_table:{total:13.5ms, fetch:13.5ms, build:9.46µs}, probe:{concurrency:5, total:18m46.8s, max:3m45.4s, probe:4m37.6s, fetch:14m9.2s} | inner join, equal:[eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)] | 25.7 KB | 0 Bytes |
| ├─IndexLookUp_236(Build) | 145.36 | 3 | root | | time:13.4ms, loops:2, index_task: {total_time: 3.06ms, fetch_handle: 3.06ms, build: 925ns, wait: 4.51µs}, table_task: {total_time: 10.1ms, num: 1, concurrency: 5}, next: {wait_index: 3.23ms, wait_table_lookup_build: 291.7µs, wait_table_lookup_resp: 9.84ms} | | 13.0 KB | N/A |
| │ ├─IndexRangeScan_233(Build) | 145.36 | 258 | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | time:3.03ms, loops:3, cop_task: {num: 2, max: 2.2ms, min: 774.5µs, avg: 1.49ms, p95: 2.2ms, max_proc_keys: 224, p95_proc_keys: 224, rpc_num: 2, rpc_time: 2.94ms, copr_cache: disabled, build_task_duration: 47.6µs, max_distsql_concurrency: 1}, tikv_task:{proc max:1ms, min:1ms, avg: 1ms, p80:1ms, p95:1ms, iters:5, tasks:2}, scan_detail: {total_process_keys: 258, total_process_keys_size: 11868, total_keys: 262, get_snapshot_time: 867.1µs, rocksdb: {key_skipped_count: 258, block: {cache_hit_count: 32}}} | range:["based","based"], ["reven","reven"], ["seque","seque"], keep order:false | N/A | N/A |
| │ └─Selection_235(Probe) | 145.36 | 3 | cop[tikv] | | time:9.81ms, loops:2, cop_task: {num: 1, max: 9.7ms, proc_keys: 258, tot_proc: 7ms, rpc_num: 1, rpc_time: 9.68ms, copr_cache: disabled, build_task_duration: 42.5µs, max_distsql_concurrency: 1}, tikv_task:{time:8ms, loops:4}, scan_detail: {total_process_keys: 258, total_process_keys_size: 16408, total_keys: 265, get_snapshot_time: 581.4µs, rocksdb: {key_skipped_count: 14, block: {cache_hit_count: 1473}}} | in(imdb.keyword.keyword, "sequel", "revenge", "based-on-novel") | N/A | N/A |
| │ └─TableRowIDScan_234 | 145.36 | 258 | cop[tikv] | table:k | tikv_task:{time:8ms, loops:4} | keep order:false | N/A | N/A |
| └─HashJoin_64(Probe) | 8378871.38 | 2581940039 | root | | time:3m41.2s, loops:2521429, build_hash_table:{total:7.29s, fetch:2.58s, build:4.71s}, probe:{concurrency:5, total:18m36.5s, max:3m45.4s, probe:17m59.8s, fetch:36.6s} | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id) eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id) eq(imdb.title.id, imdb.movie_keyword.movie_id)] | 2.40 GB | 0 Bytes |
| ├─HashJoin_75(Build) | 2585150.00 | 17625104 | root | | time:2.92s, loops:17215, build_hash_table:{total:1.24ms, fetch:1.23ms, build:16.4µs}, probe:{concurrency:5, total:36.4s, max:7.29s, probe:24.5s, fetch:12s} | inner join, equal:[eq(imdb.movie_link.link_type_id, imdb.link_type.id)] | 9.46 KB | 0 Bytes |
| │ ├─TableReader_225(Build) | 18.00 | 18 | root | | time:1.19ms, loops:2, cop_task: {num: 1, max: 2.14ms, proc_keys: 18, rpc_num: 1, rpc_time: 2.08ms, copr_cache: disabled, build_task_duration: 4.04µs, max_distsql_concurrency: 1} | data:TableFullScan_224 | 425 Bytes | N/A |
| │ │ └─TableFullScan_224 | 18.00 | 18 | cop[tikv] | table:lt | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 18, total_process_keys_size: 486, total_keys: 19, get_snapshot_time: 1.18ms, rocksdb: {key_skipped_count: 18, block: {cache_hit_count: 6}}} | keep order:false | N/A | N/A |
| │ └─HashJoin_88(Probe) | 2585150.00 | 17625104 | root | | time:2.44s, loops:17216, build_hash_table:{total:1.15s, fetch:574.9ms, build:578.7ms}, probe:{concurrency:5, total:36.3s, max:7.29s, probe:24.5s, fetch:11.8s} | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id) eq(imdb.title.id, imdb.movie_link.movie_id)] | 96.6 MB | 0 Bytes |
| │ ├─TableReader_223(Build) | 2585150.00 | 2585150 | root | | time:590.2ms, loops:2529, cop_task: {num: 77, max: 69.3ms, min: 1ms, avg: 31.2ms, p95: 63.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 2.23s, tot_wait: 3ms, rpc_num: 77, rpc_time: 2.4s, copr_cache: disabled, build_task_duration: 24.6µs, max_distsql_concurrency: 3} | data:TableFullScan_222 | 1.53 MB | N/A |
| │ │ └─TableFullScan_222 | 2585150.00 | 2585150 | cop[tikv] | table:ml | tikv_task:{proc max:66ms, min:0s, avg: 27.9ms, p80:39ms, p95:57ms, iters:2831, tasks:77}, scan_detail: {total_process_keys: 2585150, total_process_keys_size: 131808712, total_keys: 2585227, get_snapshot_time: 5.59ms, rocksdb: {key_skipped_count: 2585150, block: {cache_hit_count: 5524}}} | keep order:false | N/A | N/A |
| │ └─HashJoin_102(Probe) | 2307846.25 | 1714499 | root | | time:2.36s, loops:1678, build_hash_table:{total:2.35s, fetch:144.6ms, build:2.21s}, probe:{concurrency:5, total:36s, max:7.21s, probe:24.2s, fetch:11.8s} | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.title.id)] | 403.4 MB | 0 Bytes |
| │ ├─TableReader_218(Build) | 4234453.00 | 4247643 | root | | time:178.7ms, loops:4508, cop_task: {num: 133, max: 96ms, min: 1.38ms, avg: 41.4ms, p95: 83.4ms, max_proc_keys: 68576, p95_proc_keys: 61408, tot_proc: 4.97s, tot_wait: 26ms, rpc_num: 133, rpc_time: 5.51s, copr_cache: disabled, build_task_duration: 29.5µs, max_distsql_concurrency: 6} | data:Selection_217 | 13.8 MB | N/A |
| │ │ └─Selection_217 | 4234453.00 | 4247643 | cop[tikv] | | tikv_task:{proc max:79ms, min:0s, avg: 33.4ms, p80:53ms, p95:70ms, iters:5155, tasks:133}, scan_detail: {total_process_keys: 4736508, total_process_keys_size: 524795377, total_keys: 4736641, get_snapshot_time: 32.3ms, rocksdb: {key_skipped_count: 4736576, block: {cache_hit_count: 19024}}} | gt(imdb.title.production_year, 1950) | N/A | N/A |
| │ │ └─TableFullScan_216 | 4736508.00 | 4736508 | cop[tikv] | table:t | tikv_task:{proc max:77ms, min:0s, avg: 32.1ms, p80:51ms, p95:68ms, iters:5155, tasks:133} | keep order:false | N/A | N/A |
| │ └─HashJoin_146(Probe) | 2288639.00 | 1954290 | root | | time:551.2ms, loops:1912, build_hash_table:{total:541.3ms, fetch:372ms, build:169.4ms}, probe:{concurrency:5, total:35.8s, max:7.18s, probe:33s, fetch:2.74s} | inner join, equal:[eq(imdb.movie_companies.company_id, imdb.company_name.id)] | 38.3 MB | 0 Bytes |
| │ ├─TableReader_215(Build) | 338005.93 | 337601 | root | | time:382.1ms, loops:356, cop_task: {num: 15, max: 103.6ms, min: 1.84ms, avg: 34.6ms, p95: 103.6ms, max_proc_keys: 55264, p95_proc_keys: 55264, tot_proc: 468ms, tot_wait: 1ms, rpc_num: 15, rpc_time: 518.7ms, copr_cache: disabled, build_task_duration: 2.71µs, max_distsql_concurrency: 1} | data:Selection_214 | 4.46 MB | N/A |
| │ │ └─Selection_214 | 338005.93 | 337601 | cop[tikv] | | tikv_task:{proc max:84ms, min:0s, avg: 27.7ms, p80:58ms, p95:84ms, iters:413, tasks:15}, scan_detail: {total_process_keys: 362131, total_process_keys_size: 40171508, total_keys: 362146, get_snapshot_time: 2.25ms, rocksdb: {key_skipped_count: 362131, block: {cache_hit_count: 1463}}} | ne(imdb.company_name.country_code, "[pl]") | N/A | N/A |
| │ │ └─TableFullScan_213 | 362131.00 | 362131 | cop[tikv] | table:cn | tikv_task:{proc max:73ms, min:0s, avg: 23.9ms, p80:49ms, p95:73ms, iters:413, tasks:15} | keep order:false | N/A | N/A |
| │ └─HashJoin_194(Probe) | 2288639.00 | 1980874 | root | | time:21.5ms, loops:1938, build_hash_table:{total:532.3µs, fetch:523.3µs, build:9.03µs}, probe:{concurrency:5, total:35.4s, max:7.09s, probe:35.3s, fetch:140.3ms} | inner join, equal:[eq(imdb.company_type.id, imdb.movie_companies.company_type_id)] | 25.7 KB | 0 Bytes |
| │ ├─TableReader_205(Build) | 3.20 | 3 | root | | time:385.6µs, loops:2, cop_task: {num: 1, max: 2.35ms, proc_keys: 4, rpc_num: 1, rpc_time: 2.26ms, copr_cache: disabled, build_task_duration: 24.2µs, max_distsql_concurrency: 1} | data:Selection_204 | 413 Bytes | N/A |
| │ │ └─Selection_204 | 3.20 | 3 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_process_keys_size: 224, total_keys: 5, get_snapshot_time: 1.19ms, rocksdb: {key_skipped_count: 4, block: {cache_hit_count: 6}}} | ne(imdb.company_type.kind, "production companies"), not(isnull(imdb.company_type.kind)) | N/A | N/A |
| │ │ └─TableFullScan_203 | 4.00 | 4 | cop[tikv] | table:ct | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
| │ └─TableReader_212(Probe) | 2288639.00 | 2288639 | root | | time:53ms, loops:2313, cop_task: {num: 85, max: 552.1ms, min: 1.86ms, avg: 68.9ms, p95: 215ms, max_proc_keys: 542688, p95_proc_keys: 219104, tot_proc: 5.46s, tot_wait: 9ms, rpc_num: 85, rpc_time: 5.85s, copr_cache: disabled, build_task_duration: 20.3µs, max_distsql_concurrency: 5} | data:Selection_211 | 12.2 MB | N/A |
| │ └─Selection_211 | 2288639.00 | 2288639 | cop[tikv] | | tikv_task:{proc max:539ms, min:0s, avg: 59.9ms, p80:70ms, p95:208ms, iters:5182, tasks:85}, scan_detail: {total_process_keys: 4958296, total_process_keys_size: 307479730, total_keys: 4958381, get_snapshot_time: 14ms, rocksdb: {key_skipped_count: 4958296, block: {cache_hit_count: 11875}}} | not(isnull(imdb.movie_companies.note)) | N/A | N/A |
| │ └─TableFullScan_210 | 4958296.00 | 4958296 | cop[tikv] | table:mc | tikv_task:{proc max:505ms, min:0s, avg: 55ms, p80:63ms, p95:197ms, iters:5182, tasks:85} | keep order:false | N/A | N/A |
| └─TableReader_229(Probe) | 7480087.00 | 7480087 | root | | time:103.9ms, loops:7317, cop_task: {num: 230, max: 78.3ms, min: 1.02ms, avg: 32.2ms, p95: 60.6ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 6.7s, tot_wait: 47ms, rpc_num: 230, rpc_time: 7.4s, copr_cache: disabled, build_task_duration: 32.1µs, max_distsql_concurrency: 10} | data:TableFullScan_228 | 8.43 MB | N/A |
| └─TableFullScan_228 | 7480087.00 | 7480087 | cop[tikv] | table:mk | tikv_task:{proc max:67ms, min:0s, avg: 27.5ms, p80:46ms, p95:53ms, iters:8220, tasks:230}, scan_detail: {total_process_keys: 7480087, total_process_keys_size: 339128288, total_keys: 7480317, get_snapshot_time: 92.5ms, rocksdb: {key_skipped_count: 7480087, block: {cache_hit_count: 14756}}} | keep order:false | N/A | N/A |
+-------------------------------------------------+------------+------------+-----------+---------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
31 rows in set (3 min 45.36 sec)
```

tidb version: 61ed093fe1f47911fcb2b1a8ab1f33be0cc5aaca
tikv version: [6ea9c3af2c3d94b6f6e22311a7709a4b78872f62](https://github.com/tikv/tikv/commit/6ea9c3af2c3d94b6f6e22311a7709a4b78872f62)