Resource tour

## Query optimization techniques and performance tuning[​](#query-optimization "Direct link to Query optimization techniques and performance tuning")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 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) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## Monitoring[​](#monitoring "Direct link to Monitoring")

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Advanced dashboard](/docs/cloud/manage/monitor/advanced-dashboard) Use the built in advanced dashboard to monitor service health and performance|  |  |  |  | | --- | --- | --- | --- | | [Prometheus integration](/docs/integrations/prometheus) Use Prometheus to monitor Cloud services|  |  | | --- | --- | | [Cloud Monitoring Capabilities](/docs/use-cases/observability/cloud-monitoring) Get an overview of built in monitoring capabilities and integration options | | | | | | | |

## Security[​](#security "Direct link to Security")