explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yrbk : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #P1Js; plan #PQHU; plan #6cOj; plan #J8kif; plan #dMxR; plan #sGcK; plan #Al8j; plan #hUBw; plan #km2t; plan #vDBF

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 714.232 9,452.146 ↑ 205,842.1 1,040 1

GroupAggregate (cost=319,074,549.40..349,710,390.11 rows=214,075,799 width=186) (actual time=8,400.892..9,452.146 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 113
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 14.055 ms, Inlining 40.847 ms, Optimization 726.021 ms, Emission 454.707 ms, Total 1235.630 ms
2. 2,404.223 8,737.914 ↑ 183.0 1,197,701 1

Sort (cost=319,074,549.40..319,622,531.76 rows=219,192,944 width=126) (actual time=8,400.844..8,737.914 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,080kB
3. 79.660 6,333.691 ↑ 183.0 1,197,701 1

Subquery Scan on ufc (cost=219,165,576.42..231,769,170.70 rows=219,192,944 width=126) (actual time=5,365.633..6,333.691 rows=1,197,701 loops=1)

4. 685.692 6,254.031 ↑ 183.0 1,197,701 1

WindowAgg (cost=219,165,576.42..229,577,241.26 rows=219,192,944 width=170) (actual time=5,365.631..6,254.031 rows=1,197,701 loops=1)

5. 1,266.145 5,568.339 ↑ 183.0 1,197,701 1

Sort (cost=219,165,576.42..219,713,558.78 rows=219,192,944 width=150) (actual time=5,365.223..5,568.339 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,048kB
6. 477.980 4,302.194 ↑ 183.0 1,197,701 1

WindowAgg (cost=117,938,018.48..122,869,859.72 rows=219,192,944 width=150) (actual time=3,508.333..4,302.194 rows=1,197,701 loops=1)

7. 1,603.580 3,824.214 ↑ 183.0 1,197,701 1

Sort (cost=117,938,018.48..118,486,000.84 rows=219,192,944 width=142) (actual time=3,508.258..3,824.214 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, uracl.user_name, tf.target_approval_level
  • Sort Method: external merge Disk: 278,664kB
8. 99.119 2,220.634 ↑ 183.0 1,197,701 1

Nested Loop (cost=134,321.57..24,639,085.78 rows=219,192,944 width=142) (actual time=1,354.901..2,220.634 rows=1,197,701 loops=1)

  • Join Filter: ((uracl.institute_id = i.id) OR (uracl.institute_id IS NULL))
  • Rows Removed by Join Filter: 223,831
9. 386.148 2,042.541 ↑ 140.7 39,487 1

Merge Left Join (cost=133,871.03..14,886,090.59 rows=5,557,004 width=110) (actual time=1,352.217..2,042.541 rows=39,487 loops=1)

  • Merge Cond: (uracl.registry_id = tf.registry_id)
  • Join Filter: (((uracl.form_type_id IS NULL) OR (uracl.form_type_id = tf.form_type_id)) AND ((uracl.institute_id IS NULL) OR (uracl.institute_id = tf.institute_id)) AND ((uracl.physician_id IS NULL) OR (uracl.physician_id = tf.physician_id)) AND (((tf.target_approval_level = ANY ('{0,5}'::integer[])) AND (uracl.user_id = tf.owner_id)) OR (('frm_approve_'::text || (tf.target_approval_level)::text) = ANY (uracl.permissions))))
  • Rows Removed by Join Filter: 2,686,870
10. 0.088 1,231.649 ↑ 2,124.0 96 1

Sort (cost=117,494.04..118,003.79 rows=203,901 width=130) (actual time=1,231.599..1,231.649 rows=96 loops=1)

  • Sort Key: uracl.registry_id
  • Sort Method: quicksort Memory: 50kB
11. 0.011 1,231.561 ↑ 2,124.0 96 1

Subquery Scan on uracl (cost=19,305.21..85,572.01 rows=203,901 width=130) (actual time=1,229.492..1,231.561 rows=96 loops=1)

12. 2.215 1,231.550 ↑ 2,124.0 96 1

Hash Join (cost=19,305.21..83,533.00 rows=203,901 width=214) (actual time=1,229.490..1,231.550 rows=96 loops=1)

  • Hash Cond: (racl.trustee_id = ut.trustee_id)
13. 1,224.121 1,224.121 ↑ 1.0 96 1

Seq Scan on registry_acl racl (cost=0.00..21.29 rows=96 width=84) (actual time=1,224.100..1,224.121 rows=96 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 567
14. 0.319 5.214 ↑ 1,000.0 514 1

Hash (cost=7,860.21..7,860.21 rows=514,000 width=50) (actual time=5.213..5.214 rows=514 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 527kB
15. 0.061 4.895 ↑ 1,000.0 514 1

Subquery Scan on ut (cost=0.00..7,860.21 rows=514,000 width=50) (actual time=0.236..4.895 rows=514 loops=1)

16. 4.755 4.834 ↑ 1,000.0 514 1

ProjectSet (cost=0.00..2,720.21 rows=514,000 width=356) (actual time=0.233..4.834 rows=514 loops=1)

17. 0.079 0.079 ↑ 1.0 514 1

Seq Scan on users (cost=0.00..19.14 rows=514 width=34) (actual time=0.006..0.079 rows=514 loops=1)

18. 323.963 424.744 ↓ 162.9 2,726,209 1

Sort (cost=16,377.00..16,418.83 rows=16,735 width=124) (actual time=115.244..424.744 rows=2,726,209 loops=1)

  • Sort Key: tf.registry_id
  • Sort Method: external sort Disk: 3,960kB
19. 2.254 100.781 ↓ 1.7 28,399 1

Subquery Scan on tf (cost=461.88..15,202.99 rows=16,735 width=124) (actual time=2.368..100.781 rows=28,399 loops=1)

20. 73.563 98.527 ↓ 1.7 28,399 1

Hash Join (cost=461.88..15,035.64 rows=16,735 width=124) (actual time=2.366..98.527 rows=28,399 loops=1)

  • Hash Cond: (f.attendance_period_id = ap.id)
21. 22.634 22.634 ↑ 1.0 40,138 1

Seq Scan on forms f (cost=0.00..10,009.38 rows=40,138 width=178) (actual time=0.004..22.634 rows=40,138 loops=1)

22. 0.338 2.330 ↑ 1.0 2,743 1

Hash (cost=427.60..427.60 rows=2,743 width=64) (actual time=2.330..2.330 rows=2,743 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
23. 0.741 1.992 ↑ 1.0 2,743 1

Hash Join (cost=220.53..427.60 rows=2,743 width=64) (actual time=0.791..1.992 rows=2,743 loops=1)

  • Hash Cond: (ap.attendance_id = a.id)
24. 0.488 0.488 ↑ 1.0 6,579 1

Seq Scan on attendance_periods ap (cost=0.00..189.79 rows=6,579 width=48) (actual time=0.003..0.488 rows=6,579 loops=1)

25. 0.298 0.763 ↑ 1.0 2,743 1

Hash (cost=186.24..186.24 rows=2,743 width=32) (actual time=0.763..0.763 rows=2,743 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 204kB
26. 0.465 0.465 ↑ 1.0 2,743 1

Seq Scan on attendances a (cost=0.00..186.24 rows=2,743 width=32) (actual time=0.005..0.465 rows=2,743 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
27. 76.301 78.974 ↑ 3.2 36 39,487

Materialize (cost=450.54..453.47 rows=117 width=64) (actual time=0.000..0.002 rows=36 loops=39,487)

28. 0.004 2.673 ↑ 3.2 36 1

Subquery Scan on i (cost=450.54..452.88 rows=117 width=64) (actual time=2.666..2.673 rows=36 loops=1)

29. 0.271 2.669 ↑ 3.2 36 1

HashAggregate (cost=450.54..451.71 rows=117 width=528) (actual time=2.664..2.669 rows=36 loops=1)

  • Group Key: i_1.id
30. 0.336 2.398 ↑ 1.0 2,743 1

Hash Join (cost=229.16..443.68 rows=2,743 width=64) (actual time=0.800..2.398 rows=2,743 loops=1)

  • Hash Cond: (ap_1.institute_id = i_1.id)
31. 0.744 2.021 ↑ 1.0 2,743 1

Hash Join (cost=220.53..427.60 rows=2,743 width=16) (actual time=0.750..2.021 rows=2,743 loops=1)

  • Hash Cond: (ap_1.attendance_id = a_1.id)
32. 0.545 0.545 ↑ 1.0 6,579 1

Seq Scan on attendance_periods ap_1 (cost=0.00..189.79 rows=6,579 width=32) (actual time=0.004..0.545 rows=6,579 loops=1)

33. 0.263 0.732 ↑ 1.0 2,743 1

Hash (cost=186.24..186.24 rows=2,743 width=16) (actual time=0.732..0.732 rows=2,743 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 161kB
34. 0.469 0.469 ↑ 1.0 2,743 1

Seq Scan on attendances a_1 (cost=0.00..186.24 rows=2,743 width=16) (actual time=0.005..0.469 rows=2,743 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
35. 0.016 0.041 ↑ 1.0 117 1

Hash (cost=7.17..7.17 rows=117 width=64) (actual time=0.041..0.041 rows=117 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
36. 0.025 0.025 ↑ 1.0 117 1

Seq Scan on institutes i_1 (cost=0.00..7.17 rows=117 width=64) (actual time=0.010..0.025 rows=117 loops=1)

Execution time : 9,550.401 ms