apache / apache/pinot

Segment Pruning is not happening when filtering transformed timecolumn

Open
#6,910 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

When filtering a transformed column, segment pruning is not happening, below query cost more than 2s to finish,
```
SELECT datetimeconvert(__time, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '30:MINUTES'),
COUNT(*)
FROM product_log
WHERE datetimeconvert(__time, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '30:MINUTES') >= 1620830760000
AND datetimeconvert(__time, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '30:MINUTES') < 1620917160000
AND method = 'DeviceInternalService.CheckDeviceInSameGroup'
AND container_name = 'whale-device'
AND error > '0'
GROUP BY datetimeconvert(__time, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '30:MINUTES')
ORDER BY COUNT(*) DESC
```
The query result is:
```
{
"resultTable": {
"dataSchema": {
"columnDataTypes": [
"LONG",
"LONG"
],
"columnNames": [
"datetimeconvert(__time,'1:MILLISECONDS:EPOCH','1:MILLISECONDS:EPOCH','30:MINUTES')",
"count(*)"
]
},
"rows": [
[
1620873000000,
180
],
[
1620869400000,
179
],
[
1620871200000,
178
],
[
1620894600000,
172
],
[
1620892800000,
166
],
[
1620874800000,
164
],
[
1620876600000,
163
],
[
1620896400000,
163
],
[
1620867600000,
162
],
[
1620885600000,
161
]
]
},
"exceptions": [],
"numServersQueried": 1,
"numServersResponded": 1,
"numSegmentsQueried": 41,
"numSegmentsProcessed": 41,
"numSegmentsMatched": 12,
"numConsumingSegmentsQueried": 3,
"numDocsScanned": 7706,
"numEntriesScannedInFilter": 195554753,
"numEntriesScannedPostFilter": 7706,
"numGroupsLimitReached": false,
"totalDocs": 165272282,
"timeUsedMs": 2335,
"segmentStatistics": [],
"traceInfo": {},
"minConsumingFreshnessTimeMs": 1620917392724
}
```
And if not using a transformed time column in filter, it will return in 647ms
query:
```
SELECT datetimeconvert(__time, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '30:MINUTES'),
COUNT(*)
FROM product_log
WHERE __time >= 1620830760000
AND __time < 1620917160000
AND method = 'DeviceInternalService.CheckDeviceInSameGroup'
AND container_name = 'whale-device'
AND error > '0'
GROUP BY datetimeconvert(__time, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '30:MINUTES')
ORDER BY COUNT(*) DESC
```
result is:
```
{
"resultTable": {
"dataSchema": {
"columnDataTypes": [
"LONG",
"LONG"
],
"columnNames": [
"datetimeconvert(__time,'1:MILLISECONDS:EPOCH','1:MILLISECONDS:EPOCH','30:MINUTES')",
"count(*)"
]
},
"rows": [
[
1620873000000,
180
],
[
1620869400000,
179
],
[
1620871200000,
178
],
[
1620894600000,
172
],
[
1620892800000,
166
],
[
1620874800000,
164
],
[
1620876600000,
163
],
[
1620896400000,
163
],
[
1620867600000,
162
],
[
1620865800000,
161
]
]
},
"exceptions": [],
"numServersQueried": 1,
"numServersResponded": 1,
"numSegmentsQueried": 41,
"numSegmentsProcessed": 12,
"numSegmentsMatched": 12,
"numConsumingSegmentsQueried": 3,
"numDocsScanned": 7770,
"numEntriesScannedInFilter": 68503679,
"numEntriesScannedPostFilter": 7770,
"numGroupsLimitReached": false,
"totalDocs": 165381107,
"timeUsedMs": 647,
"segmentStatistics": [],
"traceInfo": {},
"minConsumingFreshnessTimeMs": 1620917833431
}
```

Contributor guide

Open the contributing guide

Research direction

Run the two SQL queries in the issue and compare their segment-processing metrics and runtimes. Trace how the transformed __time predicate is handled by query planning and segment pruning. Done means the transformed filter prunes the irrelevant segments like the direct __time filter while preserving the reported results.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases, performance
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.