explain() method

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

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

explain(verbose=False) -> None

Parameters:

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

Output Format

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

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

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

The explain output shows operations in three phases:

Phase 1: SQL Query Building (Lazy)

Operations that compile to SQL:

Source: file('sales.csv', 'CSVWithNames')
Filter: amount > 1000      
GroupBy: region
Aggregate: sum(amount)

Phase 2: Execution Point

When a trigger occurs:

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

Phase 3: DataFrame Operations

Operations after execution:

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

Understanding the Plan

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: amount > 1000 AND status = 'active'

Shows the WHERE clause that will be applied.

GroupBy and Aggregate

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

Shows GROUP BY columns and aggregation functions.

Sort Operations

Sort: sum DESC, region ASC

Shows ORDER BY clause.

Limit Operations

Limit: 10
Offset: 100

Shows LIMIT and OFFSET.

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

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

Examples

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

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:
────────────────────────────────────────────────────────────────────────────────

SELECT region, category, 
       SUM(amount) AS sum, 
       AVG(amount) AS mean, 
       COUNT(amount) AS count
FROM file('sales.csv', 'csv')
WHERE "date" >= '2024-01-01' AND "amount" > 100
GROUP BY region, category
ORDER BY sum DESC
LIMIT 20

================================================================================

Mixed SQL and pandas

When operations cannot be fully pushed to SQL, the plan shows multiple segments:

query = (ds
    .filter(ds['age'] > 25)           # SQL
    .groupby('city')                   # SQL
    .agg({'salary': 'mean'})           # SQL
    .apply(lambda x: x * 1.1)          # pandas (triggers segment split)
    .filter(ds['mean'] > 50000)        # SQL (new segment)
)
query.explain()

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

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

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

 [2] 🚀 [chDB] WHERE: "age" > 25
 [3] 🚀 [chDB] GROUP BY: city
 [4] 🚀 [chDB] AGGREGATE: avg(salary)
 [5] 🐼 [Pandas] APPLY: lambda
 [6] 🚀 [chDB] WHERE: "mean" > 50000

================================================================================

Debugging with explain()

Check Filter Logic

# Verify your filter is correct
query = ds.filter((ds['age'] > 25) & (ds['city'] == 'NYC'))
query.explain()
# Output shows: Filter: age > 25 AND city = 'NYC'

Verify Column Selection

# Check column pruning
query = ds.select('name', 'age').filter(ds['age'] > 25)
query.explain()
# Output shows: SELECT name, age FROM ... WHERE age > 25

Understand Aggregation

# Check aggregation functions
query = ds.groupby('dept').agg({'salary': ['sum', 'mean', 'std']})
query.explain()
# Output shows: SELECT dept, SUM(salary), AVG(salary), stddevPop(salary)

Best Practices

Check Before Executing Large Queries

# Always explain first for large data
query = ds.complex_pipeline()
query.explain()  # Check plan

# If plan looks correct
result = query.to_df()  # Execute

Use Verbose for Debugging

# When something seems wrong
query.explain(verbose=True)
# Shows engine selection and pushdown info

Compare with to_sql()

# explain() shows the plan
query.explain()

# to_sql() shows just the SQL
print(query.to_sql())

# Both useful for different purposes

Check Pushdown Status

# Verbose mode shows if operations are pushed down
query.explain(verbose=True)

# If Pushdown: No, operation runs in pandas
# Consider restructuring query for better performance

Get started

DataStore

Configuration

Debugging

Basic Usage

Syntax

Output Format

Standard Output

Icons Legend

Verbose Output

Three Execution Phases

Phase 1: SQL Query Building (Lazy)

Phase 2: Execution Point

Phase 3: DataFrame Operations

Understanding the Plan

Source Information

Filter Operations

GroupBy and Aggregate

Sort Operations

Limit Operations

Engine Information

Pushdown

Examples

Simple Query

Complex Aggregation

Mixed SQL and pandas

Debugging with explain()

Check Filter Logic

Verify Column Selection

Understand Aggregation

Best Practices

Check Before Executing Large Queries

Use Verbose for Debugging

Compare with to_sql()

Check Pushdown Status

​Basic Usage

​Syntax

​Output Format

​Standard Output

​Icons Legend

​Verbose Output

​Three Execution Phases

​Phase 1: SQL Query Building (Lazy)

​Phase 2: Execution Point

​Phase 3: DataFrame Operations

​Understanding the Plan

​Source Information

​Filter Operations

​GroupBy and Aggregate

​Sort Operations

​Limit Operations

​Engine Information

​Pushdown

​Examples

​Simple Query

​Complex Aggregation

​Mixed SQL and pandas

​Debugging with explain()

​Check Filter Logic

​Verify Column Selection

​Understand Aggregation

​Best Practices

​ Check Before Executing Large Queries

​ Use Verbose for Debugging

​ Compare with to_sql()

​ Check Pushdown Status

Basic Usage

Syntax

Output Format

Standard Output

Icons Legend

Verbose Output

Three Execution Phases

Phase 1: SQL Query Building (Lazy)

Phase 2: Execution Point

Phase 3: DataFrame Operations

Understanding the Plan

Source Information

Filter Operations

GroupBy and Aggregate

Sort Operations

Limit Operations

Engine Information

Pushdown

Examples

Simple Query

Complex Aggregation

Mixed SQL and pandas

Debugging with explain()

Check Filter Logic

Verify Column Selection

Understand Aggregation

Best Practices

Check Before Executing Large Queries

Use Verbose for Debugging

Compare with to_sql()

Check Pushdown Status