N+1 Query Prevention: From Recognition to Resolution

-- First, let's set up our example database
CREATE TABLE artists (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50)
);

CREATE TABLE albums (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    artist_id INTEGER REFERENCES artists(id),
    release_year INTEGER
);

CREATE TABLE songs (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    album_id INTEGER REFERENCES albums(id),
    duration_seconds INTEGER
);

-- Now let's look at some query patterns:

-- Pattern 1: Classic N+1 (Problematic)
-- First query:
SELECT * FROM artists WHERE country = 'USA';

-- Then for each artist:
SELECT * FROM albums WHERE artist_id = ?;  -- Repeated N times!

-- Pattern 2: Not N+1 (Fine)
-- Two independent queries:
SELECT * FROM artists WHERE country = 'USA';
SELECT COUNT(*) as total_albums FROM albums;  -- Fixed number of queries

/* Key Question: Does the number of follow-up queries depend on
   the results of the first query? If yes, it's N+1 */

-- Problem: Get all artists and their albums
-- N+1 Version (Bad):
SELECT * FROM artists;  -- First query
-- Then for each artist:
SELECT * FROM albums WHERE artist_id = ?;  -- N queries

-- Solution: Single JOIN query (Good)
SELECT 
    a.id as artist_id,
    a.name as artist_name,
    a.country,
    json_agg(
        json_build_object(
            'album_id', al.id,
            'title', al.title,
            'release_year', al.release_year
        )
    ) as albums
FROM artists a
LEFT JOIN albums al ON a.id = al.artist_id
GROUP BY a.id, a.name, a.country;

/* This solution:
   1. Makes a single database call
   2. Uses JSON aggregation for structured results
   3. Maintains data relationships
   4. Scales well with data size */

-- Problem: Get artists and their top songs
-- N+1 Version (Bad):
SELECT * FROM artists;  -- First query
-- Then for each artist:
SELECT s.* 
FROM songs s
JOIN albums al ON s.album_id = al.id
WHERE al.artist_id = ?
ORDER BY s.play_count DESC
LIMIT 5;  -- N queries

-- Solution: Two Efficient Queries (Good)
-- First, get all artists
SELECT * FROM artists;

-- Then, get all top songs in one query
SELECT 
    al.artist_id,
    array_agg(
        json_build_object(
            'song_title', s.title,
            'album_title', al.title,
            'play_count', s.play_count
        )
    ) as top_songs
FROM songs s
JOIN albums al ON s.album_id = al.id
WHERE al.artist_id IN (SELECT id FROM artists)
GROUP BY al.artist_id;

/* Benefits of this approach:
   1. Fixed number of queries (always 2)
   2. Each query is optimized
   3. Maintains readable code structure
   4. Easy to modify or extend */

-- Strategy 1: Using Common Table Expressions (CTEs)
WITH artist_stats AS (
    SELECT 
        artist_id,
        COUNT(DISTINCT al.id) as album_count,
        COUNT(DISTINCT s.id) as song_count
    FROM albums al
    LEFT JOIN songs s ON al.id = s.album_id
    GROUP BY artist_id
)
SELECT 
    a.*,
    stats.album_count,
    stats.song_count
FROM artists a
JOIN artist_stats stats ON a.id = stats.artist_id;

-- Strategy 2: Using Materialized Views for Complex Data
CREATE MATERIALIZED VIEW artist_summary AS
SELECT 
    a.id as artist_id,
    a.name as artist_name,
    COUNT(DISTINCT al.id) as album_count,
    COUNT(DISTINCT s.id) as song_count,
    array_agg(DISTINCT al.title) as album_titles
FROM artists a
LEFT JOIN albums al ON a.id = al.artist_id
LEFT JOIN songs s ON al.id = s.album_id
GROUP BY a.id, a.name;

/* These approaches help when:
   1. Data relationships are complex
   2. Performance is critical
   3. Data updates are less frequent
   4. Query patterns are predictable */

-- Testing N+1 vs Optimized Queries
EXPLAIN ANALYZE
SELECT * FROM artists;  -- Initial N+1 query

EXPLAIN ANALYZE
SELECT * FROM albums WHERE artist_id = 1;  -- Repeated query

-- Compare with optimized version:
EXPLAIN ANALYZE
SELECT 
    a.*,
    json_agg(al.*) as albums
FROM artists a
LEFT JOIN albums al ON a.id = al.artist_id
GROUP BY a.id;

/* Key metrics to compare:
   1. Total execution time
   2. Number of rows scanned
   3. Memory usage
   4. Index utilization */

-- Pattern 1: Nested Relationships
-- Problem: Artists -> Albums -> Songs
-- N+1 Version (Very Bad):
SELECT * FROM artists;  -- 1 query
-- For each artist:
SELECT * FROM albums WHERE artist_id = ?;  -- N queries
-- For each album:
SELECT * FROM songs WHERE album_id = ?;  -- M more queries!

-- Solution: Single Efficient Query
SELECT 
    a.name as artist_name,
    jsonb_agg(
        jsonb_build_object(
            'album_title', al.title,
            'songs', (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'song_title', s.title,
                        'duration', s.duration_seconds
                    )
                )
                FROM songs s
                WHERE s.album_id = al.id
            )
        )
    ) as albums_and_songs
FROM artists a
LEFT JOIN albums al ON a.id = al.artist_id
GROUP BY a.id, a.name;

/* This solution:
   1. Handles nested relationships elegantly
   2. Makes a single database call
   3. Returns structured JSON data
   4. Maintains data hierarchy */

-- Exercise 1: Identify the N+1 Problem
-- Review this code and explain why it's an N+1 query:
SELECT * FROM playlists;
-- For each playlist:
SELECT * FROM playlist_songs WHERE playlist_id = ?;

-- Exercise 2: Fix a Complex N+1 Query
-- Current code (needs optimization):
SELECT * FROM users;
-- For each user:
SELECT * FROM orders WHERE user_id = ?;
-- For each order:
SELECT * FROM order_items WHERE order_id = ?;

-- Exercise 3: Performance Comparison
-- Write queries to compare performance between:
-- 1. N+1 approach
-- 2. Single JOIN query
-- 3. Two-query approach
-- Use EXPLAIN ANALYZE to measure differences