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=# \timing
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...
