Hello Pinot team! Just had a question on inverted ...
# troubleshooting
s
Hello Pinot team! Just had a question on inverted index. Reading the doc on inverted index and referencing the following line from the doc:
Copy code
As explained in the forward index section, a column that is both sorted and equipped with a dictionary is encoded in a specialized manner that serves the purpose of implementing both forward and inverted indexes. Consequently, when these conditions are met, an inverted index is effectively created without additional configuration, even if the configuration suggests otherwise.
We have a column that is dictionary enabled and configured as sorted in realtime table. We have also set
autoGeneratedInvertedIndex
and
createInvertedIndexDuringSegmentGeneration
as
true
in the table config. However we are not seeing inverted index being used in the explain plan query. Would inverted index also be configured in the table config to see it in effect?
For more context, we have updated the config recently so that the column is
sorted
and
autoGeneratedInvertedIndex
and
createInvertedIndexDuringSegmentGeneration
is
true
m
The data itself is sorted and acts as an index (for binary search). Can you share the explain plan?
thanks 1
s
Copy code
Execution Plan
PinotLogicalAggregate(group=[{}], agg#0=[COUNT($0)])
  PinotLogicalExchange(distribution=[hash])
    PinotLogicalAggregate(group=[{}], agg#0=[COUNT()])
      LogicalFilter(condition=[AND(=($9, 4), =($10, 1523255), =($24, 53), >=($8, 20250827), <=($8, 20250828))])
        LogicalTableScan(table=[[default, table_REALTIME]])
This is the explain plan that we get when filtering on a column that is sorted and dictionary enabled
m
Can you run a query simple query like below and share the response metadata
Copy code
select count(*) from <table> where <sortedColumn> = <xxx>
s
Copy code
Execution Plan
PinotLogicalAggregate(group=[{}], agg#0=[COUNT($0)])
  PinotLogicalExchange(distribution=[hash])
    PinotLogicalAggregate(group=[{}], agg#0=[COUNT()])
      LogicalFilter(condition=[=($24, 53)])
        LogicalTableScan(table=[[default, unified_events_v2_REALTIME]])
m
no no, just run the query, not explain plan
s
Gotcha!
Copy code
{
  "requestId": "604985615000334402",
  "brokerId": "x",
  "offlineSystemActivitiesCpuTimeNs": 0,
  "realtimeSystemActivitiesCpuTimeNs": 0,
  "offlineThreadCpuTimeNs": 0,
  "realtimeThreadCpuTimeNs": 0,
  "offlineResponseSerializationCpuTimeNs": 0,
  "realtimeResponseSerializationCpuTimeNs": 0,
  "offlineTotalCpuTimeNs": 0,
  "realtimeTotalCpuTimeNs": 0,
  "numSegmentsPrunedByBroker": 0,
  "numSegmentsPrunedByServer": 48,
  "numSegmentsPrunedInvalid": 0,
  "numSegmentsPrunedByLimit": 0,
  "numSegmentsPrunedByValue": 0,
  "explainPlanNumEmptyFilterSegments": 0,
  "explainPlanNumMatchAllFilterSegments": 0,
  "numServersQueried": 4,
  "numServersResponded": 4,
  "numDocsScanned": 17416655,
  "numEntriesScannedInFilter": 1610336252,
  "numEntriesScannedPostFilter": 0,
  "numSegmentsQueried": 9958,
  "numSegmentsProcessed": 9910,
  "numSegmentsMatched": 9910,
  "numConsumingSegmentsQueried": 48,
  "numConsumingSegmentsProcessed": 0,
  "numConsumingSegmentsMatched": 0,
  "minConsumingFreshnessTimeMs": 1756455179799,
  "totalDocs": 1654179622,
  "numGroupsLimitReached": false,
  "timeUsedMs": 1908,
  "numRowsResultSet": 1,
  "segmentStatistics": [],
  "traceInfo": {},
  "brokerReduceTimeMs": 0,
  "exceptions": [],
  "resultTable": {
    "rows": [
      [
        x
      ]
    ],
    "dataSchema": {
      "columnNames": [
        "count(*)"
      ],
      "columnDataTypes": [
        "LONG"
      ]
    }
  },
  "maxRowsInJoinReached": null,
  "maxRowsInOperator": null,
  "maxRowsInWindowReached": null,
  "stageStats": null,
  "partialResult": false,
  "resultObjects": [
    {
      "count(*)": x
    }
  ]
}
m
This tells me that data is not sorted:
Copy code
"numEntriesScannedInFilter": 1610336252,
This should be 0
s
Interesting! For realtime table, when the column is set to sorted does this not sort the values by default?
m
It does
s
After a segment reload
m
No reload probably wont' sort
s
So the column was actually not set to sort upon table creation -> ingested data -> set config to sorted
would this sort the column for the newly generated realtime segments?
but leave the previous segments unsorted?
m
In the flow above, only new segments will end up having the sorted column.