Support inline projection for executors

[SunRunAway]

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.

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

• [x] Support inline projection in planner @SunRunAway #14683 #14791
• [ ] Implement inline projection for Sort and TopN (750 score) @hidehalo #19900 doing
• Join
  • [x] Implement inline projection for Hash Join @SunRunAway #14682 #14783
  • [x] Implement inline projection for Merge Join @lance6716 #15463
  • [x] Implement inline projection for Index Merge Join #15004
  • [x] Implement inline projection for Index Hash Join #15004
• [ ] Implement inline projection for Apply (750 score)
• [ ] Implement inline projection Selection (750 score) @pengdaqian2020 #20335 doing
• [x] 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

[Reminiscent]

I want to do some executors for this.

[SunRunAway]

I want to do some executors for this.

Thanks, I've been investigating inline projection for planner and join parts(#14428). I hope I can finish them in these two days, so that you guys can continue to do works for other executors.

[lance6716]

Hi, I want to help to implement this for Merge Join, is it available now? @Reminiscent @SunRunAway

[SunRunAway]

Hi, I want to help to implement this for Merge Join, is it available now? @Reminiscent @SunRunAway

Yes. That's great. Looking forward to your PR.

[lance6716]

Hi, I managed to pass tests in #15463, PTAL at your convenience.

I'm going to take Selection, but I didn't know what should I do. Seems that LogicalSelection has already did PruneColumns for its child, there's no reduntant columns which need to do inlineProjection. (BTW, I didn't find where schema of LogicalSelection is set, not familiar with these code 😄 )

[TszKitLo40]

/pick-up limit

[ti-challenge-bot[bot]]

Pick up success.

[TszKitLo40]

Maybe individual issue should be created for each item

[TszKitLo40]

/give-up

[ti-challenge-bot[bot]]

Give up success.

[hidehalo]

/pick-up Sort and TopN

[ti-challenge-bot[bot]]

Pick up success.

[ti-challenge-bot[bot]]

@hidehalo You did not submit PR within 7 days, so give up automatically.

[hidehalo]

/pick-up

[ti-challenge-bot[bot]]

Pick up success.

[hidehalo]

@SunRunAway PTAL #19900 👀

[pingyu]

/pick-up Limit

[ti-challenge-bot[bot]]

This issue already picked by hidehalo.

[ti-challenge-bot[bot]]

@hidehalo You did not submit PR within 7 days, so give up automatically.

[newcworld]

/pick-up

[ti-challenge-bot[bot]]

Pick up success.

[newcworld]

@SunRunAway I' confusing about the inline projection for Limit. Does it means

select t1.b+1 from t1 limit 3

?

btw

could you please give me more explaition about inline projection. I cannot find it in google.

[SunRunAway]

@SunRunAway I' confusing about the inline projection for Limit. Does it means

select t1.b+1 from t1 limit 3

?

btw

could you please give me more explaition about inline projection. I cannot find it in google.

That's almost right and the top Projection can also be pruned.

And here's a slide for introducing https://docs.google.com/presentation/d/1cjdMjKFMbmQdIaDvTKBHlT9TXcqGJUjgkeylrseTrx8/edit#slide=id.p

[ti-challenge-bot[bot]]

@pengdaqian2020 You did not submit PR within 7 days, so give up automatically.

[YangKian]

/pick-up Apply

[ti-challenge-bot[bot]]

The challenge program issue is already in the assign flow, so you cannot pick up this issue. But the current issue needs help, you can contact @SunRunAway to try to solve this issue together.

[SunRunAway]

@YangKian you can ignore the bot's message and you can still do this issue and get points if your pr is merged.

[YangKian]

@SunRunAway Thanks for reminding~ I'd like to have some try :P