The optimizer opportunistically enumerates the cheap GroupSorted-based eager aggregation plans when the child plan fragment provides an ordering on the local grouping columns.
This limitation avoids increasing the optimization search space and time. However, in some cases hash-based eager aggregation produces the cheapest plan. abstract plans can be used in such cases to force eager aggregation. advanced_grouping must be enabled to use such an abstract plan; otherwise the eager aggregation abstract plan is rejected.
In the example above, if r has no index on (r1, r2), and if r is large but has few r1--r2 distinct pairs of values, a hash join with eager grouping over r is the best plan, forced by this abstract plan:
1> select r1, sum(s1) 2> from r, s 3> where r2=s2 4> group by r1 5> plan 6> "(group_hashing 7> (h_join 8> (group_hashing 9> (t_scan r) 10> ) 11> (t_scan s) 12> ) 13> )" 14> go
QUERY PLAN FOR STATEMENT 1 (at line 1). Optimized using the Abstract Plan in the PLAN clause. STEP 1 The type of query is SELECT. 5 operator(s) under root |ROOT:EMIT Operator | | |HASH VECTOR AGGREGATE Operator | | GROUP BY | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Using Worktable3 for internal storage. | | Key Count: 1| | | | |HASH JOIN Operator (Join Type: Inner Join) | | | Using Worktable2 for internal storage. | | | Key Count: 1 | | | | | | |HASH VECTOR AGGREGATE Operator | | | | GROUP BY | | | | Evaluate Grouped COUNT AGGREGATE. | | | | Using Worktable1 for internal storage. | | | | Key Count: 2 | | | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | r | | | | | Table Scan. | | | | | Forward Scan. | | | | | Positioning at start of table. | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | s | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Using I/O Size 2 Kbytes for data pages. | | | | With LRU Buffer Replacement Strategy for data pages. r1 ----------- ----------- 1 2 2 4 (2 rows affected)
The hash vector aggregate operator eagerly aggregates the scan of r, as requested by the abstract plan.
