u/Impressive-Buddy7489

When running a test suite of 250 concurrent users we identified clickhouse as a bottleneck due to the nature of our queries. It’s a simple select query with multiple where clauses. The problem is that the table itself contains 200+ million records and our goals is to optimise the query in such a way that we get results under 1 seconds.

Things we have tried out

1. Projections
2. Skinny materialized view (this was working fine but was showing stale data in UI because of the nature of mv and using refreshable mv was cpu intensive process)
3. Partitioning of data
4. Horizontal scaling

PS : we are using a OLAP as OLTP (ik it’s wrong). Problem is happening when we are trying to performs a search it’s scanning all the 200 million records.

Is there any way to optimise this ?