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