Hello Druid community! Did any of you ever used the

injective: true

property of a lookup? From the documentation:

The lookup table must have a key-value pair defined for every input that the LOOKUP function call may encounter. For example, when calling LOOKUP(sku, 'sku_to_name'), the sku_to_name lookup table must have a key for all possible sku.

I wonder how strict is that constraint and what would be the outcome if I make a lookup as injective but not all the

sku

keys are present. I am testing it and actually Druid is returning back the correct result (null for cases where the

sku

key is not present). Unfortunately I dont see any benefit in terms of query execution time. Any though about that? have you ever experience boos in performance by using the

injective: true

property?

I believe there are some reverse lookup optimizations that can be done for injective lookups, resulting in the ability to make better use of the column index when an expression containing a lookup is used within a query filter. There's a whole section of the docs that discusses this here: https://druid.apache.org/docs/latest/querying/lookups#query-rewrites

Yep, you’d see optimizations with group by queries on injective lookups since the lookup would be able to use the column index while grouping, and apply lookup post grouping. Also, I believe there are planning time optimizations which can be applied on injective lookups. As far as the result correctness is concerned - It’s not a good practice to mark lookups as injective, when they are not. Druid doesn’t make any guarantees about the correctness of those results. You have one sample case which works. There would be multiple others which don’t. Also, the sample case which succeeds can fail in future versions because of the mere fact that Druid does not make guarantees about the correctness of these results. Relying on an explicitly unsupported behaviour is a bad thing to carry on in production. As far as the performance is concerned - Without knowing anything about the setup, the query shape, the number of lookups, the number of results, it’s too hard to qualify if the lookup being injective was supposed to provide a major boost or not. You should ask the inverse question instead - Does adding “injective” = true in the lookup configuration make it slower? If so, then it is definitely a bug.

thanks you all for the replies! I was just curious about the feature because most of the lookups we use have unique combination of key,value, I wanted to see whether this would bring an improvement. I believe that I don't see it because the size of the lookups, which is max 5000 rows. Also depends on the type of query that has being made, basically whether it involves the

Query Rewrites

functionality. Bottom line, I would not change anything