Am written a SQL druid query using Join, Filter & Lookup (multi dimension data fetch), this query worked fine from the day 1, but certainly it takes long time and say timeout error

in this query am using GROUP BY ROLLUP

when I removing Rollup() it working...

and I read LOOKUP also use heap memory in historical.

I don't know which CLAUSE making issue .

If i do restart druid service, again it's working fine.

But am sure again it will hit the same query respond timeout issue after some time/day.

some key point : we use single server deployment

Version : 0.22.0

and you are correct, lookups are stored in the heap

SELECT CONCAT(merchantCategoryCode, '-', mcc_name.v) AS mccName, count(distinct product.id) FILTER (WHERE product.status = 'ACTIVE' AND product.id NOT IN (SELECT id FROM product WHERE status = 'DEACTIVATED')) AS product_count FROM product INNER JOIN merchant ON product.merchantId = merchant.id INNER JOIN lookup.mcc_name ON mcc_name.k = product.merchantCategoryCode WHERE product.status = 'ACTIVE' AND merchant.status = 'ACTIVE' AND merchant.id NOT IN (SELECT id FROM portal_merchants WHERE status = 'DEACTIVATED') GROUP BY ROLLUP(1) ORDER BY store_count DESC LIMIT 6

👆

Hi Kyle, any favor / solutions on above mentioned issue to resolve. thanks

Have you had a look at the EXPLAIN for it?

will try again with EXPLAIN

is a way to achieve the above query result by splitting then (split the query )?

Thank you Kyle for your response

am using only one LOOKUP will still contribute more heap usage ? Kyle ! it has about 282 keys and mapping. But while we check the heap memory size avail about 40-50%

while i was faced timeout issue when i was executed it.

in druid SQL is there a way or option to increase memory size to specific (individual) query in run time by appending in SQL query statement itself !?