Performance and Optimizations

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/best-practices/index.md)

This section contains tips and best practices for improving performance with ClickHouse.
We recommend users read [Core Concepts](/docs/parts) as a precursor to this section,
which covers the main concepts required to improve performance.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Topic Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Query optimization guide](/docs/optimize/query-optimization) Start here for query optimization fundamentals, covering common scenarios and performance techniques to improve query execution speed.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Primary indexes advanced guide](/docs/guides/best-practices/sparse-primary-indexes) Deep dive into ClickHouse's unique sparse primary indexing system, how it differs from traditional databases, and best practices for optimal indexing strategies.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Query parallelism](/docs/optimize/query-parallelism) Learn how ClickHouse parallelizes query execution using processing lanes and `max_threads` settings, including how to inspect and optimize parallel execution.| [Partitioning key](/docs/optimize/partitioning-key) Master partition key selection to dramatically improve query performance by enabling efficient data segment pruning and avoiding common partitioning pitfalls.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Data skipping indexes](/docs/optimize/skipping-indexes) Apply secondary indexes strategically to skip irrelevant data blocks and accelerate filtered queries on non-primary key columns.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`PREWHERE` optimization](/docs/optimize/prewhere) Understand how `PREWHERE` automatically reduces I/O by filtering data before reading unnecessary columns, plus how to monitor its effectiveness.| [Bulk inserts](/docs/optimize/bulk-inserts) Maximize ingestion throughput and reduce resource overhead by batching data insertions effectively.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Asynchronous inserts](/docs/optimize/asynchronous-inserts) Improve insert performance by leveraging server-side batching to reduce client-side complexity and increase throughput for high-frequency insertions.|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Avoid mutations](/docs/optimize/avoid-mutations) Design append-only workflows that eliminate costly `UPDATE` and `DELETE` operations while maintaining data accuracy and performance.| [Avoid nullable columns](/docs/optimize/avoid-nullable-columns) Reduce storage overhead and improve query performance by using default values instead of nullable columns where possible.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Avoid `OPTIMIZE FINAL`](/docs/optimize/avoidoptimizefinal) Understand when you should and shouldn't use `OPTIMIZE TABLE FINAL`| [Analyzer](/docs/operations/analyzer) Leverage ClickHouse's new query analyzer to identify performance bottlenecks and optimize query execution plans for better efficiency.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Query profiling](/docs/operations/optimizing-performance/sampling-query-profiler) Use the sampling query profiler to analyze query execution patterns, identify performance hot spots, and optimize resource usage.|  |  |  |  | | --- | --- | --- | --- | | [Query cache](/docs/operations/query-cache) Accelerate frequently executed `SELECT` queries by enabling and configuring ClickHouse's built-in query result caching.| [Testing hardware](/docs/operations/performance-test) Run ClickHouse performance benchmarks on any server without installation to evaluate hardware capabilities. (Not applicable to ClickHouse Cloud) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Was this page helpful?