This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Recently, I have published one article on Database Design about do not use comma separated table names in queries and use explicit joins.
You must visit this:
I am observing one bad query practice. People are using multiple tables in the SELECT query by separating using commas.
SELECT * FROM a, b, c
WHERE a.id = b.id AND b.id = c.id;
When you write a query like above sample, internally Query Planner creates a virtual join between these tables and this is not a straightforward joins creation.
A Query Planner has to check all possible combinations like: first join A TO B and then join C, join A TO C and then join B, join B TO C and then join A.
PostgreSQL provides one server parameter to control the order of Joins and force join order laid out by explicit JOINs using join_collapse_limit.
You can set join_collapse_limit = 1 which force to planner join the tables in explicit order.
One thing we must sure about it, the correct explicit order of Joins otherwise it creates performance issue.
Limitation of this:
After setting this parameter, the planner has less freedom to prepare an execution plan based on join conditions.
If you are using subquery – inner join condition, it executes outer join first and then execute subquery – inner join condition which is wrong.
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.id = c.id)) ON (a.id = b.id);