In SQLAlchemy, is a filter applied before or after a join?

Question

Using SQLAlchemy, I'm performing queries like the one below:

import models as m
import sqlalchemy as sa

s = session_maker()
q = s.query(m.ShareCount, m.Article)
.join(m.Article)
.filter(sa.and_(start < m.ShareCount.tstamp, m.ShareCount.tstamp < end))

Is the filter applied before or after the join is performed?

Answer 1

Is the filter applied before or after the join is performed?

It does not really matter if the filter is applied before a join. SQLAlchemy will probably put the join before the filter, because that's how SQL typically looks like.

However the query optimization engines in a database are responsible for optimization. On PostgreSQL for example you can take your query and put an explain analyze before it to understand better what's happening.

In general it's quite hard to predict how a query is going to behave in different database systems. PostgreSQL and MySQL have different optimization techniques, so the query plans will look different as well.

My suggestion is: Don't optimize. Databases are extremely cool technology that mostly do the right thing. If something is slow, it's most likely because you forgot to add an index. If you are working with 100 million+ rows you might start to hit limitations where you might start worrying about how your queries execute. However I doubt that you are at that point.