[
https://issues.apache.org/jira/browse/DRILL-8555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18125056#comment-18125056
]
ASF GitHub Bot commented on DRILL-8555:
---------------------------------------
cgivre commented on PR #3086:
URL: https://github.com/apache/drill/pull/3086#issuecomment-6061538611
**Question: plans to support the other storage plugins?**
Right now only HBase and Iceberg opt in (`PLAN_CACHE_DESIGN.md:75`). What
are the plans for expanding this to the rest of the plugins? I'd like to see it
cover as many as possible. The most heavily used ones (Parquet and the other
`dfs` formats, and JDBC) are the ones that would benefit most, and they're
currently excluded.
I understand why they can't simply be switched on. On a hit, the cache
rebinds literals in Drill expressions. But most plugins turn those literals
into native state during planning and keep only the result:
| Plugin | Value-dependent state built at planning time |
|---|---|
| Parquet | Row-group / file pruning based on filter values |
| All `dfs` formats | Directory (partition) pruning, and a fixed file list |
| JDBC | Pushed-down SQL text with literals inlined |
| Mongo, Elasticsearch, Splunk, HTTP | Native query / request built from the
filter |
| Kafka | Offset / timestamp ranges |
Reusing those plans without rebuilding that state would silently return
wrong results. So each plugin needs to keep the source expression in its scan
JSON and redo pushdown/pruning on bind, as `PLAN_CACHE_PLUGIN_GUIDE.md`
describes.
A possible path, roughly in order of effort:
1. **`dfs` formats without filter pushdown** (CSV/TSV, JSON, Avro, etc.):
filtering stays in Drill's Filter operator, so the plugin only needs to re-list
files and redo directory pruning on bind, plus a table version that catches
schema changes.
2. **Parquet:** redo row-group/partition pruning for the new values, reusing
the existing Parquet metadata cache to keep it cheap.
3. **JDBC and other pushdown plugins:** a shared "rebuild pushdown on bind"
hook in the plugin API, so each plugin doesn't reinvent it.
It would be worth measuring planning time against bind-time rebuild cost for
`dfs`/Parquet, since re-listing and re-pruning eat into what a hit saves.
Given how large this PR already is, I'm fine with plugin expansion coming in
follow-ups. But I'd like to understand the roadmap, and to make sure the plugin
API in this PR makes opting in as cheap as possible. Could you add the plan (or
JIRAs for each plugin) to the design doc?
> 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)
