[
https://issues.apache.org/jira/browse/DRILL-8555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18121888#comment-18121888
]
ASF GitHub Bot commented on DRILL-8555:
---------------------------------------
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) |
| -
> Physical plan cache for parameterized SQL queries
> -------------------------------------------------
>
> Key: DRILL-8555
> URL: https://issues.apache.org/jira/browse/DRILL-8555
> Project: Apache Drill
> Issue Type: New Feature
> Components: Query Planning & Optimization
> Reporter: Letian Jiang
> Priority: Major
>
> h2. Motivation
> Repeated queries that differ only in literals are a common workload, yet each
> query currently requires full planning. This adds unnecessary latency and
> wastes CPU time.
> h2. Brief Design
> Add a per-Drillbit, in-memory plan cache. Drill automatically parameterizes
> eligible SQL queries and reuses a cached physical plan with the current
> literal values, without changes to client queries.
> h2. Plan reuse safety
> * *Engine:* matches the SQL template and checks relevant option changes,
> plugin configuration changes, and whether base table versions still match.
> * *Storage plugin:* explicitly opts in, provides a plan-compatible table
> version, and is responsible for rebuilding its predicate-dependent scan work
> from the rebound expressions.
> h2. Expected benefit
> Short, frequently executed queries such as HBase reads can benefit
> substantially. Analytical queries with significant planning cost can benefit
> as well.
> h2. Implementation status
> A working implementation is available, including HBase, Paimon and Iceberg
> integrations. A pull request will follow.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
