Is your feature request related to a problem or challenge?

Consider we have huge data source consists of many record batches.
Now it's impossible to get last recent N rows without full scan:

SELECT * FROM Events
ORDER BE event_time DESC
LIMIT 1000

The query above will do full scan from the starting row. But we can avoid it if we let Provider know to perform scanning from the end to start.

It the example TableProvider may know that it needed to provide only last record batch (or the latest parquet file from folder).

Describe the solution you'd like

Now we have filter and limit in TableProvider::scan:

async fn scan(
        &self,
        state: &SessionState,
        projection: Option<&Vec<usize>>,
        // filters and limit can be used here to inject some push-down operations if needed
        filters: &[Expr],
        limit: Option<usize>,
    ) -> Result<Arc<dyn ExecutionPlan>> {

Let's add SortExpression as well to push it down or just consider:

async fn scan(
        &self,
        state: &SessionState,
        projection: Option<&Vec<usize>>,
        // filters and limit can be used here to inject some push-down operations if needed
        filters: &[Expr],
        // sort expression
        expr: &[PhysicalSortExpr],
        limit: Option<usize>,
    ) -> Result<Arc<dyn ExecutionPlan>> {

Describe alternatives you've considered

Also we can add method with_sorting(self: Arc<Self>, ... ) to the trait ExecutionPlan and add an ability of pushing down sorting during optimization phase.

But I think that sorting is something fundamental so it's better to add it to TableProvider::scan

Additional context

No response