Support using index in backfilling

[hzxa21]

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

basically if we are creating a sink from A to B, and we know B or B's downstream prefer the backfilling rows to be ordered by X, we better add ORDER BY X to A if A is a MV, or create a new MV A' as SELECT .. from A ORDER BY X and create the sink from A'

Sometimes we need backfilling to emit rows in a specific ordering to reduce unnecessary downstream changes. Currently we can work around it by creating a MV with ORDER BY and use this MV for backfilling but this MV cannot be dropped after backfilling because it exists in the streaming graph. Instead, using index for backfilling seems more flexible.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[xxchan]

This was also once mentioned here https://github.com/risingwavelabs/risingwave/issues/12824#issuecomment-1776519122