This message was deleted.

Do you have a sense of the level of cardinality of that field? My understanding of how this is processed, is the distinct list has to be passed from the Historical back to the broker for final list merging and counting ... if the lists are small that shouldn't be an issue ... but if the lists are large (i.e. in the millions) it may not fare so well. There is also an approximate distinct count algorithm that you can use. Fyi I just ran a quick test in a 50b row datasource, select select count distinct (exact) on 1.3b rows, cardinality of 6, ran pretty fast ...

The performance is mainly going to depend on the number of distinct values of

CLP_ID

. If it's very high you could get better performance using approximation like

APPROX_COUNT_DISTINCT_DS_HLL

Thanks for the quick response @John Kowtko, @Gian Merlino Below is my table structure, Here CMO_ID and CODE are of high Cardinality. We have around 2.5 million distinct CMO_ID and also around 0.2 million distinct CODES.

`CMO_ID` String,
    `CODE` String,
    `LABEL` String,
    `CLP_ID` Int64,
    `PLP_ID` Int64,
    `CATEGORY` String,
    `CATEGORY_ID` UInt8,
    `ROLL_UP_DATE` Date

Sketches could help (like

APPROX_COUNT_DISTINCT_DS_HLL

). Also, making CLP_ID a string instead of number might be faster for exact count distincts, if you need them. (A guess based on strings having dictionaries made.)

SELECT
    cmo_id,
    count(distinct clp_id) as clp_id, 
    count( distinct plp_id) as plp_id
from
    test.test_table
where
    code in (
        '785P10',
        'POL0000',
        'IOPL0129',
        'Z79IKL',
        'FLKMD',
        'HJYIWSXD',
        'FIOKDS',
        '7990WJ',
        'LPOXEIO',
        'IISNEW'
    )
    and roll_up_date BETWEEN '2020-01-01'
    and '2023-12-31'
group by
    cmo_id
limit
    10

Here is the same query, which will be serve my use case

Sounds good. Please note that by default, count(distinct) is a (good) approximation, for performance reasons. Or you can use various other approximations, like count_distinct_ds_hll, etc. Also, if you really need exact, you can set query options to get that, but it will definitely be slower. I recommend the approximations if possible, or test both and see which works for you.

Generally, numbers are faster for count distincts (whether exact or approx)

Okay, let me check the performance and revert back!