title_wip

---

## Behavior Differences[​](#behavior-differences "Direct link to Behavior Differences")

### Row Order[​](#row-order "Direct link to Row Order")

In performance mode, row order is **not guaranteed** for any operation. This includes:

- Filter results
- GroupBy aggregation results
- `head()` / `tail()` without explicit `sort_values()`
- `first()` / `last()` aggregations

If you need ordered results, add an explicit `sort_values()`:

```
config.use_performance_mode()

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

# Unordered (fast)
result = ds.groupby("region")["revenue"].sum()

# Ordered (still fast, just adds ORDER BY)
result = ds.groupby("region")["revenue"].sum().sort_values()
```

### GroupBy Results[​](#groupby-results "Direct link to GroupBy Results")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Aspect Pandas mode Performance mode|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Group key location Index (via `set_index`) Regular column|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Group order Sorted by key (default) Arbitrary order|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | NULL groups Excluded (default `dropna=True`) Included|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Column format MultiIndex for multi-agg Flat names (`col_func`)| `first()`/`last()` Deterministic (row order) Non-deterministic (`any()`/`anyLast()`) | | | | | | | | | | | | | | | | | |

### Aggregation[​](#aggregation "Direct link to Aggregation")

```
config.use_performance_mode()

# Sum of all-NaN group returns NULL (not 0)
# Count returns native uint64 (not forced int64)
# No -If wrappers: sum() instead of sumIf()
result = ds.groupby("cat")["val"].sum()
```

### Single-SQL Execution[​](#single-sql "Direct link to Single-SQL Execution")

In performance mode, `ColumnExpr` groupby aggregation (e.g., `ds[condition].groupby('col')['val'].sum()`) is executed as a **single SQL query** instead of the two-step process used in pandas mode:

```
config.use_performance_mode()

# Pandas mode: two SQL queries (filter → materialize → groupby)
# Performance mode: one SQL query (WHERE + GROUP BY in same query)
result = ds[ds["rating"] > 3.5].groupby("category")["revenue"].sum()

# Generated SQL (single query):
# SELECT category, sum(revenue) FROM data WHERE rating > 3.5 GROUP BY category
```

This eliminates the intermediate DataFrame materialization and can significantly reduce memory usage and execution time.

---

## Comparison with Execution Engine[​](#vs-execution-engine "Direct link to Comparison with Execution Engine")

Performance mode (`compat_mode`) and execution engine (`execution_engine`) are **independent configuration axes**:

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Config Controls Values|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `execution_engine` **Which engine** runs the computation `auto`, `chdb`, `pandas`| `compat_mode` **Whether** to reshape output for pandas compatibility `pandas`, `performance` | | | | | | | | |

Setting `compat_mode='performance'` automatically sets `execution_engine='chdb'`, since performance mode is designed for SQL execution.

```
from chdb.datastore.config import config

# These are independent
config.use_chdb()              # Force chDB engine, keep pandas compat
config.use_performance_mode()  # Force chDB + remove pandas overhead
```

---

## Testing with Performance Mode[​](#testing "Direct link to Testing with Performance Mode")

When writing tests for performance mode, results may differ from pandas in row order and structural format. Use these strategies:

### Sort-then-compare (aggregations, filters)[​](#sort-then-compare "Direct link to Sort-then-compare (aggregations, filters)")

```
# Sort both sides by the same columns before comparing
ds_result = ds.groupby("cat")["val"].sum()
pd_result = pd_df.groupby("cat")["val"].sum()

ds_sorted = ds_result.sort_index()
pd_sorted = pd_result.sort_index()
np.testing.assert_array_equal(ds_sorted.values, pd_sorted.values)
```

### Value-range check (first/last)[​](#value-range-check "Direct link to Value-range check (first/last)")

```
# first() with any() returns an arbitrary element from the group
result = ds.groupby("cat")["val"].first()
for group_key in groups:
    assert result.loc[group_key] in group_values[group_key]
```

### Schema-and-count (LIMIT without ORDER BY)[​](#schema-and-count "Direct link to Schema-and-count (LIMIT without ORDER BY)")

```
# head() without sort_values: row set is non-deterministic
result = ds.head(5)
assert len(result) == 5
assert set(result.columns) == expected_columns
```

---

## Best Practices[​](#best-practices "Direct link to Best Practices")

### 1. Enable early in your script[​](#enable-early "Direct link to 1. Enable early in your script")

```
from chdb.datastore.config import config

config.use_performance_mode()

# All subsequent operations benefit
ds = pd.read_parquet("data.parquet")
result = ds[ds["amount"] > 100].groupby("region")["amount"].sum()
```

### 2. Add explicit sorting when order matters[​](#explicit-sort "Direct link to 2. Add explicit sorting when order matters")

```
# For display or downstream processing that expects order
result = (ds
    .groupby("region")["revenue"].sum()
    .sort_values(ascending=False)
)
```

### 3. Use for batch/ETL workloads[​](#batch-etl "Direct link to 3. Use for batch/ETL workloads")

```
config.use_performance_mode()

# ETL pipeline — order doesn't matter, throughput does
summary = (ds
    .filter(ds["date"] >= "2024-01-01")
    .groupby(["region", "product"])
    .agg({"revenue": "sum", "quantity": "sum", "rating": "mean"})
)
summary.to_df().to_parquet("summary.parquet")
```

### 4. Switch modes within a session[​](#switch-modes "Direct link to 4. Switch modes within a session")

```
# Performance mode for heavy computation
config.use_performance_mode()
aggregated = ds.groupby("cat")["val"].sum()

# Back to pandas mode for exact-match comparison
config.use_pandas_compat()
detailed = ds[ds["val"] > 100].head(10)
```

---

## Related Documentation[​](#related "Direct link to Related Documentation")

- [Execution Engine](/docs/chdb/configuration/execution-engine) — Engine selection (auto/chdb/pandas)
- [Performance Guide](/docs/chdb/guides/pandas-performance) — General optimization tips
- [Key Differences from pandas](/docs/chdb/guides/pandas-differences) — Behavioral differences

- [Overview](#overview)
  - [What Performance Mode Disables](#what-it-disables)- [Enabling Performance Mode](#enabling)
    - [Using config object](#using-config)- [Using module-level functions](#using-functions)- [Using convenience imports](#using-imports)- [When to Use Performance Mode](#when-to-use)- [Behavior Differences](#behavior-differences)
        - [Row Order](#row-order)- [GroupBy Results](#groupby-results)- [Aggregation](#aggregation)- [Single-SQL Execution](#single-sql)- [Comparison with Execution Engine](#vs-execution-engine)- [Testing with Performance Mode](#testing)
            - [Sort-then-compare (aggregations, filters)](#sort-then-compare)- [Value-range check (first/last)](#value-range-check)- [Schema-and-count (LIMIT without ORDER BY)](#schema-and-count)- [Best Practices](#best-practices)
              - [1. Enable early in your script](#enable-early)- [2. Add explicit sorting when order matters](#explicit-sort)- [3. Use for batch/ETL workloads](#batch-etl)- [4. Switch modes within a session](#switch-modes)- [Related Documentation](#related)

Was this page helpful?