# Identifying Slow Queries

Slow queries can impact ClickHouse performance, especially under high load or with inefficient queries or schema design. Whether you’re using ClickHouse on Elestio via the dashboard, accessing it inside a Docker Compose container, or running CLI queries, ClickHouse offers built-in tools to detect, diagnose, and optimize performance bottlenecks. This guide explains how to capture slow queries using system tables, measure query latency, and improve performance through tuning and query optimization.

### **Inspecting Slow Queries from the Terminal**

ClickHouse logs query profiling information by default, which you can access via system tables. This allows you to identify long-running or resource-intensive queries directly from SQL.

#### **Connect to ClickHouse via Terminal**

Use the ClickHouse client to connect to your instance:

```
clickhouse-client -h <host> --port <port> --user <username> --password <password>
```

Replace <span class="s1">&lt;host&gt;</span>, <span class="s1">&lt;port&gt;</span>, <span class="s1">&lt;username&gt;</span>, and <span class="s1">&lt;password&gt;</span> with your credentials from the Elestio dashboard.

[![image.png](https://docs.elest.io/uploads/images/gallery/2025-06/scaled-1680-/V4Ximage.png)](https://docs.elest.io/uploads/images/gallery/2025-06/V4Ximage.png)

#### **View Recent Slow Queries**

ClickHouse logs query performance stats in the <span class="s1">system.query\_log</span> table. To view the 10 most recent queries that took longer than 1 second:

```sql
SELECT
  query_start_time,
  query_duration_ms,
  query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
ORDER BY query_start_time DESC
LIMIT 10;
```

You can adjust the <span class="s1">query\_duration\_ms</span> threshold to capture slower or more critical queries.

### **Analyzing Inside Docker Compose**

If your ClickHouse instance is running inside Docker Compose, you can inspect query logs and system performance from inside the container.

#### **Access the ClickHouse Container**

Open a shell session inside the running container:

```
docker-compose exec clickhouse bash
```

Then run the ClickHouse client:

```
clickhouse-client --user root
```

If a password is required, append <span class="s1">--password &lt;yourpassword&gt;</span> to the command.

#### **Query the system.query\_log Inside the Container**

Run the same slow query inspection SQL as above to analyze performance issues:

```sql
SELECT query_start_time, query_duration_ms, query
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
ORDER BY query_start_time DESC
LIMIT 10;
```

### **Using the System Metrics &amp; Events Tables**

ClickHouse includes system tables that expose performance-related metrics in real time.

#### **Check Overall Query Performance**

You can use the <span class="s2">system.metrics</span> table to view metrics like query execution time, memory usage, and background operations:

```sql
SELECT *
FROM system.metrics
WHERE value != 0
ORDER BY value DESC;
```

For cumulative statistics like total queries processed, check the <span class="s1">system.events</span> table:

```sql
SELECT *
FROM system.events
WHERE value > 0
ORDER BY value DESC;
```

### **Understanding and Resolving Common Bottlenecks**

Slow performance in ClickHouse is often caused by suboptimal queries, improper indexing (i.e., no primary key usage), disk I/O, or high memory usage.

#### **Common Causes of Slow Queries:**

- <span class="s1">**Large table scans:**</span> Caused by missing filtering conditions or lack of primary key usage.
- <span class="s1">**JOINs on unindexed keys:**</span> Inefficient joins can result in full-table scans.
- <span class="s1">**High cardinality aggregations:**</span> Especially costly without optimization (e.g., using <span class="s2">uniqExact()</span>).
- <span class="s1">**High insert latency:**</span> Triggered by too frequent small batch writes.
- <span class="s1">**Disk bottlenecks:**</span> Heavy merges or large result sets can overload I/O.

#### **Best Practices to Avoid Slow Queries:**

- <span class="s1">**Use appropriate filtering:**</span> Always filter with indexed columns (usually primary keys).
- <span class="s1">**Avoid SELECT \*:**</span> Specify only the needed columns.
- <span class="s1">**Use sampling when possible:**</span> ClickHouse supports SAMPLE clause on MergeTree tables.
- <span class="s1">**Use LIMIT:**</span> Avoid returning large result sets when debugging.
- <span class="s1">**Optimize JOINs:**</span> Prefer <span class="s2">ANY INNER JOIN</span> or <span class="s2">JOIN ... USING</span> for performance.

### **Optimizing with Configuration Changes**

ClickHouse performance can be tuned via its configuration files (<span class="s2">config.xml</span> and <span class="s2">users.xml</span>) or environment variables. For Docker Compose setups, these can be overridden via <span class="s2">docker-compose.override.yml</span>.

#### **Adjust Query and Memory Settings Dynamically**

Some performance-related settings can be changed per session or globally:

```sql
SET max_memory_usage = 2000000000;
SET max_threads = 4;
SET log_queries = 1;
```

To make permanent changes, modify your <span class="s1">config.xml</span> or <span class="s1">users.xml</span> inside the container volume mount.