[jira] [Comment Edited] (CASSANDRA-20191) Optimize initial skipping logic for SAI queries on large partitions

["Sunil Ramchandra Pawar (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CASSANDRA-20191?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17948353#comment-17948353 ] 

Sunil Ramchandra Pawar edited comment on CASSANDRA-20191 at 4/30/25 4:17 AM:
-----------------------------------------------------------------------------

Tested the performance using below test-case -
{code:java}
@Test
public void testNextKeyPerfClusteringIndexSliceFilter()
{
    createTable("CREATE TABLE %S (" +
                "pk int, " +
                "ck int, " +
                "val text, " +
                "PRIMARY KEY (pk, ck))");

    createIndex("CREATE CUSTOM INDEX ON %s(val) USING 'StorageAttachedIndex'");

    int pk = 1;
    for (int ck = 0; ck < 10000; ck++)
    {
        execute("INSERT INTO %s (pk, ck, val) VALUES (?, ?, ?)", pk, ck, "hello1");
    }

    int pk1 = 2;
    for (int ck = 0; ck < 100; ck++)
    {
        execute("INSERT INTO %s (pk, ck, val) VALUES (?, ?, ?)", pk1, ck, "hello2");
    }

    flush();

    Histogram histogram = new Histogram(4);


    for (int i = 0; i < 10000; i++)
    {
        long start = System.nanoTime();
        execute("SELECT * FROM %s WHERE val = 'hello1' AND ck > 9000 ALLOW FILTERING");
        histogram.recordValue(System.nanoTime() - start);

        if (i % 1000 == 0)
        {
            System.out.println("50th: " + histogram.getValueAtPercentile(0.5));
            System.out.println("95th: " + histogram.getValueAtPercentile(0.95));
            System.out.println("99th: " + histogram.getValueAtPercentile(0.99));
        }
    }

    // Functionality check
    assertRowCount(execute("SELECT * FROM %s WHERE val = 'hello1' AND ck > 9000 ALLOW FILTERING"),999);
} {code}
Below are the before-and-after results, and the flame graphs are attached.

 

 

Current performance -
||50th||95th||99th||
|722698239|722698239|722698239|

 

With changes - 
||50th||95th||99th||
|373456895|373456895|373456895|

 

Flame graphs - 
 # Our current code - [^test-histogram_without.html]
 # With changes - [^test-histogram_with_changes.html]


was (Author: novice_p):
Tested the performance using below test-case -

 

 
{code:java}
@Test
public void testNextKeyPerfClusteringIndexSliceFilter()
{
    createTable("CREATE TABLE %S (" +
                "pk int, " +
                "ck int, " +
                "val text, " +
                "PRIMARY KEY (pk, ck))");

    createIndex("CREATE CUSTOM INDEX ON %s(val) USING 'StorageAttachedIndex'");

    int pk = 1;
    for (int ck = 0; ck < 10000; ck++)
    {
        execute("INSERT INTO %s (pk, ck, val) VALUES (?, ?, ?)", pk, ck, "hello1");
    }

    int pk1 = 2;
    for (int ck = 0; ck < 100; ck++)
    {
        execute("INSERT INTO %s (pk, ck, val) VALUES (?, ?, ?)", pk1, ck, "hello2");
    }

    flush();

    Histogram histogram = new Histogram(4);


    for (int i = 0; i < 10000; i++)
    {
        long start = System.nanoTime();
        execute("SELECT * FROM %s WHERE val = 'hello1' AND ck > 9000 ALLOW FILTERING");
        histogram.recordValue(System.nanoTime() - start);

        if (i % 1000 == 0)
        {
            System.out.println("50th: " + histogram.getValueAtPercentile(0.5));
            System.out.println("95th: " + histogram.getValueAtPercentile(0.95));
            System.out.println("99th: " + histogram.getValueAtPercentile(0.99));
        }
    }

    // Functionality check
    assertRowCount(execute("SELECT * FROM %s WHERE val = 'hello1' AND ck > 9000 ALLOW FILTERING"),999);
} {code}

Below are the before-and-after results, and the flame graphs are attached.

 

 

Current performance -

 

 
||50th||95th||99th||
|722698239|722698239|722698239|

 

 

 
 
With changes - 
||50th||95th||99th||
|373456895|373456895|373456895|

 

Flame graphs - 
 # Our current code - [^test-histogram_without.html]
 # With changes - [^test-histogram_with_changes.html]

 

 

> Optimize initial skipping logic for SAI queries on large partitions
> -------------------------------------------------------------------
>
>                 Key: CASSANDRA-20191
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-20191
>             Project: Apache Cassandra
>          Issue Type: Improvement
>          Components: Feature/SAI
>            Reporter: Caleb Rackliffe
>            Assignee: Sunil Ramchandra Pawar
>            Priority: Normal
>             Fix For: 5.0.x, 5.x
>
>         Attachments: test-histogram_with_changes.html, test-histogram_without.html
>
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> In {{StorageAttachedIndexSearcher.ResultRetriever}}, we skip all postings lists produced by queries on individual column indexes to the first partition in the provided {{DataRanges}}. This is an obvious and useful step, but for queries on large partitions, it might not go as far as it could. If paging or user restrictions on the clustering key space are in play, it is probably suboptimal to not even attempt to skip the postings, which are row-addressable, to the first row after the clustering prefix in the (likely single) {{DataRange}}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cassandra.apache.org
For additional commands, e-mail: commits-help@cassandra.apache.org