PostgreSQL filter performance and query optimization

Question

I have some heavy queries where performance requires that filters are applied before joins. According to some SO posts

The query optimizer is often smart enough to filter early.

session.query(Table1.post_id)\
    .join(Table2, Table1.post_id == Table2.post_id)\
    .filter(and_(Table1.user_id == thing1.id, Table2.blob_id == thing2.id))

Should I expect this type of query to be performed such that filters are applied before the join?

Check [the execution plan](https://www.postgresql.org/docs/current/using-explain.html) — , Feb 07 '19 at 11:26
have you tried to debug an [actual query](https://stackoverflow.com/questions/2950385/debugging-displaying-sql-command-sent-to-the-db-by-sqlalchemy)? — Azat Ibrakov, Feb 07 '19 at 11:27
This is more about "Postgresql performance" than "SQLAlchemy filter performance", unless you're asking how to run `EXPLAIN` on SQLA queries. — Ilja Everilä, Feb 07 '19 at 11:27
The only thing we can analyze is the actual SQL generated by SQLAlchemy that gets passed to Pg to be executed. — Andy Lester, Feb 07 '19 at 14:55

score 3 · Answer 1 · edited Feb 07 '19 at 14:01

3

Yes, PostgreSQL will apply the filters first whenever it expects that to be faster, which will probably always be the case.

To be certain, get the actual SQL statement and examine the execution plan with

EXPLAIN SELECT ...;

edited Feb 07 '19 at 14:01

Nick Barnes

19,816
3
51
63

answered Feb 07 '19 at 12:53

Laurenz Albe

209,280
17
206
263

PostgreSQL filter performance and query optimization

1 Answers1