SQLAlchemy+SQLite Left Join Performance Issue

Question

I have two identical queries save for the position of the left join in the from clause. One runs very slow, the other runs very fast (abbreviated for clarity):

--SLOW
SELECT DISTINCT b.id
FROM a LEFT OUTER JOIN b ON a.id = b.id
       JOIN c ON a.id = c.a_id 
       JOIN d ON c.id = d.c_id 
WHERE d.value = 9;

--FAST
SELECT DISTINCT b.id
FROM a JOIN c ON a.id = c.a_id 
       JOIN d ON c.id = d.c_id 
       LEFT OUTER JOIN b ON a.id = b.id
WHERE d.value = 9;

My problem is that, using SQLAlchemy, I seem to only be able to create the slow version of the query. Specifically, I am using table inheritance and am trying to run the following code:

return session.query(A).\
       with_polymorphic(B).\
       join(C).\
       join(D).\
       filter(D.value_id == 9).\
       distinct()

In other words, I don't have control over where the LEFT JOIN is being created.

How do I make SQLite and/or SQLAlchemy smarter about this?

Answer 1

I am not aware of the ways to force SA into changing the query. But I would to identify why there is a difference in execution plan for the query. I would assume that if you have indices on the join columns, the order of the JOINs in the query should not matter.

You can use the EXPLAIN statement to check the execution plan. Although you need to get familiar with The Virtual Database Engine of SQLite. Still it might be possible you spot the difference between two execution plans immediatelly and will be able to improve the database performance instead of tricking the SA.
Try to also remove C or D from the query for even easier comparison of execution plans.