Case 4: Getting Counts Along with a List (The N+1 Query Problem)

The Story

• Table 1: authors, with 10,000 rows. Columns: id, name.
• Table 2: novels, with 1,000,000 rows. Columns: id, author_id, title.
• Current State: An index exists on novels(author_id).
• The Problem: We want to display a list of all authors and the number of novels each author has written.

The Problematic SQL Query

A new developer might write this query, which looks very intuitive: "For each author, count their novels."

EXPLAIN ANALYZE
SELECT
    a.id,
    a.name,
    (SELECT COUNT(*) FROM novels n WHERE n.author_id = a.id) AS novel_count
FROM
    authors a;

The Query Plan (BEFORE a fix)

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on authors a  (cost=0.00..345000.00 rows=10000 width=40) (actual time=0.025..1850.750 rows=10000 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=34.45..34.46 rows=1 width=8) (actual time=0.182..0.182 rows=1 loops=10000)
           ->  Index Only Scan using idx_novels_author_id on novels n  (cost=0.43..34.20 rows=100 width=0) (actual time=0.015..0.175 rows=100 loops=10000)
                 Index Cond: (author_id = a.id)
 Planning Time: 0.180 ms
 Execution Time: 1855.432 ms

Why is this a problem?

1. Look at this line: Notice the loops=10000 in the last two lines.

2. The "N+1 Query" Problem:

   • The "1" Query: Seq Scan on authors a - The system scans the authors table once to get all 10,000 authors.
   • The "N" Queries: For each of those 10,000 authors, the system has to run a separate subquery (SubPlan 1) to count that author's novels. The loops=10000 is the proof.
   • In total, the database had to execute 1 + 10,000 = 10,001 queries!

3. The Warning Signs:

   • Execution Time: **1855.432 ms**: Taking almost 2 seconds for a simple list is too slow.
   • Even though each subquery (Index Only Scan) is very fast (only 0.182 ms), repeating it 10,000 times adds up to a huge number.

4. The Root Cause:

   • A Correlated Subquery forces the database to repeat a task over and over. This is a classic anti-pattern in SQL.

The Solution

• Action: Rewrite the query using a JOIN and GROUP BY. This allows the database to process all the data in a single pass.
• Command:

  EXPLAIN ANALYZE
  SELECT
      a.id,
      a.name,
      COUNT(n.id) AS novel_count
  FROM
      authors a
  LEFT JOIN novels n ON a.id = n.author_id
  GROUP BY
      a.id, a.name;

  (We use LEFT JOIN to ensure that authors who haven't written any novels still appear in the list with novel_count = 0)

The Query Plan (AFTER the fix)

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=21850.00..22100.00 rows=10000 width=40) (actual time=215.450..218.123 rows=10000 loops=1)
   Group Key: a.id, a.name
   ->  Hash Join  (cost=315.00..17100.00 rows=1000000 width=36) (actual time=2.875..145.987 rows=1000000 loops=1)
         Hash Cond: (n.author_id = a.id)
         ->  Seq Scan on novels n  (cost=0.00..14500.00 rows=1000000 width=8) (actual time=0.010..50.123 rows=1000000 loops=1)
         ->  Hash  (cost=190.00..190.00 rows=10000 width=36) (actual time=2.850..2.850 rows=10000 loops=1)
               ->  Seq Scan on authors a  (cost=0.00..190.00 rows=10000 width=36) (actual time=0.005..1.250 rows=10000 loops=1)
 Planning Time: 0.350 ms
 Execution Time: 219.850 ms

Analyzing the Result

1. The Big Change: The entire plan has changed. There is no more loops=10000.

   • The database scans both the authors and novels tables only once.
   • It performs a Hash Join to connect them.
   • Finally, it uses a HashAggregate (GROUP BY) to count the novels for each author.
   • The entire process is a single, non-repetitive workflow.

2. Performance Comparison:

   • Execution Time: Dropped from 1855.432 ms down to just 219.850 ms. That's about 8.5 times faster!

Conclusion

Always be wary of correlated subqueries, especially in the SELECT clause. Most of them can and should be rewritten using a JOIN. The JOIN + GROUP BY approach allows the database to optimize the execution across the entire dataset much more effectively.