I am doing a query like this:

   table1 join table2 on table1.fieldjoin = table2.fieldjoin
   table1.field = 'y'

I would ask is this query equivalent in terms of performance to this:

  (select * from table1 where field = 'y') as t1 join table2 
  on table1.fieldjoin = table2.fieldjoin

That is by explicting doing a where inside the joined table

  • 4
    Check the execution plan but I would be very surprised if there is a difference. – a_horse_with_no_name Jun 24 '17 at 6:44
  • @a_horse_with_no_name you can make an answer i will select that – giò Sep 16 '17 at 9:57

A SQL query is a definition of what data to return. There is a component of the database called the optimizer which determines how to get that data - which indexes to use, if any, in which sequence, which join algorithm to use etc. The output of that component is a query plan, the equivalent of a procedural program in the DBMS's internal execution language. The optimizer is free to choose any plan it likes as long as it is guaranteed to produce the output required by the SQL.

Sometimes queries which are logically equivalent but use different syntax will produce quite different query plans. Sometimes the optimizer recognizes the equivalence and produces the same plan for both statements. It can be difficult to predict which without good understanding of the working of the optimizer.

However, the optimizer does make available to us these plans. In PostgreSQL they can be retrieved using EXPLAIN. If the plans for the two queries are the same there will be no performance difference between them. Different plans will have different performance, though it may be slight.

There are a great many factors which go into constructing a plan other than the SQL. So a change in table cardinality (e.g. as time passes, or between production and development environments), or a different version of Postgres may produce a different plans for the same SQL statement.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.