Mini-challenge M05 — TaskFlow dashboard 2.1s → 50ms qua index strategy

-- Dashboard query (tu M02.7)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = $1
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

-- Truncate va re-seed
TRUNCATE tasks RESTART IDENTITY CASCADE;

-- Generate 1M task realistic
INSERT INTO tasks (project_id, assignee_id, title, status, due_at, created_at, updated_at)
SELECT
  (random() * 4 + 1)::int,                -- 5 project (1-5)
  (random() * 9 + 1)::int,                -- 10 assignee (1-10)
  'Task ' || g,
  (ARRAY['todo', 'doing', 'done', 'archived'])[1 + (random() * 3)::int],
  CASE WHEN random() < 0.7
       THEN now() + (random() * 30 - 15) * INTERVAL '1 day'
       ELSE NULL
  END,
  now() - (random() * 180) * INTERVAL '1 day',
  now() - (random() * 30) * INTERVAL '1 day'
FROM generate_series(1, 1000000) g;

-- Update statistics
ANALYZE tasks;

SELECT COUNT(*) FROM tasks;  -- expected: 1000000

\timing on

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

 Limit  (cost=... rows=20 width=...)
   ->  Sort  (cost=...)
         Sort Key: due_at NULLS LAST, id
         ->  Seq Scan on tasks  (cost=... rows=...)
               Filter: ((assignee_id = 5) AND
                        (status = ANY ('{todo,doing}')) AND
                        (due_at >= now()) AND
                        (due_at <= now() + interval '3 days'))
               Rows Removed by Filter: ~990000
 Planning Time: 0.5 ms
 Execution Time: 2100.00 ms

CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

 Limit  (cost=...)
   ->  Sort  (cost=...)
         Sort Key: due_at NULLS LAST, id
         ->  Bitmap Heap Scan on tasks  (cost=...)
               Recheck Cond: (assignee_id = 5)
               Filter: ((status = ANY ('{todo,doing}')) AND ...)
               ->  Bitmap Index Scan on idx_tasks_assignee
                     Index Cond: (assignee_id = 5)
 Execution Time: ~250.00 ms

DROP INDEX idx_tasks_assignee;
CREATE INDEX idx_tasks_dashboard_v2 ON tasks(assignee_id, status, due_at);
ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

 Limit
   ->  Index Scan using idx_tasks_dashboard_v2 on tasks
         Index Cond: ((assignee_id = 5) AND
                      (status = ANY ('{todo,doing}')) AND
                      (due_at >= now()) AND
                      (due_at <= now() + interval '3 days'))
 Execution Time: ~5.00 ms

DROP INDEX idx_tasks_dashboard_v2;
CREATE INDEX idx_tasks_dashboard_v3
  ON tasks(assignee_id, status, due_at)
  INCLUDE (id, title);

-- Update visibility map (can thiet cho Index Only Scan)
VACUUM ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

 Limit
   ->  Index Only Scan using idx_tasks_dashboard_v3 on tasks
         Index Cond: ((assignee_id = 5) AND
                      (status = ANY ('{todo,doing}')) AND
                      (due_at >= now()) AND
                      (due_at <= now() + interval '3 days'))
         Heap Fetches: 0
 Execution Time: ~2.00 ms

DROP INDEX idx_tasks_dashboard_v3;
CREATE INDEX idx_tasks_dashboard_v4
  ON tasks(assignee_id, due_at)
  INCLUDE (id, title)
  WHERE status IN ('todo', 'doing');

VACUUM ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

 Limit
   ->  Index Only Scan using idx_tasks_dashboard_v4 on tasks
         Index Cond: ((assignee_id = 5) AND
                      (due_at >= now()) AND
                      (due_at <= now() + interval '3 days'))
         Filter: (status = ANY ('{todo,doing}'))
         Heap Fetches: 0
 Execution Time: ~1.50 ms

-- Kiem tra size
SELECT pg_size_pretty(pg_relation_size('idx_tasks_dashboard_v3'));
-- ~80 MB (full index -- index ca 1M row)

SELECT pg_size_pretty(pg_relation_size('idx_tasks_dashboard_v4'));
-- ~25 MB (partial -- chi ~500k row co status todo/doing)

-- Simulate UPDATE lon -- lam visibility map outdated
UPDATE tasks SET status = 'doing' WHERE status = 'todo' LIMIT 1000;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;
-- Index Only Scan
-- Heap Fetches: 50  -- co heap fetch (visibility map stale cho ~50 page)

-- Fix: chay VACUUM de refresh visibility map
VACUUM ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS) -- chay lai cung query
-- Heap Fetches: 0  -- visibility map fresh, skip heap hoan toan

-- ==============================================
-- Section 1: Setup 1M row seed
-- ==============================================
TRUNCATE tasks RESTART IDENTITY CASCADE;
-- ... (seed script tu Step 1)
ANALYZE tasks;

-- ==============================================
-- Section 2: Baseline EXPLAIN (no index)
-- ==============================================
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Record: Seq Scan, Execution Time ~2100ms

-- ==============================================
-- Section 3: v1 -- single column
-- ==============================================
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
ANALYZE tasks;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Record: Bitmap Heap Scan, ~250ms

-- ==============================================
-- Section 4: v2 -- composite
-- ==============================================
DROP INDEX idx_tasks_assignee;
CREATE INDEX idx_tasks_dashboard_v2 ON tasks(assignee_id, status, due_at);
ANALYZE tasks;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Record: Index Scan, ~5ms

-- ==============================================
-- Section 5: v3 -- INCLUDE covering
-- ==============================================
DROP INDEX idx_tasks_dashboard_v2;
CREATE INDEX idx_tasks_dashboard_v3
  ON tasks(assignee_id, status, due_at) INCLUDE (id, title);
VACUUM ANALYZE tasks;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Record: Index Only Scan, Heap Fetches 0, ~2ms

-- ==============================================
-- Section 6: v4 -- partial index
-- ==============================================
DROP INDEX idx_tasks_dashboard_v3;
CREATE INDEX idx_tasks_dashboard_v4
  ON tasks(assignee_id, due_at) INCLUDE (id, title)
  WHERE status IN ('todo', 'doing');
VACUUM ANALYZE tasks;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Record: Index Only Scan, ~1.5ms

-- ==============================================
-- Section 7: Index size comparison
-- ==============================================
SELECT pg_size_pretty(pg_relation_size('idx_tasks_dashboard_v4'));

-- ==============================================
-- Section 8: Cleanup
-- ==============================================
DROP INDEX IF EXISTS idx_tasks_dashboard_v4;
TRUNCATE tasks RESTART IDENTITY CASCADE;