[jira] [Commented] (DRILL-8555) Physical plan cache for parameterized SQL queries

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

    [ 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)