[
https://issues.apache.org/jira/browse/DRILL-8555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18125091#comment-18125091
]
ASF GitHub Bot commented on DRILL-8555:
---------------------------------------
letian-jiang commented on PR #3086:
URL: https://github.com/apache/drill/pull/3086#issuecomment-6062617598
> **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?
1. I chose HBase because I expect high-concurrency, short-query workloads to
benefit most from plan caching: planning can account for a substantial share of
their latency. Since Drill is also an analytical engine, I wanted to test a
data lake format such as Iceberg to evaluate the benefit for analytical
workloads as well.
2. I agree that the progression from DFS formats without filter pushdown, to
Parquet, and then JDBC is reasonable. I plan to add support for these commonly
used plugins in follow-up PRs and document the roadmap in 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)
