I m hitting a slow query case where my combined offline real

Question

I m hitting a slow query case where my combined offline realtime table `metrics` is slow to query but the individual `metrics OFFLINE` and `metrics REALTIME` are quick to query separately Any ideas ```select utc date sum impressions from metrics OFFLINE where utc date gt = 1591142400000 and utc date lt 1593648000000 group by utc date order by utc date ASC limit 1831``` This returns pretty fast 200ms over a lot of 400mil rows If I switch to `metrics REALTIME` it s also fast and returns zero rows ```select utc date sum impressions from metrics REALTIME where utc date gt = 1591142400000 and utc date lt 1593648000000 group by utc date order by utc date ASC limit 1831``` However if I query `metrics` it s very slow ```select utc date sum impressions from metrics where utc date gt = 1591142400000 and utc date lt 1593648000000 group by utc date order by utc date ASC limit 1831```

Dan Hill · Answer

The logs in `pinot server` include the following timeout I modified the query above to simplify it in case you see differences ```2020 07 01 22 28 11 526 ERROR CombineGroupByOrderByOperator pqr 0 Timed out while combining group by results after 9996ms brokerRequest = BrokerRequest querySource QuerySource tableName metrics OFFLINE filterQuery FilterQuery id 0 value null operator AND nestedFilterQueryIds 1 2 3 aggregationsInfo AggregationInfo aggregationType SUM aggregationParams column=clicks isInSelectList true expressions clicks AggregationInfo aggregationType SUM aggregationParams column=impressions isInSelectList true expressions impressions AggregationInfo aggregationType SUM aggregationParams column=cost usd micros isInSelectList true expressions cost usd micros groupBy GroupBy topN 1831 expressions utc date filterSubQueryMap FilterQueryMap filterQueryMap 0=FilterQuery id 0 value null operator AND nestedFilterQueryIds 1 2 3 1=FilterQuery id 1 column utc date value 1591142400000 operator RANGE nestedFilterQueryIds 2=FilterQuery id 2 column utc date value 1593648000000 operator RANGE nestedFilterQueryIds 3=FilterQuery id 3 column timestamp value 1593561599986 operator RANGE nestedFilterQueryIds queryOptions responseFormat=sql groupByMode=sql timeoutMs=9997 pinotQuery PinotQuery dataSource DataSource tableName metrics selectList Expression type IDENTIFIER identifier Identifier name utc date Expression type FUNCTION functionCall Function operator SUM operands Expression type IDENTIFIER identifier Identifier name clicks Expression type FUNCTION functionCall Function operator SUM operands Expression type IDENTIFIER identifier Identifier name impressions Expression type FUNCTION functionCall Function operator SUM operands Expression type IDENTIFIER identifier Identifier name cost usd micros filterExpression Expression type FUNCTION functionCall Function operator AND operands Expression type FUNCTION functionCall Function operator GREATER THAN OR EQUAL operands Expression type IDENTIFIER identifier Identifier name utc date Expression type LITERAL literal lt Literal longValue 1591142400000 gt Expression type FUNCTION functionCall Function operator LESS THAN operands Expression type IDENTIFIER identifier Identifier name utc date Expression type LITERAL literal lt Literal longValue 1593648000000 gt groupByList Expression type IDENTIFIER identifier Identifier name utc date orderByList Expression type FUNCTION functionCall Function operator ASC operands Expression type IDENTIFIER identifier Identifier name utc date limit 1831 orderBy SelectionSort column utc date isAsc true limit 1831 ```

Dan Hill · Answer

Here are the metrics for the slow query `metrics` ``` exceptions numServersQueried 2 numServersResponded 0 numSegmentsQueried 0 numSegmentsProcessed 0 numSegmentsMatched 0 numConsumingSegmentsQueried 0 numDocsScanned 0 numEntriesScannedInFilter 0 numEntriesScannedPostFilter 0 numGroupsLimitReached false totalDocs 0 timeUsedMs 10000 segmentStatistics traceInfo minConsumingFreshnessTimeMs 0 ``` Here are the fast ones `metrics OFFLINE` ``` exceptions numServersQueried 1 numServersResponded 1 numSegmentsQueried 418 numSegmentsProcessed 418 numSegmentsMatched 319 numConsumingSegmentsQueried 0 numDocsScanned 319 numEntriesScannedInFilter 0 numEntriesScannedPostFilter 638 numGroupsLimitReached false totalDocs 398507752 timeUsedMs 262 segmentStatistics traceInfo minConsumingFreshnessTimeMs 0``` `metrics REALTIME` ``` resultTable dataSchema columnDataTypes STRING DOUBLE columnNames utc date sum impressions rows exceptions numServersQueried 1 numServersResponded 1 numSegmentsQueried 1 numSegmentsProcessed 0 numSegmentsMatched 0 numConsumingSegmentsQueried 1 numDocsScanned 0 numEntriesScannedInFilter 0 numEntriesScannedPostFilter 0 numGroupsLimitReached false totalDocs 0 timeUsedMs 16 segmentStatistics traceInfo minConsumingFreshnessTimeMs 9223372036854775807 ```

Xiang Fu · Answer

so realtime has no data

Dan Hill · Answer

Correct

Dan Hill · Answer

I just created it

Xiang Fu · Answer

can you try this ```select utc date sum impressions from metrics OFFLINE where utc date gt = 1591142400000 and utc date lt 1593648000000 and utc date lt 1593561599986 group by utc date order by utc date ASC limit 1831```

Xiang Fu · Answer

this is generated query sent to offline server

Xiang Fu · Answer

for hybrid table pinot get max time from offline and use that 1 day as time boundary

Dan Hill · Answer

That s fast

Dan Hill · Answer

Ohhh

Xiang Fu · Answer

hmmm

Dan Hill · Answer

I might have populated offline with test data for tomorrow too

Dan Hill · Answer

Could that impact this

Xiang Fu · Answer

hmm

Xiang Fu · Answer

if querying offline itself if fast then shouldn t be a problem

Dan Hill · Answer

Ok Yea querying offline directly is fast

Xiang Fu · Answer

the log is from pinot server or broker

Xiang Fu · Answer

```2020 07 01 22 28 11 526 ERROR CombineGroupByOrderByOperator pqr 0 Timed out while combining group by results after 9996ms brokerRequest = BrokerRequest querySource QuerySource tableName metrics OFFLINE filterQuery FilterQuery id 0 value null operator AND nestedFilterQueryIds 1 2 3 aggregationsInfo AggregationInfo aggregationType SUM aggregationParams column=clicks isInSelectList true expressions clicks AggregationInfo aggregationType SUM aggregationParams column=impressions isInSelectList true expressions impressions AggregationInfo aggregationType SUM aggregationParams column=cost usd micros isInSelectList true expressions cost usd micros groupBy GroupBy topN 1831 expressions utc date filterSubQueryMap FilterQueryMap filterQueryMap 0=FilterQuery id 0 value null operator AND nestedFilterQueryIds 1 2 3 1=FilterQuery id 1 column utc date value 1591142400000 operator RANGE nestedFilterQueryIds 2=FilterQuery id 2 column utc date value 1593648000000 operator RANGE nestedFilterQueryIds 3=FilterQuery id 3 column timestamp value 1593561599986 operator RANGE nestedFilterQueryIds queryOptions responseFormat=sql groupByMode=sql timeoutMs=9997 pinotQuery PinotQuery dataSource DataSource tableName metrics selectList Expression type IDENTIFIER identifier Identifier name utc date Expression type FUNCTION functionCall Function operator SUM operands Expression type IDENTIFIER identifier Identifier name clicks Expression type FUNCTION functionCall Function operator SUM operands Expression type IDENTIFIER identifier Identifier name impressions Expression type FUNCTION functionCall Function operator SUM operands Expression type IDENTIFIER identifier Identifier name cost usd micros filterExpression Expression type FUNCTION functionCall Function operator AND operands Expression type FUNCTION functionCall Function operator GREATER THAN OR EQUAL operands Expression type IDENTIFIER identifier Identifier name utc date Expression type LITERAL literal lt Literal longValue 1591142400000 gt Expression type FUNCTION functionCall Function operator LESS THAN operands Expression type IDENTIFIER identifier Identifier name utc date Expression type LITERAL literal lt Literal longValue 1593648000000 gt groupByList Expression type IDENTIFIER identifier Identifier name utc date orderByList Expression type FUNCTION functionCall Function operator ASC operands Expression type IDENTIFIER identifier Identifier name utc date limit 1831 orderBy SelectionSort column utc date isAsc true limit 1831 ```

Xiang Fu · Answer

this one shows the extra predicate

Xiang Fu · Answer

```3=FilterQuery id 3 column timestamp value 1593561599986 operator RANGE nestedFilterQueryIds ```

Dan Hill · Answer

pinot server

Xiang Fu · Answer

so it s timeout for offline query

Dan Hill · Answer

Hmm maybe it s a different query

Xiang Fu · Answer

is this hybrid query consistently timeout

Xiang Fu · Answer

an d is there any broker query

Dan Hill · Answer

It s consistently timing out

Dan Hill · Answer

Hmm broker logs do not have any recent updates

Dan Hill · Answer

The most recent one is a little stale ```2020 07 01 22 26 55 306 ERROR QueryRouter jersey server managed async executor 19 Caught exception while sending request 1496 to server pinot server 0 O marking query failed java net UnknownHostException pinot server 0 pinot server headless pinot metrics dev svc cluster local Name or service not known at java net Inet4AddressImpl lookupAllHostAddr Native Method ~ 1 8 0 252 at java net InetAddress$2 lookupAllHostAddr InetAddress java 929 ~ 1 8 0 252 at java net InetAddress getAddressesFromNameService InetAddress java 1324 ~ 1 8 0 252 at java net InetAddress getAllByName0 InetAddress java 1277 ~ 1 8 0 252 at java net InetAddress getAllByName InetAddress java 1193 ~ 1 8 0 252 at java net InetAddress getAllByName InetAddress java 1127 ~ 1 8 0 252 at java net InetAddress getByName InetAddress java 1077 ~ 1 8 0 252 ```

Xiang Fu · Answer

ic

Xiang Fu · Answer

hmmm

Xiang Fu · Answer

from the log seem the timeout query is `select utc date sum impressions sum cost usd micros from metrics where utc date gt = 1591142400000 and utc date lt 1593648000000 and utc date lt 1593561599986 group by utc date order by utc date LIMIT 1831`

Dan Hill · Answer

Weird I don t think my client sends three time filters

Dan Hill · Answer

My most recently timeout didn t generate any new logs

Xiang Fu · Answer

it should send two

Xiang Fu · Answer

that one more is what pinot broker added

Xiang Fu · Answer

then send to offline table

Dan Hill · Answer

Ah okay

Jackie · Answer

Based on the query seems you are using a separate column `timestamp` as the time column

Jackie · Answer

And filtering on that column is causing the slowness

Jackie · Answer

The query should be `select utc date sum impressions sum cost usd micros from metrics where utc date gt = 1591142400000 and utc date lt 1593648000000 and timestamp lt 1593561599986 group by utc date order by utc date LIMIT 1831` notice that the last filter is on `timestamp`

Jackie · Answer

What type of index do you use on `timestamp`

Dan Hill · Answer

```metrics realtime table config json | tableName metrics tableType REALTIME segmentsConfig timeColumnName timestamp timeType MILLISECONDS retentionTimeUnit DAYS retentionTimeValue 1 segmentPushType APPEND segmentAssignmentStrategy BalanceNumSegmentAssignmentStrategy schemaName metrics replication 1 replicasPerPartition 1 tableIndexConfig loadMode MMAP aggregateMetrics true noDictionaryColumns insertions impressions clicks cost usd micros starTreeIndexConfigs dimensionsSplitOrder utc date platform id customer id account id campaign id promotion id skipStarNodeCreationForDimensions functionColumnPairs SUM insertions SUM impressions SUM clicks SUM cost usd micros dimensionsSplitOrder utc date platform id content id skipStarNodeCreationForDimensions functionColumnPairs SUM insertions SUM impressions SUM clicks SUM cost usd micros streamConfigs streamType kafka stream kafka consumer type simple stream kafka topic name metrics realtime stream kafka decoder class name org apache pinot plugin stream kafka KafkaJSONMessageDecoder stream kafka consumer factory class name org apache pinot plugin stream kafka20 KafkaConsumerFactory stream kafka hlc zk connect string kafka zookeeper 2181 stream kafka zk broker url kafka zookeeper 2181 stream kafka broker list kafka 9092 realtime segment flush threshold time 3600000 realtime segment flush threshold size 50000 stream kafka consumer prop auto offset reset smallest tenants metadata customConfigs metrics schema json | schemaName metrics dimensionFieldSpecs name utc date dataType LONG name platform id dataType LONG name customer id dataType LONG name account id dataType LONG name campaign id dataType LONG name promotion id dataType LONG name content id dataType LONG metricFieldSpecs name insertions dataType LONG name impressions dataType LONG name clicks dataType LONG name cost usd micros dataType LONG timeFieldSpec incomingGranularitySpec name timestamp dataType LONG timeFormat EPOCH timeType MILLISECONDS ```

Dan Hill · Answer

Here s the realtime config The offline one looks similar

Dan Hill · Answer

Even though I have my own time dimension should I still pass in a rough timestamp filter I could do both

Dan Hill · Answer

Even if I add explicit timestamp filters it still times out ```select utc date sum impressions from metrics where utc date gt = 1591142400000 and utc date lt 1593648000000 and timestamp gt = 1591142400000 and timestamp lt 1593648000000 group by utc date order by utc date ASC limit 1831```

Dan Hill · Answer

< Jackie> < Xiang Fu> jfyi that I updated this thread

Jackie · Answer

< Dan Hill> The timestamp filter is always automatically added for Hybrid use cases One problem with this for OFFLINE side is that star tree cannot be used because `timestamp` is not part of the `dimensionsSplitOrder`

Jackie · Answer

Can you try this query on the offline side only ```select utc date sum impressions from metrics OFFLINE where utc date gt = 1591142400000 and utc date lt 1593648000000 and timestamp lt 1593648000000 group by utc date order by utc date ASC limit 1831```

Jackie · Answer

Also is it possible to use `utc date` as the time column

Dan Hill · Answer

Weird yea that query is slow ```select utc date sum impressions from metrics OFFLINE where utc date gt = 1591142400000 and utc date lt 1593648000000 and timestamp lt 1593648000000 group by utc date order by utc date ASC limit 1831``` Are there any issues with making utc the time column I ll eventually support times in some other timezones as separate dimensions

Jackie · Answer

< Dan Hill> As I mentioned the reason why this query is slow is because it cannot utilize the star tree index

Jackie · Answer

If you never explicitly query on timestamp then it is perfectly fine to use utc as the time column

Jackie · Answer

When you add other timezones you can add them as dimensions

Dan Hill · Answer

My current query is only querying `utc date` and is slow

Dan Hill · Answer

```select utc date sum impressions from metrics OFFLINE where utc date gt = 1591142400000 and utc date lt 1593648000000 group by utc date order by utc date ASC limit 1831```

Jackie · Answer

In that case you can use utc date as the time column instead

Jackie · Answer

Actually all the time fields if you want to add more in the future should be added as `DateTimeField` < Neha Pawar> can give more context on that

Dan Hill · Answer

Oh okay Any benefit of keeping the raw timestamp column What do we think the current issue with the query Is Pinot trying to use the timestamp column internally for something For adding other time fields is that just the dataType or does that go into the timeFieldSpec This table isn t serving yet so it s very easy to change ``` schemaName metrics dimensionFieldSpecs name utc date dataType LONG name platform id dataType LONG name customer id dataType LONG name account id dataType LONG name campaign id dataType LONG name promotion id dataType LONG name content id dataType LONG metricFieldSpecs name insertions dataType LONG name impressions dataType LONG name clicks dataType LONG name cost usd micros dataType LONG timeFieldSpec incomingGranularitySpec name timestamp dataType LONG timeFormat EPOCH timeType MILLISECONDS ```

Jackie · Answer

If you do not explicitly query it then you can replace it with `utc date` which uses `DAYS` granularity

Jackie · Answer

For Hybrid use cases Pinot uses the time column to merge the result from OFFLINE side and REALTIME side That s the reason why there is an extra filter on `timestamp` column

Jackie · Answer

You can update your schema to ``` schemaName metrics dimensionFieldSpecs name platform id dataType LONG name customer id dataType LONG name account id dataType LONG name campaign id dataType LONG name promotion id dataType LONG name content id dataType LONG metricFieldSpecs name insertions dataType LONG name impressions dataType LONG name clicks dataType LONG name cost usd micros dataType LONG dateTimeFieldSpecs name utc date dataType LONG format 1 MILLISECONDS EPOCH granularity 1 DAYS ```

Dan Hill · Answer

Ah ok Will it merge just the aggregates for the same dateTimeFieldSpecs

Jackie · Answer

Pinot will use the time column you specified in the `tableConfig segmentsConfig timeColumnName` to create the extra filter and merge the result

Jackie · Answer

The reason for the slowness is because `timestamp` is not in the star tree split order so your query won t utilize the star tree index

Jackie · Answer

If you change time column from `timestamp` to `utc date` it is part of the split order so your query can utilize the star tree

Dan Hill · Answer

Sweet thanks

Dan Hill · Answer

Do I need to have `utc date` in the star tree index Should I remove it

Dan Hill · Answer

Any updates to `segmentsConfig` ``` segmentsConfig timeColumnName utc date timeType MILLISECONDS ```