SQLAlchemy - DISTINCT, LIMIT, or OFFSET Causing SubqueriesThis section at the bottom of The Zen of Eager loading is really important:
When using joined eager loading, if the query contains a modifier that impacts the rows returned externally to the joins, such as when using DISTINCT, LIMIT, OFFSET or equivalent, the completed statement is first wrapped inside a subquery, and the joins used specifically for joined eager loading are applied to the subquery. SQLAlchemy’s joined eager loading goes the extra mile, and then ten miles further, to absolutely ensure that it does not affect the end result of the query, only the way collections and related objects are loaded, no matter what the format of the query is.I made an example to illustrate this here: https://gist.github.com/pawl/cb57e0ddbdd0b2e64b75e94116873367
On MySQL this can be responsible for some really poor query performance, because it can cause it to use temporary tables and filesort.
The best way I've found to prevent the subqueries is by first querying for the ids only, then running another query that includes all relations. For example:
ids = session.query(Product.id).limit(20)