H2 10x Slower Than HSQLDB And 6x Than Apache Derby For Specific ...

H2 plan:

SELECT A, SUM(B) FROM ( SELECT DISTINCT A, B FROM PUBLIC.TST ORDER BY 1 ) T /* SELECT DISTINCT A, B FROM PUBLIC.TST /++ PUBLIC.TST_A_IDX ++/ ORDER BY 1 /++ index sorted ++/ */ GROUP BY A

HSQLDB plan:

isDistinctSelect=[false] isGrouped=[true] isAggregated=[true] columns=[ COLUMN: A nullable SUM arg=[ COLUMN: B nullable ] [range variable 1 join type=INNER table=SYSTEM_SUBQUERY alias=T cardinality=0 access=FULL SCAN join condition = [index=SYS_IDX_10094 ] ]] groupColumns=[COLUMN: A ] PARAMETERS=[] SUBQUERIES[ [level=1 isDistinctSelect=[true] isGrouped=[false] isAggregated=[false] columns=[ COLUMN: PUBLIC.TST.A nullable COLUMN: PUBLIC.TST.B nullable ] [range variable 1 join type=INNER table=TST cardinality=3000000 access=FULL SCAN join condition = [index=SYS_IDX_10091 ] ] ] order by=[ COLUMN: PUBLIC.TST.A ] ]]

Derby log:

Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 1.00 optimizer estimated cost: 833450.14 Source result set: Grouped Aggregate ResultSet: Number of opens = 1 Rows input = 3000000 Has distinct aggregate = false In sorted order = false Sort information: Number of merge runs=6 Number of rows input=3000000 Number of rows output=3000000 Size of merge runs=[524287, 524287, 524287, 524287, 524287, 378565] Sort type=external constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2969453.00 optimizer estimated cost: 833450.14 Source result set: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 3000000 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 2969453.00 optimizer estimated cost: 833450.14 Source result set: Sort ResultSet: Number of opens = 1 Rows input = 3000000 Rows returned = 3000000 Eliminate duplicates = true In sorted order = false Sort information: Number of merge runs=8 Number of rows input=3000000 Number of rows output=3000000 Size of merge runs=[335519, 335519, 335519, 335519, 335519, 335519, 335519, 335519] Sort type=external constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2969453.00 optimizer estimated cost: 833450.14 Source result set: Table Scan ResultSet for TST at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3000000 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of pages visited=27273 Number of rows qualified=3000000 Number of rows visited=3000000 Scan type=heap start position: null stop position: null qualifiers: None optimizer estimated row count: 2969453.00 optimizer estimated cost: 833450.14