explain() Method

[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[​](#example-mixed "Direct link to 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()[​](#debugging "Direct link to Debugging with explain()")

### Check Filter Logic[​](#debug-filter "Direct link to 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[​](#debug-select "Direct link to 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[​](#debug-agg "Direct link to 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[​](#best-practices "Direct link to Best Practices")

### 1. Check Before Executing Large Queries[​](#best-practice-1 "Direct link to 1. 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
```

### 2. Use Verbose for Debugging[​](#best-practice-2 "Direct link to 2. Use Verbose for Debugging")

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

### 3. Compare with to\_sql()[​](#best-practice-3 "Direct link to 3. 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
```

### 4. Check Pushdown Status[​](#best-practice-4 "Direct link to 4. 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
```

- [Basic Usage](#basic)- [Syntax](#syntax)- [Output Format](#output-format)
      - [Standard Output](#standard)- [Icons Legend](#icons)- [Verbose Output](#verbose)- [Three Execution Phases](#phases)
        - [Phase 1: SQL Query Building (Lazy)](#phase-1)- [Phase 2: Execution Point](#phase-2)- [Phase 3: DataFrame Operations](#phase-3)- [Understanding the Plan](#understanding)
          - [Source Information](#source)- [Filter Operations](#filter)- [GroupBy and Aggregate](#groupby)- [Sort Operations](#sort)- [Limit Operations](#limit)- [Engine Information](#engine)
            - [Pushdown](#pushdown)- [Examples](#examples)
              - [Simple Query](#example-simple)- [Complex Aggregation](#example-complex)- [Mixed SQL and pandas](#example-mixed)- [Debugging with explain()](#debugging)
                - [Check Filter Logic](#debug-filter)- [Verify Column Selection](#debug-select)- [Understand Aggregation](#debug-agg)- [Best Practices](#best-practices)
                  - [1. Check Before Executing Large Queries](#best-practice-1)- [2. Use Verbose for Debugging](#best-practice-2)- [3. Compare with to\_sql()](#best-practice-3)- [4. Check Pushdown Status](#best-practice-4)

Was this page helpful?