I am seeing a strange query perf issue: the follow...
# troubleshootinga
Ashish
07/30/2022, 8:42 PMI am seeing a strange query perf issue: the following query should be blazingly fast - instead it is taking 10seconds +:
select distinct($segmentName) from <table_name> where ts > 1659083681436 AND ts < 1659093681436 limit 200
Given that broker already has segment pruning logic based on ts, I am not sure and ts column has a range index (no dictionary), I am confused why this query is so slow.
k
Kishore G
07/30/2022, 8:44 PM
please share the response metadata
a
Ashish
07/30/2022, 8:44 PM“numServersQueried”: 2,
“numServersResponded”: 2,
“numSegmentsQueried”: 177,
“numSegmentsProcessed”: 175,
“numSegmentsMatched”: 172,
“numConsumingSegmentsQueried”: 2,
“numDocsScanned”: 1357938528,
“numEntriesScannedInFilter”: 1452135948,
“numEntriesScannedPostFilter”: 1357938528,
“numGroupsLimitReached”: false,
“totalDocs”: 1458245948,
“timeUsedMs”: 10748,
“offlineThreadCpuTimeNs”: 0,
“realtimeThreadCpuTimeNs”: 0,
“offlineSystemActivitiesCpuTimeNs”: 0,
“realtimeSystemActivitiesCpuTimeNs”: 0,
“offlineResponseSerializationCpuTimeNs”: 0,
“realtimeResponseSerializationCpuTimeNs”: 0,
“offlineTotalCpuTimeNs”: 0,
“realtimeTotalCpuTimeNs”: 0,
“segmentStatistics”: [],
“traceInfo”: {},
“minConsumingFreshnessTimeMs”: 1659209145425,
“numRowsResultSet”: 172
Ashish
07/30/2022, 8:46 PM“rows”: [
[
“BROKER_REDUCE(limit:200)“,
0,
-1
],
[
“COMBINE_DISTINCT”,
1,
0
],
[
“DISTINCT(keyColumns:$segmentName)“,
2,
1
],
[
“TRANSFORM_PASSTHROUGH($segmentName)“,
3,
2
],
[
“PROJECT($segmentName)“,
4,
3
],
[
“FILTER_FULL_SCAN(operatorRANGE,predicate(ts > ‘1659083681436’ AND ts < ‘1659093681436’))“,
5,
4
]
]
k
Kishore G
07/30/2022, 8:49 PM
“numDocsScanned”: 1357938528,
“numEntriesScannedInFilter”: 1452135948,
“numEntriesScannedPostFilter”: 1357938528,
“numGroupsLimitReached”: false,
“totalDocs”: 1458245948,
Kishore G
07/30/2022, 8:50 PM
its matching the entire data
a
Ashish
07/30/2022, 8:52 PMYes, I understand that but with group by or distinct executor and $segmentName virtualcolumn having a single value per segment, it shouldn’t need to iterator over all the docs. No?
k
Kishore G
07/30/2022, 8:58 PM
yeah, we have some of that optimization actual columns but looks like its not triggered for virtual columns..
a
Ashish
07/30/2022, 8:58 PMI tried the same query with physical column, same issue. It is showing FILTER_FULL_SCAN even with range index on ts.
k
Kishore G
07/30/2022, 8:59 PM
what version of Pinot are you using
a
Ashish
07/30/2022, 8:59 PM0.10.0
Ashish
07/30/2022, 9:04 PMthe physical column I am using has dictionary, sorted fwd index. So, not sure why it is not using SortedIndexBasedFilterOperator and but numDocsScannedInFilter is same as totalDocs.
k
Kishore G
07/30/2022, 9:05 PM
yeah, it does not make sense..
Kishore G
07/30/2022, 9:07 PM
can you please file a github issue
a
Ashish
07/30/2022, 9:15 PMok, will do