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)
