Skew Hints
Skew hints mitigate long-running instances caused by a few hot keys. The query engine supports join salting, DISTINCT aggregation bucketing and window sorting strategies while preserving SQL results. Use them when query profiles show skew. Replication and extra shuffles also add cost.
Note:
The examples describe behavior checked in VeloDB Core 26.1.3. The Core 26.1 series is in Public Preview. Check the warehouse version and use
EXPLAINto confirm whether a rewrite applies.
Prepare data
The examples require CREATE, INSERT, and SELECT privileges in the current database. These small tables verify syntax and results, rather than demonstrating a performance benefit:
CREATE TABLE skew_left (id INT, k INT, v INT)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1;
CREATE TABLE skew_right (id INT, k INT, v INT)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1;
INSERT INTO skew_left VALUES (1,1,10),(2,1,20),(3,2,30),(4,3,40);
INSERT INTO skew_right VALUES (1,1,100),(2,2,200);
Example 1: Use a join skew hint
Salt known hot join keys
For known hot join keys, use shuffle[skew(<column>(<literal>, ...))]. The query engine randomly salts hot rows on one side, replicates matching rows on the other side across salt values, and shuffles on the original key plus the salt.
- For ordinary INNER and LEFT joins, the hot column must come from the left side. For RIGHT joins, it must come from the right. ASOF INNER/LEFT/RIGHT variants follow the corresponding side rule and still require their MATCH_CONDITION and equality keys.
- The column must be a direct column reference in a join equality key. Expressions, casts and columns outside the equality keys do not trigger rewriting. The hint requires shuffle. Mark, FULL, SEMI, and ANTI joins are excluded.
- One specified hot column is handled per rewrite. Skew on both sides can still leave replication costs and very large join results.
Write the hint on the join:
SELECT l.id AS left_id, r.id AS right_id
FROM skew_left l
JOIN[shuffle[skew(l.k(1))]] skew_right r ON l.k = r.k
ORDER BY left_id, right_id;
left_id right_id
1 1
2 1
3 2
The same distribution and hot key can be specified in LEADING:
SELECT /*+ leading(l shuffle[skew(l.k(1))] r) */
l.id AS left_id, r.id AS right_id
FROM skew_left l JOIN skew_right r ON l.k = r.k
ORDER BY left_id, right_id;
left_id right_id
1 1
2 1
3 2
Here l.k is the hot column and 1 is its known hot value. Use literals compatible with the column type. If join order changes, check that the hot column remains on the required side.
Example 2: Use a DISTINCT skew hint
Distribute deduplication work across buckets
Place [skew] before the argument of a single-argument DISTINCT aggregate:
SELECT k, COUNT(DISTINCT [skew] id) AS distinct_ids
FROM skew_left
GROUP BY k
ORDER BY k;
k distinct_ids
1 2
2 1
3 1
Supported functions are COUNT, SUM, SUM0, and single-argument GROUP_CONCAT. Rewriting requires GROUP BY, exactly one aggregate function and one DISTINCT argument at that level, a direct column argument, and that argument not already included in GROUP BY. Otherwise the query engine keeps the regular aggregation. The rewrite distributes deduplication by group keys and hash buckets before merging by the original group keys, reducing high-NDV hot-group pressure.
Example 3: Use a window skew hint
Sort locally before the window exchange
Place [skew] after PARTITION BY in the window specification:
SELECT id, SUM(v) OVER (
PARTITION BY [skew] k ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_sum
FROM skew_left
ORDER BY id;
id running_sum
1 10
2 30
3 30
4 40
This strategy locally sorts before shuffling by partition keys and merging sorted streams. Within a partition-key group, only the bottom window node requiring shuffle uses it. Upper nodes reuse distribution and ordering. A global window without PARTITION BY does not gain partition-level parallelism, and a very large single partition can still be slow.
Parameters
| Parameter | Type | Default | Range and effect |
|---|---|---|---|
skew_rewrite_join_salt_explode_factor | Session integer | 0 | 0 chooses automatically from the planned execution-instance count; 1 to 65535 sets the salt/replication multiplier. This is independent of table bucket count |
skew_rewrite_agg_bucket_num | Session integer | 1024 | 1 to 65535, controls hash buckets for the DISTINCT rewrite |
Verify the rewrite
Prefix the example SELECT with EXPLAIN and inspect accepted hints and the added salt, aggregation or sorting nodes. Enable enable_profile=true and compare per-instance rows, time, memory and shuffle bytes for the same SQL. A hint can be inapplicable. The final plan determines whether rewriting occurred.
Choose hot values from actual data. Start with automatic join salting: larger multipliers replicate more rows on the other side and may increase network/memory usage or cause OOM. Excessive aggregate buckets add shuffle and merge costs. Window skew handling optimizes sorting rather than reducing required rows. Measure again when data distribution changes.
Drop the example tables when you have finished testing:
DROP TABLE skew_left;
DROP TABLE skew_right;