I’m trying to understand if the way I’m writing my queries in postgres is not performant at scale (because of how I’m using Views to organize DRY code).
I think it boils down to whether filtering tables before joining them is equivalent to joining the tables, then filtering.
Here’s an example: Can someone tell me if Option 1 and Option 2 are equally performant on very large tables?
Option 1
with filteredTable1 as
(select *
from table1
where table1.id = 1),
filteredtTable2 as
(select *
from table2
where table2.id = 1)
select *
from filteredTable1
inner join filteredTable2 filteredTable1.id = filteredTable2.id
Option 2
with joinedTables as
(select *
from table1
inner join table2 on table1.id = table2.id)
select *
from joinedTables
where id1 = 1
Thanks!
2
Answers
The first option consists of 3 selects and 1 join, on the other hand, the second option – 2 selects and 1 join. I think that’s the answer
In general, with the standard inner join, yes the two ways are semantically identical.
Whether the two queries will have the same performance depends on what the query planner does with them. In your particular case, I would expect them to be executed in the same way, but the only way to be certain is to use
EXPLAIN
to view the query plan.