Reduce performance issues caused by `Build Plan`

[KKould]

Feature Request

I try to perform this case in a table with 200,000 rows: select * from t1 where c1 > 500 and c1 < 1000, and use pprof-rs to generate a flame graph The most obvious one is StatisticMetaLoader::load, which will then be cached, and then I generate a flame graph after loading. In the new flame graph, HepOptimizer::find_best is a relatively large performance bottleneck.

[KKould]

2_00_000 row on case: select * from t1 where c1 > 500 and c1 < 1000 2024/3/29

[KKould]

let fnck_sql = DataBaseBuilder::path("./fncksql_bench")
    .build()
    .await?;
let guard = pprof::ProfilerGuard::new(100).unwrap();

for _ in 0..10_000 {
    let _ = fnck_sql.run("select * from t1 where c1 > 500 and c1 < 1000").await?;
}
if let Ok(report) = guard.report().build() {
    let file = File::create("./flamegraph.svg").unwrap();
    report.flamegraph(file).unwrap();
};

[KKould]

I ran the benchmark again, and this pr: https://github.com/KipData/FnckSQL/pull/193 made the performance of select * from t1 where c1 = 1000 basically the same as sqlite: sqlite: 20us, fncksql: 27us, but select * from t1 where c1 > 500 and c1 < 1000 is still very different: sqlite: 130us, fncksql: 500us,

FnckSQL: Full  Read by 'select * from t1'
                        time:   [117.76 ms 119.42 ms 121.73 ms]
                        change: [-8.4606% -6.8487% -4.7828%] (p = 0.00 < 0.05)
                        Performance has improved.
Found 1 outliers among 10 measurements (10.00%)
  1 (10.00%) high mild

SQLite: Full  Read by 'select * from t1'
                        time:   [39.378 ms 40.132 ms 41.577 ms]
                        change: [-16.732% -14.192% -11.760%] (p = 0.00 < 0.05)
                        Performance has improved.

FnckSQL: Point Read by 'select * from t1 where c1 = 1000'
                        time:   [27.418 µs 27.535 µs 27.640 µs]
                        change: [-15.660% -14.844% -14.174%] (p = 0.00 < 0.05)
                        Performance has improved.
Found 1 outliers among 10 measurements (10.00%)
  1 (10.00%) low mild

SQLite: Point Read by 'select * from t1 where c1 = 1000'
                        time:   [20.427 µs 20.501 µs 20.602 µs]
                        change: [-10.885% -10.317% -9.7520%] (p = 0.00 < 0.05)
                        Performance has improved.
Found 1 outliers among 10 measurements (10.00%)
  1 (10.00%) high mild

FnckSQL: Range Read by 'select * from t1 where c1 > 500 and c1 < 1000'
                        time:   [499.18 µs 500.34 µs 501.96 µs]
                        change: [-12.385% -11.399% -10.521%] (p = 0.00 < 0.05)
                        Performance has improved.

SQLite: Range Read by 'select * from t1 where c1 > 500 and c1 < 1000'
                        time:   [129.42 µs 129.59 µs 129.80 µs]
                        change: [-7.2815% -6.8357% -6.2602%] (p = 0.00 < 0.05)
                        Performance has improved.
Found 1 outliers among 10 measurements (10.00%)
  1 (10.00%) high severe