You do not expect the Optimizer to use join index jiin_aggr (see Example: All Inner Joins with Aggregation in Join Index Definition) in its plan for the following query because the condition b1=b2 AND f1=f4 is not covered by the join index defined by jiin_aggr. Therefore, the Optimizer specifies a full-table scan to retrieve the specified rows.
The EXPLAIN report text indicates that the Optimizer does not choose jiin_aggr to cover the query:
EXPLAIN SELECT a1, e1, SUM(x1) AS total FROM t1, t2, t4 WHERE b1=b2 AND f1=f4 AND a1>2 GROUP BY 1, 2;
Explanation
---------------------------------------------------------------------
1) First, we lock HONG_JI.t4 for read on a reserved RowHash to prevent a global deadlock.
2) Next, we lock HONG_JI.t2 for read on a reserved RowHash to prevent a global deadlock.
3) We lock HONG_JI.t1 for read on a reserved RowHash to prevent a global deadlock.
4) We do an all-AMPs RETRIEVE step from HONG_JI.t1 by way of an
all-rows scan with a condition of ("HONG_JI.t1.a1 > 2") into Spool
4 (all_amps), which is duplicated on all AMPs. The size of Spool
4 is estimated with no confidence to be 2 rows. The estimated
time for this step is 0.03 seconds.
5) We do an all-AMPs JOIN step from HONG_JI.t2 by way of an all-rows
scan with no residual conditions, which is joined to Spool 4 (Last
Use). HONG_JI.t2 and Spool 4 are joined using a product join,
with a join condition of ("b1 = HONG_JI.t2.b2"). The result goes
into Spool 5 (all_amps), which is duplicated on all AMPs. The
size of Spool 5 is estimated with no confidence to be 3 rows. The
estimated time for this step is 0.04 seconds.
6) We do an all-AMPs JOIN step from HONG_JI.t4 by way of an all-rows
scan with no residual conditions, which is joined to Spool 5 (Last
Use). HONG_JI.t4 and Spool 5 are joined using a product join,
with a join condition of ("f1 = HONG_JI.t4.f4"). The result goes
into Spool 3 (all_amps), which is built locally on the AMPs. The
size of Spool 3 is estimated with no confidence to be 2 rows. The
estimated time for this step is 0.04 seconds.
7) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed globally, then placed
in Spool 6. The size of Spool 6 is estimated with no confidence
to be 2 rows. The estimated time for this step is 0.05 seconds.
8) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 2 rows. The estimated time for this step is 0.04 seconds.
9) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1.