Add Join Predicate Pushdown in CTE to improve the performance and reduce OOM in TiDB

[Yui-Song]

Enhancement

The VIEW PUSHED PREDICATE operation is a specific kind of predicate pushing, the join predicate pushdown transformation, which means "The view thus becomes correlated and must be evaluated for each row of the outer query block".
The idea behind that transformation is that the view may need to be executed more frequently, but adding the join predicate to the view can make it run much faster, since the tables in that view can now use an index access.

TiDB has implemented this kind of predicate pushing for SQL written with in-line view. For example:

perfdata>explain
    -> select distinct n.id olap_summary_log_id,
    ->     n.benchmark_id,
    ->     n.query_name,
    ->     n.plan_digest,
    ->     b.plan_digest baseline_plan_digest,
    ->     n.plan,
    ->     b.plan baseline_plan
    -> from (
    ->         select s.benchmark_id,
    ->             d.name query_name,
    ->             d.plan,
    ->             d.plan_digest
    ->         from baseline_olap_summary_logs s,
    ->             baseline_olap_detail_logs d
    ->         where s.id = d.baseline_olap_summary_log_id
    ->     ) b
    ->     right outer join (
    ->         select s.id,
    ->             b.id benchmark_id,
    ->             b.name benchmark_name,
    ->             d.name query_name,
    ->             d.plan,
    ->             d.plan_digest
    ->         from olap_summary_logs s,
    ->             olap_detail_logs d,
    ->             benchmarks b
    ->         where s.id = d.olap_summary_log_id
    ->             and s.benchmark_id = b.id
    ->     ) n on b.benchmark_id = n.benchmark_id
    ->     and b.query_name = n.query_name
    ->     and n.plan_digest <> b.plan_digest
    -> where n.id = 420015
    ->     and n.query_name = '10C';
+--------------------------------------------+---------+-----------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                         | estRows | task      | access object                                              | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------+---------+-----------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_21                                 | 1.00    | root      |                                                            | group by:perfdata.baseline_olap_detail_logs.plan, perfdata.baseline_olap_detail_logs.plan_digest, perfdata.benchmarks.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest, perfdata.olap_summary_logs.id, funcs:firstrow(perfdata.olap_summary_logs.id)->perfdata.olap_summary_logs.id, funcs:firstrow(perfdata.benchmarks.id)->perfdata.benchmarks.id, funcs:firstrow(perfdata.olap_detail_logs.name)->perfdata.olap_detail_logs.name, funcs:firstrow(perfdata.olap_detail_logs.plan_digest)->perfdata.olap_detail_logs.plan_digest, funcs:firstrow(perfdata.baseline_olap_detail_logs.plan_digest)->perfdata.baseline_olap_detail_logs.plan_digest, funcs:firstrow(perfdata.olap_detail_logs.plan)->perfdata.olap_detail_logs.plan, funcs:firstrow(perfdata.baseline_olap_detail_logs.plan)->perfdata.baseline_olap_detail_logs.plan |
| └─HashJoin_23                              | 0.00    | root      |                                                            | right outer join, equal:[eq(perfdata.baseline_olap_summary_logs.benchmark_id, perfdata.benchmarks.id) eq(perfdata.baseline_olap_detail_logs.name, perfdata.olap_detail_logs.name)], other cond:ne(perfdata.olap_detail_logs.plan_digest, perfdata.baseline_olap_detail_logs.plan_digest)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ├─Projection_40(Build)                   | 0.00    | root      |                                                            | perfdata.olap_summary_logs.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest, perfdata.benchmarks.id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │ └─HashJoin_42                          | 0.00    | root      |                                                            | CARTESIAN inner join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   │   ├─IndexLookUp_49(Build)              | 0.00    | root      |                                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │   │ ├─IndexRangeScan_46(Build)         | 0.00    | cop[tikv] | table:d, index:idx_olap_detail_logs_1(olap_summary_log_id) | range:[420015,420015], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|   │   │ └─Selection_48(Probe)              | 0.00    | cop[tikv] |                                                            | eq(perfdata.olap_detail_logs.name, "10C")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|   │   │   └─TableRowIDScan_47              | 0.00    | cop[tikv] | table:d                                                    | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │   └─IndexJoin_54(Probe)                | 1.25    | root      |                                                            | inner join, inner:TableReader_51, outer key:perfdata.olap_summary_logs.benchmark_id, inner key:perfdata.benchmarks.id, equal cond:eq(perfdata.olap_summary_logs.benchmark_id, perfdata.benchmarks.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   │     ├─Point_Get_61(Build)              | 1.00    | root      | table:olap_summary_logs                                    | handle:420015                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │     └─TableReader_51(Probe)            | 1.00    | root      |                                                            | data:TableRangeScan_50                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|   │       └─TableRangeScan_50              | 1.00    | cop[tikv] | table:b                                                    | range: decided by [perfdata.olap_summary_logs.benchmark_id], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|   └─HashJoin_34(Probe)                     | 9.99    | root      |                                                            | inner join, equal:[eq(perfdata.baseline_olap_summary_logs.id, perfdata.baseline_olap_detail_logs.baseline_olap_summary_log_id)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|     ├─TableReader_39(Build)                | 6.00    | root      |                                                            | data:TableFullScan_38                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     │ └─TableFullScan_38                   | 6.00    | cop[tikv] | table:s                                                    | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|     └─TableReader_37(Probe)                | 7.99    | root      |                                                            | data:Selection_36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|       └─Selection_36                       | 7.99    | cop[tikv] |                                                            | eq(perfdata.baseline_olap_detail_logs.name, "10C"), not(isnull(perfdata.baseline_olap_detail_logs.name)), not(isnull(perfdata.baseline_olap_detail_logs.plan_digest))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|         └─TableFullScan_35                 | 1943.00 | cop[tikv] | table:d                                                    | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+--------------------------------------------+---------+-----------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.06 sec)

We can see that the predicates n.id = 420015 and n.query_name = '10C' was pushed into the two in-line views.
However, if I rewrote the SQL statement with CTE, I got the following plan and what's worse, the execution failed with OOM:

perfdata@172.16.4.36:perfdata>explain
    -> with nightly_data as(
    ->     select s.id,
    ->         b.id benchmark_id,
    ->         b.name benchmark_name,
    ->         d.name query_name,
    ->         d.plan,
    ->         d.plan_digest
    ->     from olap_summary_logs s,
    ->         olap_detail_logs d,
    ->         benchmarks b
    ->     where s.id = d.olap_summary_log_id
    ->         and s.benchmark_id = b.id
    -> ),
    -> baseline_data as(
    ->     select s.benchmark_id,
    ->         d.name query_name,
    ->         d.plan,
    ->         d.plan_digest
    ->     from baseline_olap_summary_logs s,
    ->         baseline_olap_detail_logs d
    ->     where s.id = d.baseline_olap_summary_log_id
    -> )
    -> select distinct n.id olap_summary_log_id,
    ->     n.benchmark_id,
    ->     n.query_name,
    ->     n.plan_digest,
    ->     b.plan_digest baseline_plan_digest,
    ->     n.plan,
    ->     b.plan baseline_plan
    -> from baseline_data b
    ->     right outer join nightly_data n on b.benchmark_id = n.benchmark_id
    ->     and b.query_name = n.query_name
    ->     and n.plan_digest <> b.plan_digest
    -> where n.id = 420015
    ->     and n.query_name = '10C';
+----------------------------------------+-----------+-----------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows   | task      | access object                                              | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------+-----------+-----------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_90                             | 38619.90  | root      |                                                            | group by:perfdata.baseline_olap_detail_logs.plan, perfdata.baseline_olap_detail_logs.plan_digest, perfdata.benchmarks.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest, perfdata.olap_summary_logs.id, funcs:firstrow(perfdata.olap_summary_logs.id)->perfdata.olap_summary_logs.id, funcs:firstrow(perfdata.benchmarks.id)->perfdata.benchmarks.id, funcs:firstrow(perfdata.olap_detail_logs.name)->perfdata.olap_detail_logs.name, funcs:firstrow(perfdata.olap_detail_logs.plan_digest)->perfdata.olap_detail_logs.plan_digest, funcs:firstrow(perfdata.baseline_olap_detail_logs.plan_digest)->perfdata.baseline_olap_detail_logs.plan_digest, funcs:firstrow(perfdata.olap_detail_logs.plan)->perfdata.olap_detail_logs.plan, funcs:firstrow(perfdata.baseline_olap_detail_logs.plan)->perfdata.baseline_olap_detail_logs.plan |
| └─HashJoin_91                          | 167216.64 | root      |                                                            | right outer join, equal:[eq(perfdata.baseline_olap_summary_logs.benchmark_id, perfdata.benchmarks.id) eq(perfdata.baseline_olap_detail_logs.name, perfdata.olap_detail_logs.name)], other cond:ne(perfdata.olap_detail_logs.plan_digest, perfdata.baseline_olap_detail_logs.plan_digest)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ├─Selection_93(Build)                | 1554.40   | root      |                                                            | eq(perfdata.baseline_olap_detail_logs.name, "10C"), not(isnull(perfdata.baseline_olap_detail_logs.name)), not(isnull(perfdata.baseline_olap_detail_logs.plan_digest)), not(isnull(perfdata.baseline_olap_summary_logs.benchmark_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   │ └─CTEFullScan_94                   | 1943.00   | root      | CTE:b                                                      | data:CTE_1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|   └─Selection_95(Probe)                | 38619.90  | root      |                                                            | eq(perfdata.olap_detail_logs.name, "10C"), eq(perfdata.olap_summary_logs.id, 420015)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|     └─CTEFullScan_96                   | 48274.88  | root      | CTE:n                                                      | data:CTE_0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| CTE_1                                  | 1943.00   | root      |                                                            | Non-Recursive CTE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| └─HashJoin_33(Seed Part)               | 1943.00   | root      |                                                            | inner join, equal:[eq(perfdata.baseline_olap_summary_logs.id, perfdata.baseline_olap_detail_logs.baseline_olap_summary_log_id)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   ├─TableReader_37(Build)              | 6.00      | root      |                                                            | data:TableFullScan_36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   │ └─TableFullScan_36                 | 6.00      | cop[tikv] | table:s                                                    | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   └─TableReader_35(Probe)              | 1943.00   | root      |                                                            | data:TableFullScan_34                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     └─TableFullScan_34                 | 1943.00   | cop[tikv] | table:d                                                    | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| CTE_0                                  | 48274.88  | root      |                                                            | Non-Recursive CTE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| └─Projection_42(Seed Part)             | 48274.88  | root      |                                                            | perfdata.olap_summary_logs.id, perfdata.benchmarks.id, perfdata.benchmarks.name, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|   └─Projection_43                      | 48274.88  | root      |                                                            | perfdata.olap_summary_logs.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest, perfdata.benchmarks.id, perfdata.benchmarks.name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|     └─IndexHashJoin_50                 | 48274.88  | root      |                                                            | inner join, inner:IndexLookUp_47, outer key:perfdata.olap_summary_logs.id, inner key:perfdata.olap_detail_logs.olap_summary_log_id, equal cond:eq(perfdata.olap_summary_logs.id, perfdata.olap_detail_logs.olap_summary_log_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|       ├─IndexJoin_77(Build)            | 448.75    | root      |                                                            | inner join, inner:TableReader_74, outer key:perfdata.olap_summary_logs.benchmark_id, inner key:perfdata.benchmarks.id, equal cond:eq(perfdata.olap_summary_logs.benchmark_id, perfdata.benchmarks.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|       │ ├─TableReader_85(Build)        | 359.00    | root      |                                                            | data:TableFullScan_84                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|       │ │ └─TableFullScan_84           | 359.00    | cop[tikv] | table:s                                                    | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|       │ └─TableReader_74(Probe)        | 1.00      | root      |                                                            | data:TableRangeScan_73                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|       │   └─TableRangeScan_73          | 1.00      | cop[tikv] | table:b                                                    | range: decided by [perfdata.olap_summary_logs.benchmark_id], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|       └─IndexLookUp_47(Probe)          | 107.58    | root      |                                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|         ├─IndexRangeScan_45(Build)     | 107.58    | cop[tikv] | table:d, index:idx_olap_detail_logs_1(olap_summary_log_id) | range: decided by [eq(perfdata.olap_detail_logs.olap_summary_log_id, perfdata.olap_summary_logs.id)], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|         └─TableRowIDScan_46(Probe)     | 107.58    | cop[tikv] | table:d                                                    | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------+-----------+-----------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.05 sec)

And the expected plan should be like this:

perfdata>explain analyze
    -> with nightly_data as(
    ->     select s.id,
    ->         b.id benchmark_id,
    ->         b.name benchmark_name,
    ->         d.name query_name,
    ->         d.plan,
    ->         d.plan_digest
    ->     from olap_summary_logs s,
    ->         olap_detail_logs d,
    ->         benchmarks b
    ->     where s.id = d.olap_summary_log_id
    ->         and s.benchmark_id = b.id
    ->         and s.id = 420015 -- summary_log_id
    ->         and d.name = '10C' -- query_name
    -> ),
    -> baseline_data as(
    ->     select s.benchmark_id,
    ->         d.name query_name,
    ->         d.plan,
    ->         d.plan_digest
    ->     from baseline_olap_summary_logs s,
    ->         baseline_olap_detail_logs d
    ->     where s.id = d.baseline_olap_summary_log_id
    ->     and d.name = '10C'
    -> )
    -> select distinct n.id olap_summary_log_id,
    ->     n.benchmark_id,
    ->     n.query_name,
    ->     n.plan_digest,
    ->     b.plan_digest baseline_plan_digest,
    ->     n.plan,
    ->     b.plan baseline_plan
    -> from baseline_data b
    ->     join nightly_data n on b.benchmark_id = n.benchmark_id
    ->     and b.query_name = n.query_name
    ->     and n.plan_digest <> b.plan_digest;
+------------------------------------------+---------+---------+-----------+------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                                       | estRows | actRows | task      | access object                                              | execution info                                                                                                                                                                                                                                                                                                                                                                      | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | memory    | disk    |
+------------------------------------------+---------+---------+-----------+------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashAgg_69                               | 1.00    | 24      | root      |                                                            | time:104ms, loops:6, partial_worker:{wall_time:103.039863ms, concurrency:5, task_num:1, tot_wait:502.514862ms, tot_exec:1.586515ms, tot_time:504.964579ms, max:102.939455ms, p95:102.939455ms}, final_worker:{wall_time:103.983879ms, concurrency:5, task_num:5, tot_wait:514.800333ms, tot_exec:2.977676ms, tot_time:517.784399ms, max:103.969158ms, p95:103.969158ms}             | group by:perfdata.baseline_olap_detail_logs.plan, perfdata.baseline_olap_detail_logs.plan_digest, perfdata.benchmarks.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest, perfdata.olap_summary_logs.id, funcs:firstrow(perfdata.olap_summary_logs.id)->perfdata.olap_summary_logs.id, funcs:firstrow(perfdata.benchmarks.id)->perfdata.benchmarks.id, funcs:firstrow(perfdata.olap_detail_logs.name)->perfdata.olap_detail_logs.name, funcs:firstrow(perfdata.olap_detail_logs.plan_digest)->perfdata.olap_detail_logs.plan_digest, funcs:firstrow(perfdata.baseline_olap_detail_logs.plan_digest)->perfdata.baseline_olap_detail_logs.plan_digest, funcs:firstrow(perfdata.olap_detail_logs.plan)->perfdata.olap_detail_logs.plan, funcs:firstrow(perfdata.baseline_olap_detail_logs.plan)->perfdata.baseline_olap_detail_logs.plan | 6.17 MB   | N/A     |
| └─Projection_70                          | 0.00    | 24      | root      |                                                            | time:100.5ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                              | perfdata.baseline_olap_detail_logs.plan, perfdata.baseline_olap_detail_logs.plan_digest, perfdata.olap_summary_logs.id, perfdata.benchmarks.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 1.04 MB   | N/A     |
|   └─HashJoin_72                          | 0.00    | 24      | root      |                                                            | time:100.5ms, loops:2, build_hash_table:{total:22.6ms, fetch:22.6ms, build:5.77µs}, probe:{concurrency:5, total:496.9ms, max:100.3ms, probe:1.24ms, fetch:495.6ms}                                                                                                                                                                                                                  | inner join, equal:[eq(perfdata.benchmarks.id, perfdata.baseline_olap_summary_logs.benchmark_id) eq(perfdata.olap_detail_logs.name, perfdata.baseline_olap_detail_logs.name)], other cond:ne(perfdata.olap_detail_logs.plan_digest, perfdata.baseline_olap_detail_logs.plan_digest)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | 161.4 KB  | 0 Bytes |
|     ├─Selection_73(Build)                | 0.00    | 3       | root      |                                                            | time:22.5ms, loops:2                                                                                                                                                                                                                                                                                                                                                                | not(isnull(perfdata.benchmarks.id)), not(isnull(perfdata.olap_detail_logs.name)), not(isnull(perfdata.olap_detail_logs.plan_digest))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | 81 KB     | N/A     |
|     │ └─CTEFullScan_74                   | 0.00    | 3       | root      | CTE:n                                                      | time:22.4ms, loops:3                                                                                                                                                                                                                                                                                                                                                                | data:CTE_0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | 181.9 KB  | 0 Bytes |
|     └─Selection_75(Probe)                | 8.00    | 8       | root      |                                                            | time:99.1ms, loops:2                                                                                                                                                                                                                                                                                                                                                                | not(isnull(perfdata.baseline_olap_detail_logs.name)), not(isnull(perfdata.baseline_olap_detail_logs.plan_digest)), not(isnull(perfdata.baseline_olap_summary_logs.benchmark_id))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 153.0 KB  | N/A     |
|       └─CTEFullScan_76                   | 10.00   | 8       | root      | CTE:b                                                      | time:99ms, loops:3                                                                                                                                                                                                                                                                                                                                                                  | data:CTE_1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | 355.9 KB  | 0 Bytes |
| CTE_0                                    | 0.00    | 3       | root      |                                                            | time:22.4ms, loops:3                                                                                                                                                                                                                                                                                                                                                                | Non-Recursive CTE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 181.9 KB  | 0 Bytes |
| └─Projection_42(Seed Part)               | 0.00    | 3       | root      |                                                            | time:22.4ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                               | perfdata.olap_summary_logs.id, perfdata.benchmarks.id, perfdata.benchmarks.name, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | 90.9 KB   | N/A     |
|   └─Projection_43                        | 0.00    | 3       | root      |                                                            | time:22.4ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                               | perfdata.olap_summary_logs.id, perfdata.olap_detail_logs.name, perfdata.olap_detail_logs.plan, perfdata.olap_detail_logs.plan_digest, perfdata.benchmarks.id, perfdata.benchmarks.name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | 90.9 KB   | N/A     |
|     └─HashJoin_45                        | 0.00    | 3       | root      |                                                            | time:22.4ms, loops:2, build_hash_table:{total:22.3ms, fetch:22.3ms, build:3.23µs}, probe:{concurrency:5, total:111.3ms, max:22.3ms, probe:54µs, fetch:111.2ms}                                                                                                                                                                                                                      | CARTESIAN inner join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | 136.9 KB  | 0 Bytes |
|       ├─IndexLookUp_52(Build)            | 0.00    | 3       | root      |                                                            | time:22.1ms, loops:2, index_task: {total_time: 1.36ms, fetch_handle: 1.35ms, build: 1.1µs, wait: 2.84µs}, table_task: {total_time: 27.6ms, num: 1, concurrency: 5}                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 101.6 KB  | N/A     |
|       │ ├─IndexRangeScan_49(Build)       | 0.00    | 232     | cop[tikv] | table:d, index:idx_olap_detail_logs_1(olap_summary_log_id) | time:1.34ms, loops:3, cop_task: {num: 1, max: 1.29ms, proc_keys: 232, rpc_num: 1, rpc_time: 1.28ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:4}, scan_detail: {total_process_keys: 232, total_keys: 233, rocksdb: {delete_skipped_count: 0, key_skipped_count: 232, block: {cache_hit_count: 7, read_count: 2, read_byte: 19.3 KB}}}                                  | range:[420015,420015], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A       | N/A     |
|       │ └─Selection_51(Probe)            | 0.00    | 3       | cop[tikv] |                                                            | time:20.1ms, loops:2, cop_task: {num: 1, max: 20ms, proc_keys: 232, tot_proc: 19ms, tot_wait: 1ms, rpc_num: 1, rpc_time: 20ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:19ms, loops:4}, scan_detail: {total_process_keys: 232, total_keys: 233, rocksdb: {delete_skipped_count: 0, key_skipped_count: 463, block: {cache_hit_count: 13, read_count: 108, read_byte: 639.1 KB}}} | eq(perfdata.olap_detail_logs.name, "10C")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A       | N/A     |
|       │   └─TableRowIDScan_50            | 0.00    | 232     | cop[tikv] | table:d                                                    | tikv_task:{time:19ms, loops:4}                                                                                                                                                                                                                                                                                                                                                      | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A       | N/A     |
|       └─IndexJoin_57(Probe)              | 1.25    | 1       | root      |                                                            | time:10µs, loops:2, inner:{total:1.27ms, concurrency:5, task:1, construct:7.73µs, fetch:1.26ms, build:1.81µs}, probe:2.7µs                                                                                                                                                                                                                                                          | inner join, inner:TableReader_54, outer key:perfdata.olap_summary_logs.benchmark_id, inner key:perfdata.benchmarks.id, equal cond:eq(perfdata.olap_summary_logs.benchmark_id, perfdata.benchmarks.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 41.1 KB   | N/A     |
|         ├─Point_Get_64(Build)            | 1.00    | 1       | root      | table:olap_summary_logs                                    | time:497.3µs, loops:3, Get:{num_rpc:1, total_time:467.6µs}, scan_detail: {total_process_keys: 1, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 6, read_count: 1, read_byte: 15.0 KB}}}                                                                                                                                           | handle:420015                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | N/A       | N/A     |
|         └─TableReader_54(Probe)          | 1.00    | 1       | root      |                                                            | time:1.22ms, loops:2, cop_task: {num: 1, max: 1.15ms, proc_keys: 1, rpc_num: 1, rpc_time: 1.14ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                       | data:TableRangeScan_53                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A       | N/A     |
|           └─TableRangeScan_53            | 1.00    | 1       | cop[tikv] | table:b                                                    | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 8, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                         | range: decided by [perfdata.olap_summary_logs.benchmark_id], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | N/A       | N/A     |
| CTE_1                                    | 10.00   | 8       | root      |                                                            | time:99ms, loops:3                                                                                                                                                                                                                                                                                                                                                                  | Non-Recursive CTE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 355.9 KB  | 0 Bytes |
| └─HashJoin_33(Seed Part)                 | 10.00   | 8       | root      |                                                            | time:98.9ms, loops:2, build_hash_table:{total:190.1µs, fetch:180.7µs, build:9.42µs}, probe:{concurrency:5, total:493.5ms, max:98.8ms, probe:132.6µs, fetch:493.4ms}                                                                                                                                                                                                                 | inner join, equal:[eq(perfdata.baseline_olap_summary_logs.id, perfdata.baseline_olap_detail_logs.baseline_olap_summary_log_id)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 16.5 KB   | 0 Bytes |
|   ├─TableReader_38(Build)                | 6.00    | 6       | root      |                                                            | time:30.2µs, loops:2, cop_task: {num: 1, max: 703.2µs, proc_keys: 6, rpc_num: 1, rpc_time: 693.5µs, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                     | data:TableFullScan_37                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | 304 Bytes | N/A     |
|   │ └─TableFullScan_37                   | 6.00    | 6       | cop[tikv] | table:s                                                    | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 6, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 6, block: {cache_hit_count: 6, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                         | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A       | N/A     |
|   └─TableReader_36(Probe)                | 8.00    | 8       | root      |                                                            | time:98.7ms, loops:2, cop_task: {num: 1, max: 98.9ms, proc_keys: 1943, tot_proc: 98ms, rpc_num: 1, rpc_time: 98.9ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                    | data:Selection_35                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | 147.6 KB  | N/A     |
|     └─Selection_35                       | 8.00    | 8       | cop[tikv] |                                                            | tikv_task:{time:91ms, loops:6}, scan_detail: {total_process_keys: 1943, total_keys: 1944, rocksdb: {delete_skipped_count: 0, key_skipped_count: 3885, block: {cache_hit_count: 816, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                            | eq(perfdata.baseline_olap_detail_logs.name, "10C")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | N/A       | N/A     |
|       └─TableFullScan_34                 | 1943.00 | 1943    | cop[tikv] | table:d                                                    | tikv_task:{time:91ms, loops:6}                                                                                                                                                                                                                                                                                                                                                      | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A       | N/A     |
+------------------------------------------+---------+---------+-----------+------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
26 rows in set (0.17 sec)

Therefore, the Join Predicate Pushdown should be implemented for CTE to improve the performance and reduce OOM in TiDB.

[wjhuang2016]

I'm doing some research to optimize CTE(both non-recursive and recursive). For now, I'm planning to implement predicate-pushdown, to make TPC-DS work with all CTE queries.

[Yui-Song]

I'm doing some research to optimize CTE(both non-recursive and recursive). For now, I'm planning to implement predicate-pushdown, to make TPC-DS work with all CTE queries.

Oh, can't wait to see it! I'm a big fan of CTE and just fed up with the OOM things. I have reported two OOM issues this week.

[wjhuang2016]

According to your SQL, I think inlining is more efficient since the CTE is used only once.

[Yui-Song]

According to your SQL, I think inlining is more efficient since the CTE is used only once.
Yes, maybe using in-line view in this case is more efficient. However, the user shouldn't get an OOM when they try to use CTE, right?

[wjhuang2016]

According to your SQL, I think inlining is more efficient since the CTE is used only once.
Yes, maybe using in-line view in this case is more efficient. However, the user shouldn't get an OOM when they try to use CTE, right?

I mean that we can use inling to implement CTE instead of materialization.

[Yui-Song]

According to your SQL, I think inlining is more efficient since the CTE is used only once.
Yes, maybe using in-line view in this case is more efficient. However, the user shouldn't get an OOM when they try to use CTE, right?

I mean that we can use inline to implement CTE instead of materialization.

Oh, Oracle can decide to use inlining or materialization according to the cost. Maybe we can implement something like this.