Learning PostgreSQL 11 - Third Edition

Query rewriting

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/nullpostgres=# \timing Timing is on.postgres=# SELECT id, name, (SELECT count(*) FROM success_story where guru_id=id) FROM guru;Time: 144,929 mspostgres=# 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 mspostgres=# SELECT guru.id, name, COALESCE(count(*),0) FROM guru LEFT JOIN success_story on guru_id = guru.id group by guru.id, ...