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?

This is more about "Postgresql performance" than "SQLAlchemy filter performance", unless you're asking how to run EXPLAIN on SQLA queries. — Ilja Everilä
– Ilja Everilä, Commented Feb 7, 2019 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
– Andy Lester, Commented Feb 7, 2019 at 14:55

Nick Barnes · Accepted Answer · 2019-02-07 14:01:08Z

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 7, 2019 at 14:01

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

answered Feb 7, 2019 at 12:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL filter performance and query optimization

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related