[DERBY-3498] Performance sensitive to FROM order list in three way join with GROUP BY

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.2.2.0
• Component/s: SQL
• Labels: derby_triage10_5_2
• Urgency: Normal
• Bug behavior facts: Performance
• Assignee: Unassigned
• Reporter: Daniel John Debrunner
• Created: 05/Mar/08 19:40
• Updated: 16/Feb/12 22:02

Description

The Order Entry system test has this query in Checks.testCondition2():

SELECT D.D_ID, D.D_W_ID,D.D_NEXT_O_ID,
MAX(O.O_ID),MAX(N.NO_O_ID) FROM NEWORDERS N,
DISTRICT D, ORDERS O WHERE D.D_W_ID=O.O_W_ID
AND D.D_W_ID = N.NO_W_ID AND D.D_ID = O.O_D_ID
AND D.D_ID = N.NO_D_ID GROUP BY
D.D_ID,D.D_W_ID,D.D_NEXT_O_ID

Changing the FROM list to put DISTRICT first reduced the running time from ~138seconds to ~105 and a similar reduction on another machine.
(with a 1 warehouse database)

Beyond that, the query seems to take too long. 10 rows will be returned and for each district row (returned row) the MAX() values in the select list should just be a max lookup in the primary key for the ORDERS and NEWORDERS table. The primary key for these tables is W_ID, D_ID, O_ID.
Thus it should be a scan of the 10 row DISTRICT table with 20 index lookups, hard to see how that should table 100 seconds.

One can see this by using the java-system-mini-codeline-jars target.