PolarDB:Eliminate redundant GROUP BY/ORDER BY

Scenario 1: Grouping by primary or unique key

Trigger conditions:

• The GROUP BY clause includes a table's primary key.

• Other columns in the GROUP BY list are functionally determined by that key.

Business scenario: Calculate the total order count per user and display their username.

Original SQL:

-- user_id is the primary key of the user table and uniquely determines user_name.
SELECT
    u.user_id,
    u.user_name,
    COUNT(o.order_id)
FROM user u
JOIN orders o ON u.user_id = o.user_id
GROUP BY
    u.user_id,
    u.user_name;

What the optimizer does: Because user_id is the primary key, the FD user_id -> user_name holds. The optimizer simplifies GROUP BY u.user_id, u.user_name to GROUP BY u.user_id, removing the redundant grouping on user_name.

Optimized SQL:

SELECT
    u.user_id,
    u.user_name,
    COUNT(o.order_id)
FROM user u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

Verify the optimization:

SHOW WARNINGS;

The Message column shows the rewritten query with GROUP BY u.user_id only, confirming that u.user_name has been removed from the grouping key.

Scenario 2: Grouping by constants

Trigger conditions:

• All columns in the GROUP BY clause appear as equality constants in the WHERE clause.

Business scenario: Query a specific row by exact column values.

Original SQL:

SELECT a, b FROM t1 WHERE a = 1 AND b = 1 GROUP BY a, b;

What the optimizer does: Both a and b are constants in the WHERE clause, so the result set contains at most one row. The optimizer removes the GROUP BY and adds LIMIT 1.

Optimized SQL:

SELECT a, b FROM t1 WHERE a = 1 AND b = 1 LIMIT 1;

Verify the optimization:

SHOW WARNINGS;

The Message column shows the rewritten query with LIMIT 1 and no GROUP BY clause.

Scenario 3: Multi-table join with complex grouping (TPC-H Q10)

Trigger conditions:

• A multi-table join propagates a primary key's FD through equi-join conditions.

• All non-key columns in the GROUP BY list are transitively determined by the primary key.

Original SQL (TPC-H Q10):

SELECT
    c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM customer, orders, lineitem, nation
WHERE c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND c_nationkey = n_nationkey
  -- Other filter conditions...
GROUP BY
    c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
ORDER BY revenue DESC
LIMIT 20;

What the optimizer does:

1. Primary key dependency: c_custkey is the primary key of the customer table, so it determines c_name, c_acctbal, c_phone, c_address, c_comment, and c_nationkey.

2. Dependency propagation: The join condition c_nationkey = n_nationkey and the fact that n_nationkey is the primary key of nation means c_custkey also determines n_name.

3. Simplification: All non-key columns in the GROUP BY list are functionally dependent on c_custkey. The optimizer simplifies the clause to GROUP BY c_custkey.

Optimized SQL:

SELECT
    c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM customer, orders, lineitem, nation
WHERE c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND c_nationkey = n_nationkey
  -- Other filter conditions...
GROUP BY c_custkey
ORDER BY revenue DESC
LIMIT 20;

Verify the optimization:

SHOW WARNINGS;

The Message column shows the rewritten query with GROUP BY c_custkey only, confirming that all other columns have been removed from the grouping key.

Scenario 4: ORDER BY simplification with a computed column

Trigger conditions:

• The ORDER BY clause includes a column that is a deterministic function of other columns in the same clause.

• That column is defined as UNIQUE NOT NULL.

Table schema:

CREATE TABLE t1 (
  a INT,
  b INT,
  c INT AS (a + b) UNIQUE NOT NULL
);

Original SQL:

EXPLAIN SELECT a, b FROM t1 ORDER BY a, b, c;

What the optimizer does: Column c is computed from a and b (c = a + b) and defined as UNIQUE NOT NULL, establishing the FD (a, b) -> c. The optimizer simplifies ORDER BY a, b, c to ORDER BY a, b.

Verify the optimization:

SHOW WARNINGS;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                       |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+

The rewritten query in the Message column confirms that c has been removed from the sort key.