Sorted index on a non-dictionary-encoded column results in full segment scan

[nizarhejazi]

Defining a sorted index on a non-dictionary-encoded column causes Pinot to fully scan the content of segments (no inverted index is utilized).

user table config:

      "noDictionaryColumns": [
        "id",
      ],
      "sortedColumn": [
        "id"
      ],

Example query:
EXPLAIN PLAN FOR select id, "home.state" from user where id IN ('some_id')

Output:

Created another user table with a sorted index defined over id which is dictionary-encoded.

user_id_dict_encoded table config:

      "noDictionaryColumns": [
      ],
      "sortedColumn": [
        "id"
      ],

Example query:
EXPLAIN PLAN FOR select id, "home.state" from user_id_dict_encoded where id IN ('some_id')

Output:

@mayankshriv found the root cause of the issue.

[klsince]

Thanks for the report. The planning logic for the IN clause in the query would hit this if-check to use sorted index for column only when it's sorted AND dictionary encoded.

We may consider to enforce dictionary encoding for column set in sortedColumn to make it more intuitive here.

[mayankshriv]

In case of realtime ingestion, the following happens:

• The column does get stored wtih no-dict (or raw values).
• The column is also sorted on raw values, and that is reflected in metadata as well.
• However, sorted raw values != sorted index, and this leads to full scan.

While this is a valid expected behavior, I recommend we should change the precedence. If user specifies sorted as well as no-dict in real-time table, it would be better to default to dictionary based sorted index, so as to avoid full scan. I recommend this because the penalty of full scan is much higher than dict vs no dict storage overhead.

[klsince]

FYI, currently, column encoding can't be changed (like from raw to dict encoding and vice versa) by reloading segments with the reload or reset swagger APIs.

There is an ongoing effort to extend the segment loading logic to change column encoding to fix such issues in future.

For now, to work around this issue, one need to re-ingested the data with the corrected tableConfig: either from upstream to REALTIME table, or move data from REALTIME table to an OFFLINE table with the RealtimeToOfflineSegment minion task.

[Jackie-Jiang]

I'd suggest throwing error (during table config validation) when the explicitly configured sorted columns is configured as no-dictionary column to avoid the confusion. We should make the exception message very clear on why sorted column should not be configured as no-dictionary