pingcap/tidb

Support inline projection for executors

Open

#14,428 opened on 2020年1月9日

GitHub で見る
 (28 comments) (4 reactions) (2 assignees)Go (40,090 stars) (6,186 forks)batch import
challenge-programhelp wantedsig/executionsig/plannertype/enhancement

説明

Description

Is your feature request related to a problem? Please describe:

For example,

select t1.b+1 from t1, t2 where t1.a = t2.a

Join executor reads t1.a and t1.b from table t1, t2.a from table t2, then concatenates and materilizes these three columns. Next step, projection executor projects three columns to one column of t1.b.

Describe the feature you'd like:

We can optimize it by set the output schema of Join executor as only t1.b, so that the executor do know that it only need to output t1.b. It can avoid copying t1.a and t2.a which are not needed by its parent when materilizes/copys data buffers.

Inline Projection

Here's a demostration PR to show how the performance we gain from avoiding copying extra columns for SORT executor.

What we need to do:

Executor Why need this
Sort Copy rows with random access
TopN Copy rows with random access
Join (Hash Join, Merge Join, Index Merge Join, Index Hash Join) Copy rows with random access from build table, join rows from one of the build table and one of the probe table
Apply Copy rows with random access from build table, join rows from one of the build table and one of the probe table
Selection Copy rows with random access
Limit? Prune copying of extra columns
  • Support inline projection in planner @SunRunAway #14683 #14791
  • Implement inline projection for Sort and TopN (750 score) @hidehalo #19900 doing
  • Join
    • Implement inline projection for Hash Join @SunRunAway #14682 #14783
    • Implement inline projection for Merge Join @lance6716 #15463
    • Implement inline projection for Index Merge Join #15004
    • Implement inline projection for Index Hash Join #15004
  • Implement inline projection for Apply (750 score)
  • Implement inline projection Selection (750 score) @pengdaqian2020 #20335 doing
  • Implement inline projection for Limit (750 score) @pingyu https://github.com/pingcap/tidb/pull/20288

Difficulty

  • Medium

Score

  • 3000

Mentor(s)

  • @SunRunAway
  • @pingyu

Recommended Skills

  • SQL Optimization
  • Golang Profiling
  • Code Refactoring

コントリビューターガイド

技術スタック
gosql
領域
databaseperformance
Issue 種別
feature
難度新しい貢献者にとっての実装難度です。1 は非常に小さな変更、5 は専門的な作業です。
3
推定時間経験ある貢献者が調査、実装、テスト、pull request 準備にかけるおおよその時間範囲です。
over 1 week
活動状況Issue が今どれだけ取り組みやすいかを示します。新しい、活発、古い、ブロック中、またはメンテナー入力待ちなどです。
active
明確さ期待される変更、受け入れ条件、次の手順がどれだけ明確かを示します。
clear
前提条件
Go programmingSQL query executionProfiling tools
初心者向け度初回貢献者にどれだけ取り組みやすいかを 1-100 で推定したスコアです。
20
調査方針
Review the demonstration PR #14353 to understand the optimization pattern. Examine implemented examples like Hash Join (#14682) and Limit (#20288). For a specific uncompleted subtask like Apply or Selection, study the executor's code to identify where columns are copied unnecessarily and propose modifications to prune non needed columns from output schema. Engage with mentors @SunRunAway and @pingyu for guidance.