https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
b
It does support joins, same as the Imply version. One thing to make sure of is that the dimension table is on the right-hand side of the join. If it already is, you might also consider using a lookup if that's possible, see whether that performs better.
d
Hi @Ben Krug Dim is on the right-hand side of join. Also, i have modified the query as suggested with LOOKUP and there is a slight improvement in performance, and it takes 7 sec now. I have added additional context as well here like vectorize etc. Could you please review this once and suggest if anything else can be done?
{
"query": "select LOOKUP(fe.product_id,'dim_prod_manf_desc') as manf_desc ,fe.sale_month,fe.sale_year,fe.sale_quarter,sum(fe.dist_total_revenue) from fact_extrapolated_revenue fe where __time >= TIME_PARSE ('2020-12-31T000000.000Z') AND __time < TIME_PARSE ('2022-11-30T000000.000Z') and fe.product_id IN (select dp.product_id from denorm_dim_product_unspsc dp where dp.taxonomy_id IN (41151519,41151519,42311502,42311506,42311510,42311513,42311515,42311523,42311527,42311540,42311545,42311546,42311547,42311552,42312005,42312103,42312105,42312313,42312401)) GROUP BY fe.product_id,fe.sale_month,fe.sale_year,fe.sale_quarter", "context": { "populateCache": 
*true*,
"useCache": 
*true*,
"vectorize": 
*true*,
"vectorSize": 512, "forceLimitPushDown": 
*false*,
"enableParallelMerge": 
true
 
}}
Also if while ingesting the data, the dimension columns are ordered in ascending order, will it improve query performance?
b
I'm not sure... Also, ordering in segments depends on partitioning scheme, and order of dimensions in the spec, I believe. Nothing jumps out about the query. I figure the inner is pretty quick? (And btw, URI lookups are, in the long run, often faster and less problematic than jdbc lookups. I don't know which you used.) Might be tuning possible for groupBy in general, but that gets pretty complicated. (https://druid.apache.org/docs/latest/querying/groupbyquery.html)
d
thank you @Ben Krug. Also, what about query vectorization? if i mention vectorize=true in my query context, will it improve the overall performance in group by queries?
b
Often vectorization helps, if it can be applied (see here). But it's also on by default, context is more helpful for disabling it when it causes problems.
Open in Slack
PreviousNext
Powered by