PostgreSQL - INNER JOIN vs WHERE, which one will perform better?

Question

I've just made a quick search through the web but couldn't find exactly what I'm looking for.

How much efficient an INNER JOIN is comparing to a regular WHERE statement?

I have a couple of queries in PostgreSQL that need to use some tables (say four or five) "linked" together by key/foreign key conditions. To implement those queries I'm using the WHERE clause to join all the required tables.

I wonder if any performance gains will bd achieved if I re-write those queries with the INNER JOIN clause (instead of a WHERE clause).

Anthropocentric

Just compare the query plans: they are exactly the same. – Ovi

Answer 1

I think what you mean to say is difference between the below queries

select a.col1,b.col2 from table1 a, table2 b where a.id = b.id;

Against

select a.col1,b.col2 from table1 a
join table2 b on a.id = b.id;

To my knowledge, both are doing a INNER JOIN; it's just that the above one is a old style, hard to read, error prone implicit join syntax and the below one is recommended explicit join syntax.

So, I don't see any performance gain/loss here; since either way they are performing the same thing.

Natatorium

Yes, exactly. This example explains well the point of my question. I just want to make sure that the optimizer will convert those both query implementations to the same query plan, which will make both implementations perform the same. – Anthropocentric

Yes since inherently they both are same. – Natatorium

You can use explain analyze to get the execution plan with both queries (the one joining tables in the where and the other one joining tables with join syntax) and compare both plans: they will be the same, with same operations and records filtered. – Tensor