CODEWITHSUNDEEP
clickhouse
Thomas Johnson
Thomas Johnson

Reputation: 11688

Why does clickhouse need so much memory for a simple query?

I have a simple query like:

SELECT w, x, y, z FROM my_table
WHERE microsecs >= 1234 AND microsecs <= 2345 AND a='foo' AND b='bar'
ORDER BY microsecs ASC

My primary key on the table is (a, b, microsecs).

The query should return about 295 million rows. I'm using clickhouse-driver to stream the data with execute_iter.

Despite this, I need to set max_memory_usage to about 48GB for the query to succeed.

Why does clickhouse need so much memory for a simple query, and how can I reduce the required memory?

Upvotes: 1

Views: 1823

Answers (1)

Thomas Johnson
Thomas Johnson

Reputation: 11688

I asked this question in the clickhouse_en telegram channel, and I was pointed to the documentation on optimizing read in-order. I changed my ORDER BY from ORDER BY microsecs to ORDER BY a, b, microsecs and the memory required dropped dramatically.

Upvotes: 3

Related Questions