In a grouped query, the database applies the HAVING clause after GROUP BY aggregation—meaning it groups every row before discarding ones that fail the filter. If the filter has nothing to do with aggregation, this is wasted work.
PolarDB automatically moves eligible filter conditions from the HAVING clause to the WHERE clause, so the database filters rows before grouping. This reduces the volume of data that reaches the grouping stage and improves query performance.
How it works
The optimizer inspects the HAVING clause and identifies conditions that depend only on GROUP BY columns—not on aggregate functions. It rewrites the query to apply those conditions in the WHERE clause before grouping begins. Any remaining conditions stay in HAVING.
What can be pushed down
A condition is eligible when it depends solely on columns in the GROUP BY clause, or on columns that are identical to the grouped columns.
What cannot be pushed down
Conditions that reference aggregate functions—such as MAX(), SUM(), or COUNT()—cannot be pushed down. Aggregate values are not available until after grouping, so these conditions must remain in HAVING.
For example, the following query cannot have MAX(c) > 12 pushed down because MAX(c) is an aggregate. Only t1.a > 2 is eligible:
SELECT t1.a, MAX(t1.b)
FROM t1
GROUP BY t1.a
HAVING (t1.a > 2) AND (MAX(c) > 12);After pushdown, the query becomes:
SELECT t1.a, MAX(t1.b)
FROM t1
WHERE (t1.a > 2)
GROUP BY t1.a
HAVING (MAX(c) > 12);Supported versions
This feature requires one of the following engine versions. To check your cluster version, see Query the engine version.
MySQL 8.0.1, revision version 8.0.1.1.42 or later
MySQL 8.0.2, revision version 8.0.2.2.10 or later
Configure condition pushdown
Use the following parameters to control HAVING-to-WHERE condition pushdown. For details on setting parameters, see Configure cluster and node parameters.
| Parameter | Scope | Description |
|---|---|---|
loose_polar_optimizer_switch | Global and session | Enables or disables condition pushdown. Valid values: having_cond_pushdown=ON (default) and having_cond_pushdown=OFF. |
loose_having_cond_pushdown_mode | Global | Specifies which nodes run condition pushdown. Valid values: REPLICA_ON (default), ON, and OFF. |
loose_having_cond_pushdown_mode values
| Value | Behavior |
|---|---|
REPLICA_ON (default) | Enables condition pushdown on read-only nodes only. |
ON | Enables condition pushdown on all nodes. |
OFF | Disables condition pushdown on all nodes. |
Examples
Example 1: simple condition on a GROUP BY column
The condition t1.a > 2 references only the GROUP BY column, so the optimizer moves it to the WHERE clause. The aggregate condition MAX(c) > 12 stays in HAVING.
Before pushdown:
SELECT t1.a, MAX(t1.b)
FROM t1
GROUP BY t1.a
HAVING (t1.a > 2) AND (MAX(c) > 12);After pushdown:
SELECT t1.a, MAX(t1.b)
FROM t1
WHERE (t1.a > 2)
GROUP BY t1.a
HAVING (MAX(c) > 12);Example 2: partial pushdown in a compound condition
When a compound condition mixes GROUP BY column references with non-GROUP BY column references, the optimizer extracts only the pushable parts. Here, t1.a < 3 can be extracted from the second OR branch and moved to WHERE.
Before pushdown:
SELECT t1.a, MAX(t1.b), t1.c
FROM t1
GROUP BY t1.a
HAVING ((t1.a = t1.c) AND (t1.a > 1)) OR ((t1.a < 3) AND (t1.c > 3));After pushdown:
SELECT t1.a, MAX(t1.b), t1.c
FROM t1
WHERE ((t1.a = t1.c) AND (t1.a > 1)) OR (t1.a < 3)
GROUP BY t1.a
HAVING ((t1.a = t1.c) AND (t1.a > 1)) OR ((t1.a < 3) AND (t1.c > 3));The full originalHAVINGclause is preserved to guarantee correct results. TheWHEREclause filters rows before grouping; theHAVINGclause handles the final correctness check after grouping.