> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pawsql.com/llms.txt
> Use this file to discover all available pages before exploring further.
> ## Agent Instructions
> PawSQL 是一个产品：Cloud 是公网部署形态，Engine / Optimizer / Auditor / Advisor / Patroller 是同一产品的组件与交付形态，不是彼此独立的产品。 / PawSQL is a single product: Cloud is the public deployment form, while Engine / Optimizer / Auditor / Advisor / Patroller are components and delivery forms of the same product, not separate products.
> 术语以站内术语表为准：SQL 审核对应英文 SQL Review，查询重写对应 Query Rewrite，索引推荐对应 Index Recommendation；英文内容统一用 Review，不用 Audit。 / Use the site glossary for terminology: 审核 is SQL Review, 重写 is Query Rewrite, 索引推荐 is Index Recommendation; English content uses Review, never Audit.
> 引用能力范围或版本支持时以对应页面为准；标注 unknown、或 status 非 published 的内容表示尚未经产品核实，不应作为事实引用。 / Cite capability scope and version support from the corresponding page; content marked unknown, or with a status other than published, is not yet product-verified and must not be cited as fact.
# Big Data SQL Optimization
> Optimize Hive and big-data SQL using partition pruning, bucket-aware joins, skew analysis, aggregation optimization, window functions, and global sort analysis.
PawSQL Big Data SQL Optimization targets performance problems that are common in Hive and other large-scale SQL processing environments but less common in traditional relational databases.
The analysis focuses on partition pruning, bucket-aware joins, data skew, aggregation skew, window functions, global sorting, and large-scale Shuffle.
## Overview
In big-data SQL, performance is often determined not by a single operator but by the interaction between scan volume, Shuffle, partition design, and data skew.
A SQL statement with only a few dozen lines may trigger:
* Terabytes of data scanning
* Large network Shuffles
* Long-running Reduce tasks
* COUNT DISTINCT skew
* Hot partitions in window functions
* Global sorting that blocks the entire job
PawSQL applies big-data-specific optimization rules and analysis models to these patterns.
```mermaid theme={null}
flowchart LR
A["Hive SQL"] --> B["Partition / Bucket Analysis"]
B --> C["Skew & Shuffle Analysis"]
C --> D["Rewrite / Strategy"]
D --> E["Optimized Big Data SQL"]
```
## Why Big Data SQL Needs Specialized Rules
Traditional database bottlenecks often involve:
* Random I/O
* Index access
* Join selection
* Single-node CPU and memory
In Hive and other big-data engines, the dominant costs are more often:
* Data scan volume
* Shuffle
* Task count
* Data skew
* Partition and bucket design
* Global sorting and aggregation
Big-data SQL therefore requires optimization methods that differ from OLTP databases.
## Key Capabilities
### Partition pruning analysis
Detect cases where functions, expressions, or inappropriate predicates on partition columns prevent partition pruning.
For example:
```sql theme={null}
WHERE date_format(dt, 'yyyy-MM-dd') = '2026-09-09'
```
may prevent the engine from pruning directly on `dt`.
### Bucket join analysis
Analyze whether join columns and bucket design are compatible, including:
* Mismatched bucket columns
* Bucket-count relationships
* SMB Join conditions
* Small-table opportunities for Map Join
### COUNT DISTINCT skew
Detect scenarios where `COUNT(DISTINCT ...)` may create reducer skew and suggest multi-stage aggregation or equivalent rewrites.
### GROUP BY skew
Analyze Group Key distribution and identify cases where a small number of hot keys overload individual Reduce tasks.
### Window-function skew
For `ROW_NUMBER()`, `RANK()`, `SUM() OVER (...)`, and similar functions, identify imbalanced partition keys that may create hot tasks.
### Global sort optimization
Detect the cost of `ORDER BY` global sorting.
For Top-N scenarios, PawSQL can evaluate local sorting, window-based techniques, or multi-stage Top-N strategies.
### UNION and aggregation optimization
Identify:
* Cases where `UNION ALL` is sufficient
* Extra Shuffle caused by UNION deduplication
* Skew in global aggregation
* Multi-stage aggregation opportunities
## Typical Optimization Areas
```mermaid theme={null}
flowchart LR
A["Partition Pruning
Reduce the amount of data scanned."]
B["Bucket-aware Join
Use bucket design to reduce Shuffle."]
C["Data Skew
Detect hot keys and long-tail tasks."]
D["Aggregation
Optimize COUNT DISTINCT and GROUP BY."]
E["Window Functions
Avoid overloaded partitions."]
F["Global Sort
Reduce global-sort and single-reducer bottlenecks."]
```
## Example: Partition Pruning
Suppose the table is:
```text theme={null}
sales
PARTITIONED BY (dt STRING)
```
Inefficient form:
```sql theme={null}
SELECT *
FROM sales
WHERE substr(dt, 1, 7) = '2026-09';
```
A more pruning-friendly form may be:
```sql theme={null}
SELECT *
FROM sales
WHERE dt >= '2026-09-01'
AND dt < '2026-10-01';
```
The latter makes the required partition range easier for the engine to identify directly.
## Example: Global Top-N
Original SQL:
```sql theme={null}
SELECT *
FROM events
ORDER BY score DESC
LIMIT 100;
```
On a very large data set, global sorting may be expensive.
Depending on engine capabilities, PawSQL can analyze whether the query can use:
* Local Top-N
* Partitioned sorting
* Two-stage Top-N
* Window functions
to reduce full global sorting.
## Big Data Optimization Is About Data Movement
For big-data SQL, one of the most important questions is usually not:
> “Which index is better?”
but:
**How much data must be scanned? How much data must move? Why is the slowest task slow?**
PawSQL therefore treats scan volume, Shuffle, partitioning, bucketing, and skew as first-class optimization signals.
## Related Capabilities
## Related Use Cases