In Postgres, is filtering two tables before a join performantly equal to joining two tables, then fi-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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.