Writing a query in several ways enables the developer to detect some issues in coding best practices, planner parameters, and performance optimization. The example of returning the guru IDs, names, and the count of success_stories can be written as follows: 

postgres=# o /dev/null
postgres=# 	iming 
Timing is on.
postgres=# SELECT id, name, (SELECT count(*) FROM success_story where guru_id=id) FROM guru;
Time: 144,929 ms
postgres=# WITH counts AS (SELECT count(*), guru_id FROM success_story group by guru_id) SELECT id, name, COALESCE(count,0) FROM guru LEFT JOIN counts on guru_id = id;
Time: 728,855 ms
postgres=# SELECT guru.id, name, COALESCE(count(*),0) FROM guru LEFT JOIN success_story on guru_id = guru.id group by guru.id, name ;
Time: 452,659 ms
postgres=# SELECT id, name, COALESCE(count,0) FROM guru LEFT JOIN ( SELECT count(*), guru_id FROM success_story group by guru_id )as counts on guru_id = id;
Time: 824,164 ms
postgres=# SELECT guru.id, name, count(*) FROM guru LEFT JOIN success_story on guru_id = guru.id group by guru.id, name ;
Time: 454,865 ms
postgres=# SELECT id, name, count FROM guru , LATERAL (SELECT count(*) FROM success_story WHERE guru_id=id ) as foo(count);
Time: 137,902 ms
postgres=# SELECT id, name, count FROM guru LEFT JOIN LATERAL (SELECT count(*) FROM success_story WHERE guru_id=id ) AS foo ON true;
Time: 156,294 ms

In the previous example, using LATERAL gave the best performance results, while CTE and joining a subquery gave the worst performance. Note that the result shown here might deviate on other machines due to hardware specifications such as hard disk specifications. LATERAL is very powerful at optimizing queries that require limiting the results, such as give me the top ten stories for each journalist. Also, LATERAL is very useful when joining table functions.