Evgeny Stanilovsky created IGNITE-24718: -------------------------------------------
Summary: Sensible performance drop on queries with range scan if index scan is involved Key: IGNITE-24718 URL: https://issues.apache.org/jira/browse/IGNITE-24718 Project: Ignite Issue Type: Task Components: general Affects Versions: 3.0 Reporter: Evgeny Stanilovsky SqlIndexScanBenchmark highlights (a strange for me) that sorted index scan with range work slower by comparison with table scan. A bit additional context : benchmark initially fill table with incrementally grow DATE rows (val column): {noformat} LocalDate.of(1970, 1, 1) LocalDate.of(1970, 1, 2) {noformat} and so on ... further, build sorted index with only this DATE involved field : {noformat} CREATE INDEX test_val_idx ON test(val) {noformat} and queries through table and index scan: {noformat} SELECT * FROM test WHERE val >= LocalDate.of(1970, 1, 1) LIMIT ? {noformat} where limit is a variable bench param, i expect to obtain smth. improvement in table vs index scan, but for such a case - table scan is always wins, is it expected ? Concrete described case is a kinda of degenerate case - all rows are involved into scan, predicate is always TRUE for every next row, thus: selectivity = 1 and highlights the performance difference in table vs index scan usages. {noformat} Benchmark (fsync) (limit) (scanMode) Mode Cnt Score Error Units SqlIndexScanBenchmark.forceIndexScan false 1000 FIRST_N avgt 10 3.950 ± 0.707 ms/op SqlIndexScanBenchmark.forceIndexScan false 10000 FIRST_N avgt 10 43.260 ± 11.955 ms/op SqlIndexScanBenchmark.forceIndexScan false 100000 FIRST_N avgt 10 296.725 ± 18.029 ms/op SqlIndexScanBenchmark.forceTableScan false 1000 FIRST_N avgt 10 1.654 ± 0.079 ms/op SqlIndexScanBenchmark.forceTableScan false 10000 FIRST_N avgt 10 15.195 ± 10.048 ms/op SqlIndexScanBenchmark.forceTableScan false 100000 FIRST_N avgt 10 123.513 ± 14.146 ms/op {noformat} config : WITH STORAGE_PROFILES='default', replicas = 1, partitions = 1 -- This message was sent by Atlassian Jira (v8.20.10#820010)