CODEWITHSUNDEEP
postgresql
ZiiMakc
ZiiMakc

Reputation: 37066

Postgresql should left join use WHERE or ON is enough?

When you do a select subquery, should you use WHERE inside it or 's on s.id = t.id' is enough? I want to understand if subquery without where selecting all the rows and then filter them, or it's select only that match condition 'on add.id = table.id'

SELECT * FROM table 
      left join (
          select *
          from add
          /* where add.id = 1 */ - do i need this?
          group by add.id
          ) add on add.id = table.id
    WHERE table.id = 1

As i understand from EXPLAIN:

Nested Loop Left Join  (cost=2.95..13.00 rows=10 width=1026)
Join Filter: (add.id = table.id)

It loads all rows and then do a filter. Is it bad?

Upvotes: 0

Views: 44

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13139

I'm not sure if your example it too simple, but you shouldn't need a subquery at all for this one - and definitely not the group by.

Suppose you do need a subquery, then for this specific example, it leads to exactly the same query plan whether you add the where clause or not. The idea of the query planner is that it tries to find a way to make your query as fast as possible. Oftentimes this means ordering the execution of joins and where clauses in such a way, that the result set is increased sooner rather than later. I generated exactly the same query, only with reservations and customers, I hope that's okay.

EXPLAIN
SELECT *
FROM reservations
  LEFT OUTER JOIN (
    SELECT *
    FROM customers
  ) AS customers ON customers.id = reservations.customer_id
WHERE customer_id = 1;

Nested Loop Left Join  (cost=0.17..183.46 rows=92 width=483)
  Join Filter: (customers.id = reservations.customer_id)
  ->  Index Scan using index_reservations_on_customer_id on reservations  (cost=0.09..179.01 rows=92 width=255)
        Index Cond: (customer_id = 1)
  ->  Materialize  (cost=0.08..4.09 rows=1 width=228)
        ->  Index Scan using customers_pkey on customers  (cost=0.08..4.09 rows=1 width=228)
              Index Cond: (id = 1)

The deepest arrows are executed first. This means that even though I didn't have the equivalent of where add.id = 1 in my subquery, it still knew that the equality customers.id = customer_id = 1 should be true, so it decided to filter on customers.id = 1 before even attempting to join anything

Upvotes: 1

Related Questions