JOIN performance depends on presentation-order of table alias

[GoogleCodeExporter]

In h2-1.3.152 (and earlier):

What steps will reproduce the problem?

1. With 500,000 row table (provided via email) this query take > 60 seconds to 
run:
SELECT count(*)
FROM om AS a INNER JOIN om AS b ON a.objId=b.objId AND a.omattribid=28
LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE b.omattribid=25
AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff'; 

2. This query (reversed table order) runs in < 15ms:
SELECT count(*)
FROM om AS b INNER JOIN om AS a ON a.objId=b.objId AND a.omattribid=28
LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE b.omattribid=25
AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff'; 

More info is here:
http://groups.google.com/group/h2-database/browse_thread/thread/30164b51d0d28551
?pli=1

Original issue reported on code.google.com by KENSYS...@gmail.com on 15 Mar 2011 at 6:08

[GoogleCodeExporter]

Thanks! This is already in the roadmap: "Support optimizing queries with both 
inner and outer joins", and there is already a test case, but it's probably a 
good idea to create an issue report for this. Anyway, I will change the status 
to 'in roadmap' because prioritization happens there. I will also assign the 
issue number in the roadmap or course.

Original comment by thomas.t...@gmail.com on 26 Mar 2011 at 10:55

• Changed state: InRoadmap