Search code examples
postgresqlsubqueryquery-optimization

Postgres subquery execution steps

Suppose I have a query which says

Select * from ( 
   select coalesce(mytable.created_date,mytable1.created_date) as created_date,...
   from mytable 
   left join mytable1 ON (mytable.id=mytable1.id)
   --Other Joins and tables here
) as foo
where created_date > CURRENT_DATE

Will Postgres select only the rows where created_date is > CURRENT_DATE for inner query joins where I am joining many tables?

Or will it take all rows from mytable and make joins with other tables on inner query, then check for created_date > CURRENT_DATE.

Is my previous query the same as

select coalesce(mytable.created_date,mytable1.created_date),... from mytable 
    left join mytable1 ON (mytable.id=mytable1.id)
    --Other Joins and tables here 
WHERE 
    coalesce(mytable.created_date,mytable1.created_date) > CURRENT_DATE
Solution

  • As you can see when you use EXPLAIN, the optimizer can “flatten” such subqueries, so that the execution plans for these two queries will be the same.

    In other words, the optimizer is able to push the WHERE condition into the subquery and the join, so that it can be executed first.

    Moreover, if created_date happens to be a column of mytable1, PostgreSQL will deduce that created_date can never be NULL and perform an inner join rather than an outer join.