letian-jiang opened a new pull request, #3086: URL: https://github.com/apache/drill/pull/3086
# [DRILL-8555](https://issues.apache.org/jira/browse/DRILL-8555): Physical plan cache for parameterized SQL queries ## Description This PR adds a Drillbit-scoped physical plan cache with HBase and Iceberg support, reusing plans across connections to reduce repeated validation and optimization. It is disabled by default; enable it with `ALTER SESSION SET planner.enable_plan_cache = true`. ## Method ```mermaid flowchart LR SQL[SQL] --> Template[SQL template] Template --> Cache{Plan cache} Cache -->|Hit| Bind[Bind literals] Cache -->|Miss| Planner[Plan query] Bind --> Plan[Physical plan] Planner --> Plan ``` Eligible literals become parameter slots in the SQL template. A hit binds current values to a fresh copy of the cached plan; a miss plans the query normally and populates the cache after successful execution. ## Safety guarantees - Only supported read queries are cached. Volatile or query-context functions and unsupported scans bypass caching. Structural literals and function configuration arguments stay in the cache key. - Reuse requires matching effective options, plugin configurations and table compatibility versions. Binding checks parameter types and numeric ranges; compatibility or reconstruction failures fall back to normal planning. - Cached plans are immutable, and each execution gets a fresh operator graph. Plugins opt in explicitly and must rebuild scan state from current parameters and metadata while preserving residual filters. ## Benchmark Measured on one local Drillbit with a Ryzen 7 9700X, 30 GiB RAM and OpenJDK 21. HBase used a 1,000-row mini-cluster for point reads, 50-row range scans and column filters. Iceberg ran all 22 TPC-H queries over eight SF0.01 tables (Q15 used a derived table; Q19 exposed the common equijoin). ### Cache hits | Workload | Planning off → hit (reduction) | End-to-end off → hit (reduction) | | --- | ---: | ---: | | HBase point read | 50 → 14 ms (**72.0%**) | 66 → 29 ms (**56.1%**) | | HBase range scan | 40 → 12 ms (**70.0%**) | 55 → 26 ms (**52.7%**) | | HBase column filter | 32 → 10 ms (**68.8%**) | 44 → 22 ms (**50.0%**) | | Iceberg TPC-H SF0.01 | 2,161 → 355 ms (**83.6%**) | 18,831 → 16,906 ms (**10.2%**) | The benefit is largest when planning dominates latency: it accounts for roughly **73–76%** of the reported HBase baseline latency, and hits reduce end-to-end latency by **50–56%**. Analytical queries also benefit: Iceberg planning drops **83.6%**, reducing aggregate end-to-end latency by **10.2%**. HBase values are medians of three run medians (15 pairs per workload per run). Iceberg values are sums of per-query medians (three pairs per query), not suite wall time. Percentages are latency reductions relative to cache-off execution. ### Cache misses A separate warmed comparison cleared the plan cache before each cache-on query and drained background writes before both modes. Misses added a median **3–5 ms** of paired end-to-end latency for HBase (45 pairs per workload). For Iceberg, the sum of query end-to-end medians changed from **20,624 to 21,806 ms (+5.7%)**. Miss overhead was modest in these local measurements, while hits provided the largest benefit for short queries. ## Documentation - `PLAN_CACHE_DESIGN.md`: basic principles and supported scope. - `PLAN_CACHE_PLUGIN_GUIDE.md`: plugin APIs and scan reconstruction requirements. -- 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]
