Query against a materialized view with uniqExact column fails due to memory limit

Question

I have a materialized view with the following structure:

CREATE MATERIALIZED VIEW events_daily
    ENGINE = AggregatingMergeTree()
    ORDER BY (
        owner_id, user_id, event_type_id, event_day, field1, field2
    )
AS SELECT
       toStartOfDay(event_datetime) as event_day,
       owner_id,
       user_id,
       event_type_id,
       field1,
       field2,
       countState() as count,
       uniqExactState(message_id, field1, field2) as unique_count
   FROM raw_events
   GROUP BY owner_id, user_id, event_type_id, event_day;

When I'm trying to perform a select for a user with big amount of records, I'm getting a memory limit error:

Memory limit (for query) exceeded: would use 9.42 GiB (attempt to allocate chunk of 134217728 bytes), maximum: 9.31 GiB: While executing AggregatingTransform.

The SELECT query I'm trying to execute:

SELECT event_day, 
  event_type_id,
  countMerge(count) as count,
  uniqExactMerge(unique_count) as unique_count
FROM events_daily WHERE owner_id = xxx AND event_day >= '2022-04-05 00:00:00' AND event_day <= '2022-05-05 23:00:00'
GROUP BY owner_id, event_type_id, event_day
ORDER BY event_day, event_type_id

If I change the dates condition to a narrower one (e.g. querying data for 1 week instead of 1 month), it works. Also if I remove uniqExactMerge from the SELECT clause, it also works (and pretty fast).

So is there a solution to make a query with uniqExactMerge() for heavy set of data? Or should I alter the whole architecture in some way?

Answer 1

By default, if my memory is correct you are going to use up to 10GB of RAM. You can increase this value: https://clickhouse.com/docs/en/operations/settings/query-complexity/#settings_max_memory_usage

You should also consider using function a less intensive function like uniqExactMerge as it uses a lot of memory. Try using a lighter version like: uniq: https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/uniq/