Summary
Just because the SQL is the same doesn’t mean they should always plan and execute the same. This article lists the steps one would need to follow to understand why a query ran faster or slower than other times.
Reported Issue
Running the same query may end up in different query completion times.
Relevant Versions
This applies to all the versions of Dremio.
Steps to resolve
Variable factors include:
- UI vs JDBC vs ODBC vs ADBC vs REST - Try not to compare performance across client types. Look under “Resource Allocation” to confirm query type (and other WLM information);
- Whether query ran on same coordinator or not? Look under Job Summary -> Coordinator IP;
- Whether same set of reflections were considered, matched and chosen. Check the reflection and materialization ids in the acceleration profile to confirm;
- Plan Cache Used? Shown in the planning tab;
- Result Cache Used? Shown in the planning tab;
- Catalog Access Stats? Shown in the planning tab;
- Shows whether inline metadata refresh occurred. Which dataset was slowest?
Intermediate level debugging includes:
- Using visual profile to find slowest operator;
- Identify expensive operator in visual profile (rows and memory);
- Physical join type/implementation and order
More advanced debugging includes:
- Interpreting operator statistics for C3 cache hits and row group pruning;
- Identifying data skew in specific operators;
- Reading scan operators to see if filters pushed down;
- Partition Filter vs Non-Partition Filter vs Runtime Filter;
- Compare materialization ids in physical plan to identify stale data;
- In profile server, download the cluster metadata to see node stats and source types.