hey friends, i come to you with a query optimization question, we have the following query:

select sum(impression_count) from metrics where user_id = xxx and product_id = xxxx

this query even tho it has more selectivity is slower than this one:

select sum(impression_count) from metrics where user_id = xxx

we currently are partitioning on the

user_id

and have a bloom filter on

product_id,user_id

is it because of the partitioning (?) also we do see an elevation of

numEntriesScannedInFilter

when we also add the

product_id

in the query but without it is pretty much 0, what do you recommend to do in this case.

What’s the cardinality of product_id. Bloom filter is not useful in this case. You should try inverted index on product_id

yes, user_id has sorted index

try inverted index on product_id, it should be fast

this one basically: https://docs.pinot.apache.org/basics/indexing/inverted-index#bitmap-inverted-index

Yes

so i could potentially just leave the bloom filter there? there’s bloom filter for both

user_id

and

product_id

you can but its not needed

will that improve the

numEntriesScannedInFilter

?

@Kishore G bloom filter not being useful is it because

product_id

is not a partition key so bloom filter wouldn’t prune many segments? Is it a rule of thumb that bloom filter should only enable for cols that are part of the primary key in general?