I´m running pole position benchmark over derby 10.2.2 server mode. In circuit Barcelona derby is extremely inefficient. Blame on lap "read" that is running too many times with a inefficient query.
Looking to the querys from "read" and "query" (A and B) i see a inner join over 5 tables. The query plan is confusing but i can see a table scan over table barcelona3. It shouldn't be necessary, why is the optimizer doing it? Also, I have indexes over columns id and barcelona2.b2 All tables have ~30500 elements. All columns are integers, but B2 can be null. Running query A and B 100 times, the execution of query B is always more efficient than A. In order ~8seg to ~19seg. It´s a big difference, i can´t explain it. Does anyone have some idea? I will now start to try rewrite querys, but i find this two questions interesting. And also find the perfect opportunity to start building the tree of query plan. PJ query A select * from barcelona0, barcelona1, barcelona2, barcelona3, barcelona4 where barcelona1.parent = barcelona0.id and barcelona2.parent = barcelona1.id and barcelona3.parent = barcelona2.id and barcelona4.parent = barcelona3.id and barcelona0.id=? query B select * from barcelona0, barcelona1, barcelona2, barcelona3, barcelona4 where barcelona1.parent = barcelona0.id and barcelona2.parent = barcelona1.id and barcelona3.parent = barcelona2.id and barcelona4.parent = barcelona3.id and barcelona2.b2=?
