explain() Method

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/debugging/explain.md)

The `explain()` method shows the execution plan for a DataStore query, helping you understand what operations will be performed and what SQL will be generated.

## Basic Usage[​](#basic "Direct link to Basic Usage")

```
from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")

from chdb import datastore as pd

ds = pd.read_csv("sales.csv")

query = (ds
    .filter(ds['amount'] > 1000)
    .groupby('region')
    .agg({'amount': ['sum', 'mean']})
    .sort('sum', ascending=False)
)

# View execution plan
query.explain()
```

## Syntax[​](#syntax "Direct link to Syntax")

```
explain(verbose=False) -> None
```

**Parameters:**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  | | --- | --- | --- | --- | | `verbose` bool `False` Show additional metadata | | | | | | | |

## Output Format[​](#output-format "Direct link to Output Format")

### Standard Output[​](#standard "Direct link to Standard Output")

```
================================================================================
Execution Plan (in execution order)
================================================================================

 [1] 📊 Data Source: file('sales.csv', 'csv')

Operations:
────────────────────────────────────────────────────────────────────────────────
    ️  Segment 1 [chDB] (from source): Operations 2-5
    ️  Note: SQL operations after Pandas ops use Python() table function

 [2] 🚀 [chDB] WHERE: "amount" > 1000
 [3] 🚀 [chDB] GROUP BY: region
 [4] 🚀 [chDB] AGGREGATE: sum(amount), avg(amount)
 [5] 🚀 [chDB] ORDER BY: sum DESC

────────────────────────────────────────────────────────────────────────────────
Final State: 📊 Pending (lazy, not yet executed)
             └─> Will execute when print(), .to_df(), .execute() is called

────────────────────────────────────────────────────────────────────────────────
Generated SQL Query:
────────────────────────────────────────────────────────────────────────────────

SELECT region, SUM(amount) AS sum, AVG(amount) AS mean
FROM file('sales.csv', 'csv')
WHERE "amount" > 1000
GROUP BY region
ORDER BY sum DESC

================================================================================
```

### Icons Legend[​](#icons "Direct link to Icons Legend")

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Icon Meaning|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 📊 Data source|  |  |  |  | | --- | --- | --- | --- | | 🚀 chDB (SQL) operation|  |  | | --- | --- | | 🐼 pandas operation | | | | | | | |

### Verbose Output[​](#verbose "Direct link to Verbose Output")

```
query.explain(verbose=True)
```

Verbose mode shows additional details for each operation, including the full SQL query with internal row-order tracking mechanisms.

---

## Three Execution Phases[​](#phases "Direct link to Three Execution Phases")

The explain output shows operations in three phases:

### Phase 1: SQL Query Building (Lazy)[​](#phase-1 "Direct link to Phase 1: SQL Query Building (Lazy)")

Operations that compile to SQL:

```
  1. Source: file('sales.csv', 'CSVWithNames')
  2. Filter: amount > 1000      
  3. GroupBy: region
  4. Aggregate: sum(amount)
```

### Phase 2: Execution Point[​](#phase-2 "Direct link to Phase 2: Execution Point")

When a trigger occurs:

```
  5. Execute SQL -> DataFrame
     Trigger: to_df() called
```

### Phase 3: DataFrame Operations[​](#phase-3 "Direct link to Phase 3: DataFrame Operations")

Operations after execution:

```
  6. [pandas] pivot_table(...)
  7. [pandas] apply(custom_func)
```

---

## Understanding the Plan[​](#understanding "Direct link to Understanding the Plan")

### Source Information[​](#source "Direct link to Source Information")

```
Source: file('sales.csv', 'CSVWithNames')
```

- `file()` - ClickHouse file() table function
- `'CSVWithNames'` - File format with header

Other source types:

```
Source: s3('bucket/data.parquet', ...)
Source: mysql('host', 'db', 'table', ...)
Source: __dataframe__  (pandas DataFrame input)
```

### Filter Operations[​](#filter "Direct link to Filter Operations")

```
Filter: amount > 1000 AND status = 'active'
```

Shows the WHERE clause that will be applied.

### GroupBy and Aggregate[​](#groupby "Direct link to GroupBy and Aggregate")

```
GroupBy: region, category
Aggregate: sum(amount), avg(amount), count(id)
```

Shows GROUP BY columns and aggregation functions.

### Sort Operations[​](#sort "Direct link to Sort Operations")

```
Sort: sum DESC, region ASC
```

Shows ORDER BY clause.

### Limit Operations[​](#limit "Direct link to Limit Operations")

```
Limit: 10
Offset: 100
```

Shows LIMIT and OFFSET.

---

## Engine Information[​](#engine "Direct link to Engine Information")

When using verbose mode, you can see which engine will be used:

```
Filter: amount > 1000
  - Engine: chdb
  - Pushdown: Yes

Apply: custom_function
  - Engine: pandas
  - Pushdown: No
```

### Pushdown[​](#pushdown "Direct link to Pushdown")

- **Yes**: Operation will be executed at the data source (SQL)
- **No**: Operation requires pandas execution

---

## Examples[​](#examples "Direct link to Examples")

### Simple Query[​](#example-simple "Direct link to Simple Query")

```
from pathlib import Path
Path("data.csv").write_text("""\
name,age,city,salary,department
Alice,25,NYC,55000,Engineering
Bob,30,LA,65000,Product
Charlie,35,NYC,80000,Engineering
Diana,28,SF,70000,Design
Eve,42,NYC,95000,Product
""")

ds = pd.read_csv("data.csv")
ds.filter(ds['age'] > 25).explain()
```

```
================================================================================
Execution Plan (in execution order)
================================================================================

 [1] 📊 Data Source: file('data.csv', 'csv')

Operations:
────────────────────────────────────────────────────────────────────────────────
    ️  Segment 1 [chDB] (from source): Operations 2-2

 [2] 🚀 [chDB] WHERE: "age" > 25

────────────────────────────────────────────────────────────────────────────────
Generated SQL Query:
────────────────────────────────────────────────────────────────────────────────

SELECT * FROM file('data.csv', 'csv') WHERE "age" > 25

================================================================================
```

### Complex Aggregation[​](#example-complex "Direct link to Complex Aggregation")

```
query = (ds
    .filter(ds['date'] >= '2024-01-01')
    .filter(ds['amount'] > 100)
    .select('region', 'category', 'amount')
    .groupby('region', 'category')
    .agg({
        'amount': ['sum', 'mean', 'count']
    })
    .sort('sum', ascending=False)
    .limit(20)
)
query.explain()
```

```
================================================================================
Execution Plan (in execution order)
================================================================================

 [1] 📊 Data Source: file('sales.csv', 'csv')

Operations:
────────────────────────────────────────────────────────────────────────────────
    ️  Segment 1 [chDB] (from source): Operations 2-8

 [2] 🚀 [chDB] WHERE: "date" >= '2024-01-01'
 [3] 🚀 [chDB] WHERE: "amount" > 100
 [4] 🚀 [chDB] SELECT: region, category, amount
 [5] 🚀 [chDB] GROUP BY: region, category
 [6] 🚀 [chDB] AGGREGATE: sum(amount), avg(amount), count(amount)
 [7] 🚀 [chDB] ORDER BY: sum DESC
 [8] 🚀 [chDB] LIMIT: 20

────────────────────────────────────────────────────────────────────────────────
Generated SQL Query:
────────────────────────────────────────────────────────────────────────────────