explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J3nn

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 44,944.790 ↑ 1.0 100 1

Limit (cost=2,927,813.33..2,927,817.08 rows=100 width=167) (actual time=44,944.567..44,944.790 rows=100 loops=1)

2. 0.214 44,944.787 ↑ 48,051.5 100 1

GroupAggregate (cost=2,927,813.33..3,108,006.50 rows=4,805,151 width=167) (actual time=44,944.566..44,944.787 rows=100 loops=1)

  • Group Key: tasks.id, tasks.taskapp_id, taskapps.title, units_status_new.new, units_status_done.done, units_status_deleted.deleted
3. 34,710.866 44,944.573 ↑ 34,322.5 140 1

Sort (cost=2,927,813.33..2,939,826.21 rows=4,805,151 width=167) (actual time=44,944.535..44,944.573 rows=140 loops=1)

  • Sort Key: tasks.id, tasks.taskapp_id, taskapps.title, units_status_new.new, units_status_done.done, units_status_deleted.deleted
  • Sort Method: external merge Disk: 2835920kB
4. 2,285.016 10,233.707 ↓ 4.0 19,444,699 1

Hash Left Join (cost=1,185,845.28..2,000,360.56 rows=4,805,151 width=167) (actual time=2,697.104..10,233.707 rows=19,444,699 loops=1)

  • Hash Cond: (tasks.id = units_status_deleted.task_id)
5. 3,978.436 7,948.244 ↓ 4.0 19,444,699 1

Hash Right Join (cost=1,185,839.56..1,982,335.05 rows=4,805,151 width=159) (actual time=2,696.643..7,948.244 rows=19,444,699 loops=1)

  • Hash Cond: (units_new.task_id = tasks.id)
6. 1,274.654 1,274.654 ↑ 1.0 3,023,696 1

Index Only Scan using units_new_idx_status_task on units_new (cost=0.56..732,851.15 rows=3,118,695 width=4) (actual time=0.784..1,274.654 rows=3,023,696 loops=1)

  • Heap Fetches: 1370551
7. 112.253 2,695.154 ↓ 2.5 394,755 1

Hash (cost=1,183,902.86..1,183,902.86 rows=154,892 width=159) (actual time=2,695.154..2,695.154 rows=394,755 loops=1)

  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 69157kB
8. 159.673 2,582.901 ↓ 2.5 394,755 1

Merge Left Join (cost=1,159,616.21..1,183,902.86 rows=154,892 width=159) (actual time=2,256.213..2,582.901 rows=394,755 loops=1)

  • Merge Cond: (tasks.id = task_competence.task_id)
9. 57.058 2,423.228 ↓ 1.5 135,272 1

Merge Left Join (cost=1,159,615.61..1,174,742.94 rows=88,949 width=155) (actual time=2,256.201..2,423.228 rows=135,272 loops=1)

  • Merge Cond: (tasks.id = tasks_squads.task_id)
10. 12.919 2,366.170 ↓ 1.0 92,195 1

Merge Left Join (cost=1,159,615.32..1,169,393.38 rows=88,949 width=151) (actual time=2,256.190..2,366.170 rows=92,195 loops=1)

  • Merge Cond: (tasks.id = units_status_done.task_id)
11. 12.228 1,492.677 ↓ 1.0 92,195 1

Merge Left Join (cost=685,352.75..694,829.10 rows=88,949 width=143) (actual time=1,397.982..1,492.677 rows=92,195 loops=1)

  • Merge Cond: (tasks.id = units_status_new.task_id)
12. 25.859 80.313 ↓ 1.0 92,195 1

Nested Loop (cost=0.29..9,080.87 rows=88,949 width=135) (actual time=0.024..80.313 rows=92,195 loops=1)

13. 54.454 54.454 ↓ 1.0 92,195 1

Index Scan using tasks_pkey on tasks (cost=0.29..7,967.92 rows=88,949 width=103) (actual time=0.009..54.454 rows=92,195 loops=1)

  • Filter: (taskapp_id = 1)
  • Rows Removed by Filter: 16684
14. 0.000 0.000 ↑ 1.0 1 92,195

Materialize (cost=0.00..1.09 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=92,195)

15. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on taskapps (cost=0.00..1.09 rows=1 width=36) (actual time=0.010..0.013 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 7
16. 5.673 1,400.136 ↓ 1.1 14,209 1

Sort (cost=685,352.45..685,383.76 rows=12,522 width=12) (actual time=1,397.956..1,400.136 rows=14,209 loops=1)

  • Sort Key: units_status_new.task_id
  • Sort Method: quicksort Memory: 1051kB
  • -> Subquery Scan on units_status_new (cost=684249.76..684500.20 rows=12522 width=12) (actual time=1392.036..1395.319 r
17. 411.218 1,394.463 ↓ 1.1 14,209 1

HashAggregate (cost=684,249.76..684,374.98 rows=12,522 width=16) (actual time=1,392.036..1,394.463 rows=14,209 loops=1)

  • Group Key: units_new_1.task_id, units_new_1.status
18. 983.245 983.245 ↑ 1.1 2,021,755 1

Index Only Scan using units_new_idx_status_task on units_new units_new_1 (cost=0.56..668,276.58 rows=2,129,757 width=16) (actual time=0.044..983.245 rows=2,021,755 loops=1)

  • Index Cond: (status = 'new'::text)
  • Heap Fetches: 721430
19. 5.076 860.574 ↓ 2.5 14,517 1

Sort (cost=474,262.57..474,276.89 rows=5,729 width=12) (actual time=858.205..860.574 rows=14,517 loops=1)

  • Sort Key: units_status_done.task_id
  • Sort Method: quicksort Memory: 1065kB
20. 0.857 855.498 ↓ 2.5 14,517 1

Subquery Scan on units_status_done (cost=473,790.38..473,904.96 rows=5,729 width=12) (actual time=852.010..855.498 rows=14,517 loops=1)

21. 200.484 854.641 ↓ 2.5 14,517 1

HashAggregate (cost=473,790.38..473,847.67 rows=5,729 width=16) (actual time=852.009..854.641 rows=14,517 loops=1)

  • Group Key: units_new_2.task_id, units_new_2.status
22. 654.157 654.157 ↓ 1.0 985,844 1

Index Only Scan using units_new_idx_status_task on units_new units_new_2 (cost=0.56..466,483.28 rows=974,280 width=16) (actual time=0.024..654.157 rows=985,844 loops=1)

  • Index Cond: (status = 'done'::text)
23. 0.000 0.447 ↓ 0.0 0 1

Hash (cost=5.70..5.70 rows=1 width=12) (actual time=0.447..0.447 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
24. 0.001 0.447 ↓ 0.0 0 1

Subquery Scan on units_status_deleted (cost=5.68..5.70 rows=1 width=12) (actual time=0.447..0.447 rows=0 loops=1)

25. 0.001 0.446 ↓ 0.0 0 1

HashAggregate (cost=5.68..5.69 rows=1 width=16) (actual time=0.446..0.446 rows=0 loops=1)

  • Group Key: units_new_3.task_id, units_new_3.status
26. 0.445 0.445 ↓ 0.0 0 1

Index Scan using units_new_idx_status on units_new units_new_3 (cost=0.56..5.68 rows=1 width=16) (actual time=0.445..0.445 rows=0 loops=1)

  • Index Cond: (status = 'deleted'::text)