kosiew commented on PR #27435: URL: https://github.com/apache/datafusion/pull/27435#issuecomment-6880400693
# CASE WHEN Lookup Table Benchmark Summary ## Overview The benchmark compares `HEAD` (baseline) against `bench-26609` (proposed change) for Apache DataFusion's `CASE WHEN` evaluation. **Main finding:** The proposed change delivers substantial speedups for integer lookup cases, but little improvement for string-based lookups. ### Key Results | Metric | Result | |---|---| | Best speedup | **8.18×** (`i32 → i32`, 2 entries) | | Integer → string | **1.46–2.31×** faster | | String → integer | **~1×** (little change) | | Largest observed regression | **36% slower** (`decimal128 → i32`, 8 entries) | ## Performance by Data Type | Lookup Type | Observed Speedup | Assessment | |---|---|---| | `i32 → i32` | 1.0–8.18× | Strong improvement | | `i64 → i32` | 1.03–7.52× | Strong for small tables | | `i32 → utf8` | 1.46–2.31× | Consistent improvement | | `utf8 → i32` | Approximately 1× | Little change | | `decimal128 → i32` | Mostly unchanged | Mixed, with one regression | ## Effect of Lookup Table Size The optimization is particularly effective for small lookup tables. Its benefit declines as the number of entries increases. ### Integer Lookup Speedups | Entries | `i32 → i32` | `i64 → i32` | |---:|---:|---:| | 2 | 8.18× | 7.52× | | 4 | 6.53× | 5.10× | | 8 | 4.60× | 3.56× | | 12 | 3.77× | 2.88× | | 16 | 2.80× | 2.11× | | 24 | 2.33× | 1.04× | | 32 | 1.63× | 1.06× | | 64 | 1.00× | 1.03× | All benchmarks process 8,192 rows. **Example:** For `i32 → i32` with two entries: - `HEAD`: 42.3 µs - `bench-26609`: 5.2 µs - **Speedup: 8.18×** With 64 entries, both versions take approximately 44.7 µs. **Observation:** The performance advantage diminishes as lookup table size increases. ## Potential Concerns ### 1. Decimal Regression For `decimal128 → i32` with eight entries: - `HEAD`: 47.5 µs - `bench-26609`: 64.4 µs - **Regression: approximately 36% slower** This result warrants investigation. ### 2. String Lookups The `utf8 → i32` benchmarks show little meaningful improvement. Some cases are approximately 1–5% slower, although measurement variability may account for part of the difference. ### 3. Scaling Behavior The integer lookup optimization loses much of its advantage as the number of entries increases. For `i32 → i32`, the speedup declines from 8.18× with two entries to 1.00× with 64 entries. This suggests that the optimization is most useful for small lookup tables. ## Review Assessment The benchmarks provide strong evidence that the change improves performance for small integer-based `CASE WHEN` lookup tables. The results also show consistent improvements for `i32 → utf8` cases. However, two areas deserve further investigation: 1. **Decimal regression:** Determine why the eight-entry `decimal128 → i32` case becomes approximately 36% slower. 2. **Large lookup tables:** Confirm that the implementation uses an appropriate fallback when the optimized approach no longer provides a meaningful advantage. ## Conclusion **The optimization is promising, with substantial performance gains in its strongest cases.** The largest improvements occur with small integer lookup tables, where speedups reach 8.18×. The benefits diminish with larger tables, while string-based lookups remain largely unchanged. The decimal regression should be investigated before approval. **Overall assessment:** Strong targeted improvement, but not a universal performance gain across all data types and lookup table sizes. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
