Explain analyze before and after adding an index

explain-analyze-subjects.md

No index:

CTE Scan on flat_members  (cost=10145.57..10147.59 rows=101 width=8) (actual time=3.321..26.953 rows=3 loops=1)
  CTE flat_members
    ->  Recursive Union  (cost=0.00..10145.57 rows=101 width=8) (actual time=3.319..26.947 rows=3 loops=1)
          ->  Seq Scan on subject_group_members gm  (cost=0.00..866.77 rows=1 width=8) (actual time=3.315..4.825 rows=2 loops=1)
                Filter: (subject_id = 30459)
                Rows Removed by Filter: 48380
          ->  Hash Join  (cost=0.33..927.68 rows=10 width=8) (actual time=10.937..11.043 rows=0 loops=2)
                Hash Cond: (s.subject_id = f.subject_group_id)
                ->  Seq Scan on subject_group_members s  (cost=0.00..745.82 rows=48382 width=16) (actual time=0.010..5.079 rows=48382 loops=2)
                ->  Hash  (cost=0.20..0.20 rows=10 width=8) (actual time=0.004..0.004 rows=2 loops=2)
                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                      ->  WorkTable Scan on flat_members f  (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.001 rows=2 loops=2)
Planning Time: 0.244 ms
Execution Time: 27.043 ms

Multi-column index:

CREATE INDEX subject_group_members_subject_id_subject_group_id
    ON subject_group_members (subject_id, subject_group_id);

CTE Scan on flat_members  (cost=440.08..442.10 rows=101 width=8) (actual time=0.022..0.034 rows=3 loops=1)
  CTE flat_members
    ->  Recursive Union  (cost=0.29..440.08 rows=101 width=8) (actual time=0.020..0.032 rows=3 loops=1)
          ->  Index Only Scan using subject_group_members_subject_id_subject_group_id on subject_group_members gm  (cost=0.29..4.31 rows=1 width=8) (actual time=0.019..0.020 rows=2 loops=1)
                Index Cond: (subject_id = 30459)
                Heap Fetches: 0
          ->  Nested Loop  (cost=0.29..43.38 rows=10 width=8) (actual time=0.005..0.005 rows=0 loops=2)
                ->  WorkTable Scan on flat_members f  (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.000 rows=2 loops=2)
                ->  Index Only Scan using subject_group_members_subject_id_subject_group_id on subject_group_members s  (cost=0.29..4.31 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=3)
                      Index Cond: (subject_id = f.subject_group_id)
                      Heap Fetches: 0
Planning Time: 0.197 ms
Execution Time: 0.058 ms

Multi-column index AND individual indexes:

CTE Scan on flat_members  (cost=440.08..442.10 rows=101 width=8) (actual time=0.060..0.075 rows=3 loops=1)
  CTE flat_members
    ->  Recursive Union  (cost=0.29..440.08 rows=101 width=8) (actual time=0.059..0.073 rows=3 loops=1)
          ->  Index Only Scan using subject_group_members_subject_id_subject_group_id on subject_group_members gm  (cost=0.29..4.31 rows=1 width=8) (actual time=0.058..0.058 rows=2 loops=1)
                Index Cond: (subject_id = 30459)
                Heap Fetches: 0
          ->  Nested Loop  (cost=0.29..43.38 rows=10 width=8) (actual time=0.006..0.006 rows=0 loops=2)
                ->  WorkTable Scan on flat_members f  (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.000 rows=2 loops=2)
                ->  Index Only Scan using subject_group_members_subject_id_subject_group_id on subject_group_members s  (cost=0.29..4.31 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=3)
                      Index Cond: (subject_id = f.subject_group_id)
                      Heap Fetches: 0
Planning Time: 0.179 ms
Execution Time: 0.094 ms

Individual indexes only:

CTE Scan on flat_members  (cost=844.08..846.10 rows=101 width=8) (actual time=0.179..0.204 rows=3 loops=1)
  CTE flat_members
    ->  Recursive Union  (cost=0.29..844.08 rows=101 width=8) (actual time=0.177..0.201 rows=3 loops=1)
          ->  Index Scan using subject_group_members_subject_id on subject_group_members gm  (cost=0.29..8.31 rows=1 width=8) (actual time=0.174..0.178 rows=2 loops=1)
                Index Cond: (subject_id = 30459)
          ->  Nested Loop  (cost=0.29..83.38 rows=10 width=8) (actual time=0.008..0.009 rows=0 loops=2)
                ->  WorkTable Scan on flat_members f  (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.001 rows=2 loops=2)
                ->  Index Scan using subject_group_members_subject_id on subject_group_members s  (cost=0.29..8.31 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=3)
                      Index Cond: (subject_id = f.subject_group_id)
Planning Time: 0.299 ms
Execution Time: 0.239 ms