https://pinot.apache.org/ logo
Channels
Powered by
y

Yupeng Fu

12/01/2020, 9:25 PM
hey, any good way to optimize such query
Copy code
SELECT hour_start_timestamp_utc FROM downtime WHERE (secondsSinceEpoch > 1606247126) ORDER BY secondsSinceEpoch DESC, hour_start_timestamp_utc DESC LIMIT 1
it scans the past 1 week of data but return only 1 record. since the table is large,  it ends up scanning about 100 million records per query, and takes seconds query output is like
Copy code
{
  "selectionResults": {
    "columns": [
      "hour_start_timestamp_utc"
    ],
    "results": [
      [
        "2020-12-01 09:00:00"
      ]
    ]
  },
  "exceptions": [],
  "numServersQueried": 9,
  "numServersResponded": 9,
  "numSegmentsQueried": 1059,
  "numSegmentsProcessed": 1059,
  "numSegmentsMatched": 18,
  "numConsumingSegmentsQueried": 0,
  "numDocsScanned": 142101504,
  "numEntriesScannedInFilter": 0,
  "numEntriesScannedPostFilter": 284203008,
  "numGroupsLimitReached": false,
  "totalDocs": 7374174837,
  "timeUsedMs": 3522,
  "segmentStatistics": [],
  "traceInfo": {},
  "minConsumingFreshnessTimeMs": 0
}
m

Mayank

12/01/2020, 9:29 PM
Perhaps add another predicate on secondsSinceEpoch < xxx
That should allow for pruning more segments. If the code doesn't do that today, would be a good feature to add.
y

Yupeng Fu

12/01/2020, 9:30 PM
you mean the range indexing?
m

Mayank

12/01/2020, 9:30 PM
Well range based pruning (based on metadata)
so if minTime = a and maxTime = b (in metadata), then segments where querty is not between a and be can be pruned out
We do have a time segment pruner, I don't recall if it can handle ranges
y

Yupeng Fu

12/01/2020, 9:31 PM
got it
does it require to have both lower and upper bounds?
x

Xiang Fu

12/01/2020, 9:38 PM
use max(hour_start_timestamp_utc)?
k

Ken Krugler

12/01/2020, 9:38 PM
If the segments are time-based, and you set the table config’s columnMinMaxValueGeneratorMode to something that gave you min/max for the secondsSinceEpoch column, then would Pinot optimize out the segments that contain no possible rows?
m

Mayank

12/01/2020, 9:43 PM
@Yupeng Fu I assumed your predicate timestamp was for 1 week ago. If so, that would prune out data from older than 1 week, but would still scan data for the last one week
@Xiang Fu using 
max
would still scan, unless we can somehow prune out segments
y

Yupeng Fu

12/01/2020, 9:43 PM
@Xiang Fu tried max, but got error
Copy code
"message": "QueryExecutionError:\njava.lang.NumberFormatException: For input string: \"2020-10-15 09:00:00\"\n\tat sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)\n\tat sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)\n\tat java.lang.Double.parseDouble(Double.java:538)\n\tat org.apache.pinot.core.segment.index.readers.StringDictionary.getDoubleValue(StringDictionary.java:58)\n\tat org.apache.pinot.core.operator.query.DictionaryBasedAggregationOperator.getNextBlock(DictionaryBasedAggregationOperator.java:66)\n\tat org.apache.pinot.core.operator.query.DictionaryBasedAggregationOperator.getNextBlock(DictionaryBasedAggregationOperator.java:43)\n\tat org.apache.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:49)\n\tat org.apache.pinot.core.operator.CombineOperator$1.runJob(CombineOperator.java:105)\n\tat org.apache.pinot.core.util.trace.TraceRunnable.run(TraceRunnable.java:40)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)\n\tat com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:57)"
m

Mayank

12/01/2020, 9:44 PM
Yeah, seems that works on numeric data only
x

Xiang Fu

12/01/2020, 9:44 PM
it’s not for string, if that column is number then should be ok
then you can use time_coverter function to convert it to human readable format
y

Yupeng Fu

12/01/2020, 9:45 PM
that would lead to scan as well?
x

Xiang Fu

12/01/2020, 9:46 PM
if it’s a full segment match, then we should just read it from dictionary
y

Yupeng Fu

12/01/2020, 9:46 PM
@Mayank that’s right, we wanted to get latest record, but not sure when it was last generated
m

Mayank

12/01/2020, 9:46 PM
I don't think max helps here, unless we find a way to avoid segment scans (pruning)
y

Yupeng Fu

12/01/2020, 9:47 PM
so use a wider range to be safe
m

Mayank

12/01/2020, 9:47 PM
Why do you go back 7 days for the latest record?
y

Yupeng Fu

12/01/2020, 9:48 PM
to be safe
it’s used to track the most recent down time
m

Mayank

12/01/2020, 9:48 PM
Your data is time ordered?
y

Yupeng Fu

12/01/2020, 9:49 PM
need to check with the user, but i hope we can assume so
m

Mayank

12/01/2020, 9:49 PM
If you just say max without any predicates then it will work (I think that is what Xiang was suggesting)?
without any predicates, we dont' scan, we just look at the metadata
y

Yupeng Fu

12/01/2020, 9:49 PM
right
we tried that query, and got the error above
m

Mayank

12/01/2020, 9:50 PM
Try time-convert as Xiang suggested
Powered by