[jira] [Created] (IGNITE-24718) Sensible performance drop on queries with range scan if index scan is involved

["Evgeny Stanilovsky (Jira)" <ji...@apache.org>]

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)