#general
@huangshoukai: @huangshoukai has joined the channel
@chundong.wang: Hi team, when we tried to ran below query, ```SELECT facility_name as key_col, COUNT(*) as val_col FROM enriched_station_orders_v1_OFFLINE WHERE created_at_seconds BETWEEN 1606756268 AND 1609175468 AND (facility_organization_id <> 'ac56d23b-a6a2-4c49-8412-a0a0949fb5ef') GROUP BY key_col ORDER BY val_col DESC LIMIT 5``` We’ll get exceptions on pinot-server like (index number seems to vary), ```Caught exception while processing and combining group-by order-by for index: 1``` However if we change from `facility_organization_id <> 'ac56d23b-a6a2-4c49-8412-a0a0949fb5ef'` to `facility_organization_id = 'ac56d23b-a6a2-4c49-8412-a0a0949fb5ef'` there won’t be such exception. Or if we switch to `facility_id` instead of `facility_name` it won’t threw exception as well. Have you seen such issue before?
@chundong.wang: cc @alex.odle
@mayanks: What's the exception you are seeing (it should be logged along with the error message)?
@chundong.wang: The exception I got from pinot-ui is ``` { "message": "QueryExecutionError:\njava.lang.ArrayIndexOutOfBoundsException", "errorCode": 200 },```
@chundong.wang: But from the log on pinot-server, the exception isn’t there.
@mayanks: Hmm, server should have also logged: ```LOGGER.error( "Caught exception while processing and combining group-by order-by for index: {}, operator: {}, queryContext: {}", index, _operators.get(index).getClass().getName(), _queryContext, e); mergedProcessingExceptions.add(QueryException.getException(QueryException.QUERY_EXECUTION_ERROR, e));```
@chundong.wang: I know and that’s weird to me too. The full error log I see on pinot-server is, ```2020/12/29 17:50:16.761 ERROR [GroupByOrderByCombineOperator] [pqw-7] Caught exception while processing and combining group-by order-by for index: 1, operator: org.apache.pinot.core.operator.query.AggregationGroupByOrderByOperator, queryContext: QueryContext{_selectExpressions=[facility_name, count(*)], _aliasMap={facility_name=key_col, count(*)=val_col}, _filter=(created_at_seconds BETWEEN '1606756268' AND '1609175468' AND facility_organization_id != 'ac56d23b-a6a2-4c49-8412-a0a0949fb5ef'), _groupByExpressions=[facility_name], _orderByExpressions=[count(*) DESC], _havingFilter=null, _limit=5, _offset=0, _queryOptions={responseFormat=sql, groupByMode=sql, timeoutMs=24999}, _debugOptions=null, _brokerRequest=BrokerRequest(querySource:QuerySource(tableName:enriched_station_orders_v1_OFFLINE), filterQuery:FilterQuery(id:0, value:null, operator:AND, nestedFilterQueryIds:[1, 2]), aggregationsInfo:[AggregationInfo(aggregationType:COUNT, aggregationParams:{column=*}, isInSelectList:true, expressions:[*])], groupBy:GroupBy(topN:5, expressions:[facility_name]), filterSubQueryMap:FilterQueryMap(filterQueryMap:{0=FilterQuery(id:0, value:null, operator:AND, nestedFilterQueryIds:[1, 2]), 1=FilterQuery(id:1, column:created_at_seconds, value:[[1606756268 1609175468]], operator:RANGE, nestedFilterQueryIds:[]), 2=FilterQuery(id:2, column:facility_organization_id, value:[ac56d23b-a6a2-4c49-8412-a0a0949fb5ef], operator:NOT, nestedFilterQueryIds:[])}), queryOptions:{responseFormat=sql, groupByMode=sql, timeoutMs=24999}, pinotQuery:PinotQuery(dataSource:DataSource(tableName:enriched_station_orders_v1_OFFLINE), selectList:[_expression_(type:FUNCTION, functionCall:Function(operator:AS, operands:[_expression_(type:IDENTIFIER, identifier:Identifier(name:facility_name)), _expression_(type:IDENTIFIER, identifier:Identifier(name:key_col))])), _expression_(type:FUNCTION, functionCall:Function(operator:AS, operands:[_expression_(type:FUNCTION, functionCall:Function(operator:COUNT, operands:[_expression_(type:IDENTIFIER, identifier:Identifier(name:*))])), _expression_(type:IDENTIFIER, identifier:Identifier(name:val_col))]))], filterExpression:_expression_(type:FUNCTION, functionCall:Function(operator:AND, operands:[_expression_(type:FUNCTION, functionCall:Function(operator:BETWEEN, operands:[_expression_(type:IDENTIFIER, identifier:Identifier(name:created_at_seconds)), _expression_(type:LITERAL, literal:<Literal longValue:1606756268>), _expression_(type:LITERAL, literal:<Literal longValue:1609175468>)])), _expression_(type:FUNCTION, functionCall:Function(operator:NOT_EQUALS, operands:[_expression_(type:IDENTIFIER, identifier:Identifier(name:facility_organization_id)), _expression_(type:LITERAL, literal:<Literal stringValue:ac56d23b-a6a2-4c49-8412-a0a0949fb5ef>)]))])), groupByList:[_expression_(type:IDENTIFIER, identifier:Identifier(name:facility_name))], orderByList:[_expression_(type:FUNCTION, functionCall:Function(operator:DESC, operands:[_expression_(type:FUNCTION, functionCall:Function(operator:COUNT, operands:[_expression_(type:IDENTIFIER, identifier:Identifier(name:*))]))]))], limit:5), orderBy:[SelectionSort(column:count(*), isAsc:false)], limit:5)} ```
@chundong.wang: Basically ended at `queryContext` part
@mayanks: Seems like a bug, mind filing an issue?
@chundong.wang: Sure!
@mayanks: Also, can you try NOT IN instead, just to check?
@chundong.wang: Just tried, and it’s the same.
@mayanks: Thanks. I'd it is a general bug though, and something in your data might be triggering this. Would it be possible for you to test similar queries on other tables?
@chundong.wang: On staging environment (exact same configured except different data), this issue couldn’t be repro. So I’m suspecting it has something to do with corner case of data. I’d imaging prod to have more real data.
@chundong.wang: I tried this in other table on prod and it won’t repro. I tried other columns in the same table and it won’t repro. But I don’t know what kind of data issue is causing this.
@mayanks: Hmm, would be hard to repro without knowing what to look for
@mayanks: What's the cardinality of the column?
@chundong.wang: `facility_organization_id` is a couple of thousands.
@mayanks: And how many docs are you expecting in your original query result?
@mayanks: Seems like you want to including all orgs except one, which could be huge
@chundong.wang: 6 million doc scanned I think
@chundong.wang: total around 100mm docs
@mayanks: I see, that is not too much
@chundong.wang: Thanks.
@mayanks: Yeah, at this point, I think issue is only going to help with tracking. Are you open to debugging at your end (with help from community)? I presume you cannot share the data (even one segment)
@chundong.wang: I’ll need @elon.azoulay’s help to turn on/off flags or gather dump.
@chundong.wang: But yes I think we could debug this together when he’s ready. :slightly_smiling_face:
@mayanks: There's also `AnonymizeDataCommand` which can anonymize your data (if that can make it shareable)
@g.kishore: @jackie.jxt ^^
@chundong.wang: I heard this has something to do with tab character in string column
@jackie.jxt: @chundong.wang Do you have the stack trace after the exception log?
@chundong.wang: I don’t. The server log ends at the query context part without actual stack trace of the exception.
@jackie.jxt: Based on the description, I think the problem is that there are `\t` in some `facility_name` values
@jackie.jxt: Which version of Pinot are you running?
@chundong.wang: `0.5.0`
@jackie.jxt: Is it easy to try with the latest version?
@jackie.jxt: There is a fix for this issue, but not included in `0.5.0`:
@chundong.wang: I see. I’ll check with the team to see how we’d upgrade. Is `0.6.0` released yet (which I suppose included this fix)?
@jackie.jxt: Yes, and it includes this fix
@ramol.86: @ramol.86 has joined the channel
#random
@huangshoukai: @huangshoukai has joined the channel
@ramol.86: @ramol.86 has joined the channel
#pql-2-calcite
@avg1206: @avg1206 has joined the channel
#troubleshooting
@huangshoukai: @huangshoukai has joined the channel
@ramol.86: @ramol.86 has joined the channel
#pinot-perf-tuning
@ken: What’s the best way to confirm that a star tree index is being used for a query?
@ken: Asking because if I add a where clause (that filters out nothing) using a dimension NOT in my dimensionsSplitOrder list, I though that the star tree wouldn’t be used - and the query time is the same for the case without that where clause.
@chinmay.cerebro: You might want to ensure that the index is created properly first for those segments:
@jackie.jxt: If it filters out nothing, star-tree can still be used. You may check the `numDocsScanned` to see if the query is scanning the pre-aggregated records
@ken: `numDocsScanned` = 1B (out of 1.7B) for case where the filter matches a few records, 362M for case where the filter matches no records, and 362M for the case of no filtering. But time taken is almost identical in all cases (27 seconds, +/- 1 second).
@jackie.jxt: Can you share the query and the star-tree config? Seems not much documents are pre-aggregated
@jackie.jxt: Too many documents selected even with star-tree
@ken: ``` "starTreeIndexConfigs": [{ "dimensionsSplitOrder": [ "advertiser", "adHash", "network", "imageSize", "adType", "platform", "country", "crawlDays" ], "skipStarNodeCreationForDimensions": [ ], "functionColumnPairs": [ "SUM__adSpend", "SUM__impressions", "DistinctCountHLL__adHash", "DistinctCountHLL__crawlDays", "MIN__crawlDays", "MAX__crawlDays" ], "maxLeafRecords": 10000 },```
@ken: `select adHash,advertiser,sum(adSpend) from crawldata group by adHash,advertiser order by sum(adSpend) desc limit 100`
@jackie.jxt: Does `adHash` and `advertiser` has very high cardinality?
@ken: Yes
@jackie.jxt: Most of the query time should be spent on grouping these 2 columns
@jackie.jxt: Star-tree won't help much for this query as not much documents can be pre-aggregated
@jackie.jxt: Only records with the same `adHash` and `advertiser` can be pre-aggregated
@ken: There’s a one-to-many relationship from advertiser to adHash. So given an adHash, it’s always for one advertiser.
@jackie.jxt: I see. Can you try `select adHash, sum(adSpend) from crawldata group by adHash order by sum(adSpend) desc limit 100` and see if it is faster?
@ken: it’s slower - timed out after 30 sec
@ken: Retrying with 50 sec
@jackie.jxt: It should definitely be cheaper than grouping on 2 columns
@ken: I’m going to have to log onto servers and check logs, as now the initial query is also timing out. So feels like something is borked…
@ken: I did just run a distinct_count query on adHash, which probably blew memory somewhere :disappointed:
@ken: As there are likely > 1B unique adHashes, out of 1.7B records.
@jackie.jxt: Based on the `numDocsScanned` for star-tree queries, maybe ~300M unique adHashes :wink:
@ken: I partition each month of data into 30 segments, by adHash. So likely there’s about 300M unique adHashes per segment…
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
