https://pinot.apache.org/ logo
Join Slack
Powered by
there seems to be a hard limit of 1 million rows r...
# troubleshooting
x
there seems to be a hard limit of 1 million rows returned by pinot even with using 
LIMIT
way beyond that; any way to remove this? currently using the 
latest-jdk11
image for pinot on kubernetes unfortunately, i can’t seem to use 
IN_SUBQUERY
to represent the userid set, so on the client side i break my pinot queries into 1. fetch userids query (using a GROUP BY + HAVING query). sometimes i may get more than a million user ids 2. do final query
m
@Jackie ^^
Could you clarify on what's the exact issue with 
IN_SUBQUERY
?
x
my queries to fetch userids look like:
Copy code
SELECT userid, COUNT(*) ... GROUP BY userid HAVING COUNT(userid) >= 8
there’s 2 columns returned (even though i only want the userids) so i cannot use 
IN_SUBQUERY
. am i wrong?
m
What happens when you drop the count(*) from selection?
x
Copy code
pinotdb.exceptions.DatabaseError: {'errorCode': 200,
 'message': 'QueryExecutionError:\n'
            'java.lang.UnsupportedOperationException: Operation not supported '
            'for DISTINCT aggregation function\n'
            '\tat '
            'org.apache.pinot.core.query.aggregation.function.DistinctAggregationFunction.createAggregationResultHolder(DistinctAggregationFunction.java:104)\n'
            '\tat '
            'org.apache.pinot.core.query.aggregation.DefaultAggregationExecutor.<init>(DefaultAggregationExecutor.java:37)\n'
            '\tat '
            'org.apache.pinot.core.operator.query.AggregationOperator.getNextBlock(AggregationOperator.java:61)\n'
            '\tat '
            'org.apache.pinot.core.operator.query.AggregationOperator.getNextBlock(AggregationOperator.java:35)'}
{'errorCode': 200,
 'message': 'QueryExecutionError:\n'
            'java.lang.UnsupportedOperationException: Operation not supported '
            'for DISTINCT aggregation function\n'
            '\tat '
            'org.apache.pinot.core.query.aggregation.function.DistinctAggregationFunction.createAggregationResultHolder(DistinctAggregationFunction.java:104)\n'
            '\tat '
            'org.apache.pinot.core.query.aggregation.DefaultAggregationExecutor.<init>(DefaultAggregationExecutor.java:37)\n'
            '\tat '
            'org.apache.pinot.core.operator.query.AggregationOperator.getNextBlock(AggregationOperator.java:61)\n'
            '\tat '
            'org.apache.pinot.core.operator.query.AggregationOperator.getNextBlock(AggregationOperator.java:35)'}
Hi, any update on this limit? :)
hi just checking again, does anybody know how i can bypass the 1 million limit on rows returned by the broker?
m
Hey, sorry missed this earlier. Let me find out.
Note though, if you unconstrain, and run a very expensive query, you are running the risk of OOM
x
no worries! unfortunately if i want to do the userid filtering after 
GROUP BY + HAVING
, there's no workaround i'm aware of but to fetch the userids on the client side then use it as 
ID_SET
in the second query
should it put this down as a github issue / feature request? i feel like this is a bit of a niche use case
m
Please do
I found there's a server conf 
pinot.server.query.executor.num.group.limits
@Jackie Do we have a query option?
j
We don't allow overriding groups limit with query option because that can potentially exhaust the system resource
It can be configured via the server conf 
pinot.server.query.executor.num.groups.limit
Note that it is 
num.groups.limit
instead of 
num.group.limits
m
@Jackie what about the 
ID_SET
question in the thread?
j
This is not really a 
ID_SET
problem, as the filter is applied to aggregation result
x
sorry to revisit this @Mayank, but isnt the 1 million limit controlled by this? 
pinot.server.query.executor.groupby.trim.threshold
pinot.server.query.executor.num.group.limits
is 100k
m
Yes it is. If you increase that to a large value then for queries that indeed hit that number will use up lot more resources
x
in my case, the cardinality of users that i need to group by on can be up to 6-7M
do you have any case studies or experience with people who have increased 
pinot.server.query.executor.groupby.trim.threshold
beyond the 1M limit to cater for this use case?
Open in Slack
PreviousNext
Powered by