SQLAlchemy: order_by (No) for connection subtitle queries?

Question

We are using SQLAlchemy 0.9.8 on Python 2.7.7 and Postgres 9.3.

We have a request that uses joinloads to completely populate some Recipe objects using a single request. The query creates a large SQL statement that takes 20 seconds to execute - too long. This displays the SQL statement on Pastebin .

The submitted SQL has an ORDER BY clause, which Postgres explains, is the source of 99% of the time spent on this query. It looks like this comes from a relationship in an ORM model that has an order_by clause.

However, we do not care that the results are returned for this query - we only care about the order when viewing a single object. If I delete the ORDER BY clause at the end of the expressed SQL statement, the query is completed in less than a second — perfect.

We tried using .order_by (None) in the request, but this does not seem to have any effect. ORDER BY seems to be related to connected loads, because if you change one-time loads to lazyloads, they go away. But we need variables for speed.

How can I get SQLAlchemy to omit the ORDER BY clauses?

---

FYI, here is the request:

missing_recipes = cls.query(session).filter(Recipe.id.in_(missing_recipe_ids)) if missing_recipe_ids else []

Here is an excerpt from the ORM class:

class Recipe(Base, TransactionalIdMixin, TableCacheMixin, TableCreatedModifiedMixin):
    __tablename__ = 'recipes'
      authors = relationship('RecipeAuthor', cascade=OrmCommonClass.OwnedChildCascadeOptions,
                           single_parent=True,
                           lazy='joined', order_by='RecipeAuthor.order', backref='recipe')
    scanned_photos = relationship(ScannedPhoto, backref='recipe', order_by="ScannedPhoto.position")
    utensils = relationship(CookingUtensil, secondary=lambda: recipe_cooking_utensils_table)
    utensil_labels = association_proxy('utensils', 'name')

Our query () method looks something like this (some other attached loads are omitted):

@classmethod
def query(cls, session):
    query = query.options(
        joinedload(cls.ingredients).joinedload(RecipeIngredient.ingredient),
        joinedload(cls.instructions),
        joinedload(cls.scanned_photos),
        joinedload(cls.tags),
        joinedload(cls.authors),
    )

Answer 1

[ ]

order_by (), , , , joinload(), contains_eager() (http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager).

mixedload() - , (, ORDER BY the relationship), , , - , FROM . contains_eager() . , ( ), , .