explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PLBE

Settings
# exclusive inclusive rows x rows loops node
1. 910.462 8,608.329 ↑ 1.2 23,040 1

GroupAggregate (cost=7,015,371.44..7,032,970.63 rows=28,475 width=176) (actual time=7,630.647..8,608.329 rows=23,040 loops=1)

  • Group Key: clps.user_id, clps.learning_path_id
2. 230.109 7,697.867 ↓ 1.0 195,022 1

Sort (cost=7,015,371.44..7,015,858.01 rows=194,625 width=69) (actual time=7,630.519..7,697.867 rows=195,022 loops=1)

  • Sort Key: clps.user_id, clps.learning_path_id
  • Sort Method: external merge Disk: 14,224kB
3. 25.456 7,467.758 ↓ 1.0 195,022 1

Nested Loop Left Join (cost=155.02..6,990,289.81 rows=194,625 width=69) (actual time=1.036..7,467.758 rows=195,022 loops=1)

4. 30.445 4,321.950 ↓ 1.0 195,022 1

Nested Loop Left Join (cost=145.28..4,309,233.12 rows=194,625 width=65) (actual time=1.026..4,321.950 rows=195,022 loops=1)

5. 112.407 976.131 ↓ 1.0 195,022 1

Nested Loop Left Join (cost=135.54..1,628,663.00 rows=194,625 width=69) (actual time=1.013..976.131 rows=195,022 loops=1)

6. 74.896 83.636 ↓ 1.0 195,022 1

Hash Join (cost=127.22..4,030.81 rows=194,625 width=68) (actual time=0.998..83.636 rows=195,022 loops=1)

  • Hash Cond: (clps.learning_path_id = clpp.learning_path_id)
7. 7.764 7.764 ↑ 1.0 48,678 1

Seq Scan on claire_learning_path_subscription clps (cost=0.00..983.78 rows=48,678 width=36) (actual time=0.007..7.764 rows=48,678 loops=1)

8. 0.170 0.976 ↑ 1.0 1,024 1

Hash (cost=114.42..114.42 rows=1,024 width=32) (actual time=0.976..0.976 rows=1,024 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 72kB
9. 0.392 0.806 ↑ 1.0 1,024 1

Hash Join (cost=84.10..114.42 rows=1,024 width=32) (actual time=0.315..0.806 rows=1,024 loops=1)

  • Hash Cond: (clpp.project_id = cp.id)
10. 0.117 0.117 ↑ 1.0 1,024 1

Seq Scan on claire_learning_path_project clpp (cost=0.00..16.24 rows=1,024 width=8) (actual time=0.005..0.117 rows=1,024 loops=1)

11. 0.119 0.297 ↑ 1.0 760 1

Hash (cost=74.60..74.60 rows=760 width=28) (actual time=0.297..0.297 rows=760 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 52kB
12. 0.178 0.178 ↑ 1.0 760 1

Seq Scan on claire_project cp (cost=0.00..74.60 rows=760 width=28) (actual time=0.003..0.178 rows=760 loops=1)

13. 0.000 780.088 ↑ 1.0 1 195,022

Limit (cost=8.32..8.33 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=195,022)

14. 390.044 780.088 ↑ 1.0 1 195,022

Sort (cost=8.32..8.33 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=195,022)

  • Sort Key: _cps.started_at DESC
  • Sort Method: quicksort Memory: 25kB
15. 390.044 390.044 ↑ 1.0 1 195,022

Index Scan using idx_claire_project_session_project_id_user_id on claire_project_session _cps (cost=0.29..8.31 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=195,022)

  • Index Cond: ((project_id = clpp.project_id) AND (user_id = clps.user_id))
16. 390.044 3,315.374 ↓ 0.0 0 195,022

Limit (cost=9.74..13.75 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=195,022)

17. 0.000 2,925.330 ↓ 0.0 0 195,022

Bitmap Heap Scan on claire_project_completion _cpc (cost=9.74..13.75 rows=1 width=20) (actual time=0.015..0.015 rows=0 loops=195,022)

  • Recheck Cond: ((user_id = clps.user_id) AND (project_id = clpp.project_id))
  • Heap Blocks: exact=15,809
18. 128.758 2,925.330 ↓ 0.0 0 195,022

BitmapAnd (cost=9.74..9.74 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=195,022)

19. 390.044 390.044 ↑ 2.7 3 195,022

Bitmap Index Scan on idx_56ff5e38a76ed395 (cost=0.00..4.35 rows=8 width=0) (actual time=0.002..0.002 rows=3 loops=195,022)

  • Index Cond: (user_id = clps.user_id)
20. 2,406.528 2,406.528 ↓ 3.2 358 75,204

Bitmap Index Scan on idx_56ff5e38166d1f9c (cost=0.00..5.14 rows=113 width=0) (actual time=0.032..0.032 rows=358 loops=75,204)

  • Index Cond: (project_id = clpp.project_id)
21. 390.044 3,120.352 ↓ 0.0 0 195,022

Limit (cost=9.74..13.76 rows=1 width=12) (actual time=0.016..0.016 rows=0 loops=195,022)

22. 0.000 2,730.308 ↓ 0.0 0 195,022

Bitmap Heap Scan on claire_project_completion _cpc_1 (cost=9.74..13.76 rows=1 width=12) (actual time=0.014..0.014 rows=0 loops=195,022)

  • Recheck Cond: ((user_id = clps.user_id) AND (project_id = clpp.project_id))
  • Filter: (effective_at = clps.followed_at)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=58,999
23. 279.166 2,730.308 ↓ 0.0 0 195,022

BitmapAnd (cost=9.74..9.74 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=195,022)

24. 195.022 195.022 ↑ 2.7 3 195,022

Bitmap Index Scan on idx_56ff5e38a76ed395 (cost=0.00..4.35 rows=8 width=0) (actual time=0.001..0.001 rows=3 loops=195,022)

  • Index Cond: (user_id = clps.user_id)
25. 2,256.120 2,256.120 ↓ 3.2 358 75,204

Bitmap Index Scan on idx_56ff5e38166d1f9c (cost=0.00..5.14 rows=113 width=0) (actual time=0.030..0.030 rows=358 loops=75,204)

  • Index Cond: (project_id = clpp.project_id)
Planning time : 0.738 ms
Execution time : 8,613.727 ms