Segment Pruning is not happening when filtering transformed timecolumn
- 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
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