How to Debug Queries in ClickHouse

Galaxy Glossary

How do I debug slow or failing queries in ClickHouse?

Use EXPLAIN, system tables, and query settings to trace, profile, and optimize slow or failing ClickHouse statements.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How do I see the execution plan?

Run EXPLAIN before your SELECT to view the optimizer’s planned steps. Use EXPLAIN SYNTAX for parsed SQL, EXPLAIN AST for the abstract syntax tree, and EXPLAIN QUERY TREE for the full plan.

Which system tables reveal real-time activity?

system.processes lists currently running queries with memory and read metrics. system.part_log and system.query_log store historical details such as duration, rows read, and exception_code.

How can I profile resource usage per query?

Append SET send_logs_level='trace' to capture granular CPU, IO, and network statistics in server logs. Combine with profile_events columns from system.query_log to pinpoint bottlenecks.

Why is my SELECT on Orders slow?

Check if the ORDER BY or WHERE clauses hit the primary key. Use EXPLAIN QUERY TREE SELECT * FROM Orders WHERE order_date = today(). If the plan shows an Unsorted read, add a data-skipping index or adjust the ORDER BY key.

How do I capture the exact error stack?

Set trace_id in the session, execute the failing statement, then inspect system.trace_log filtered by that id. The stack trace reveals function names and line numbers inside the ClickHouse engine.

Can I run a dry-run without reading data?

Yes. SET max_threads = 0 combined with EXPLAIN validates syntax and plan generation without touching disks.

Best practice: use settings inline

Attach SETs after the query to keep scripts self-contained: EXPLAIN QUERY TREE SELECT … SETTINGS max_threads=1, allow_experimental_analyzer=1.

Best practice: sample large tables

Add SAMPLE 0.1 to SELECTs when investigating logic rather than performance. This returns 10 % of rows, reducing wait time.

Best practice: leverage log_comment

Tag every ad-hoc query with SET log_comment = 'debug-order-pipeline' so you can grep logs later.

Quick recap

Use EXPLAIN for the plan, system.processes for live stats, and system.query_log for history. Inline settings and comments keep debugging reproducible.

Why How to Debug Queries in ClickHouse is important

How to Debug Queries in ClickHouse Example Usage


-- Detect slow JOIN between Orders and Customers
EXPLAIN QUERY TREE
SELECT c.name, o.total_amount
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= today() - 30
SETTINGS log_comment = 'debug-orders-customers';

-- Monitor while it runs
SELECT elapsed, read_rows, memory_usage
FROM system.processes
WHERE log_comment = 'debug-orders-customers';

How to Debug Queries in ClickHouse Syntax


EXPLAIN [SYNTAX|AST|QUERY TREE] SELECT ...

SELECT *
FROM system.processes
WHERE query LIKE '%Customers%';

SELECT query, rows_read, memory_usage, ProfileEvents['ReadCompressedBytes']
FROM system.query_log
WHERE query_kind = 'Select'
  AND event_date = today();

-- Example ecommerce debugging sequence
EXPLAIN QUERY TREE
SELECT customer_id, sum(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
ORDER BY total_spent DESC
SETTINGS max_threads = 1, allow_experimental_analyzer = 1;

Common Mistakes

Ignoring the primary key in WHERE clauses. This forces a full table scan, causing high disk IO. Always align filters with the table’s ORDER BY columns or create a data-skipping index.
Running EXPLAIN without settings used in production. The plan may differ when <code>allow_experimental_analyzer</code> or <code>max_threads</code> are set. Mirror production settings during debugging.