[Feature] Optimize read table with limit.

[wwj6591812]

Search before asking

• I searched in the issues and found nothing similar.

Motivation

一、Problem Background
In our company's Flink Session-based OLAP environment, we observed that a very basic Flink SQL query against a Paimon table with a LIMIT clause took an unexpectedly long time—over 20 seconds—to complete.
（1）The query is as follows:
select * from paimon_na61.sample.s_holo_mainse_rank_xfc_all_features_swift_parsed limit 10;

（2）The table location is :
http://bs.alibaba-inc.com/fsutil?path=dfs%3A//na61dfsalake1--cn-zhangjiakou/alake/omega_na61/sample.db/s_holo_mainse_rank_xfc_all_features_swift_parsed/&isdir=true

二、Root Cause Analysis
Through code review and by attaching to the live Java process with Arthas, we identified the following:
1、The primary bottleneck is the SnapshotReaderImpl#generateSplits method.
（1）We only need select 10 record, but the List is too large.

We found the number of files observed in the manifests system table is also consistent with this large scale.

（2）The main time cost:

http://ha3.oss-cn-hangzhou-zmf.aliyuncs.com/052894/paimon_sample_table_slow.arthas.html

2、Upon further investigation, we found that when executing a SELECT query, the current implementation of AbstractFileStoreScan#plan does not take the LIMIT clause from the SQL into account. This can cause the splits variable (List) within SnapshotReaderImpl#read() to become excessively large. Consequently, when the table consists of a large number of files, SnapshotReaderImpl#generateSplits takes a very long time to execute.

三、Conclusion and Plan
To resolve this, we propose to push the LIMIT clause down into the AbstractFileStoreScan logic.
We plan to create two separate Pull Requests (PRs) for this effort: one to support Append-only tables and another for Primary Key (PK) tables. Since the logic for PK tables is significantly more complex than for Append-only tables, we will implement support for Append-only tables first.

Solution

No response

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!