explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P1Js

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 8,392.486 ↑ 205,842.0 1,040 1

GroupAggregate (cost=319,063,204.78..349,699,030.95 rows=214,075,697 width=186) (actual time=7,427.237..8,392.486 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 88
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 17.121 ms, Inlining 47.315 ms, Optimization 931.073 ms, Emission 546.024 ms, Total 1541.533 ms
  • Functions: 95
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 10.487 ms, Inlining 36.912 ms, Optimization 711.211 ms, Emission 419.387 ms, Total 1177.997 ms
2. 0.000 7,736.971 ↑ 193.2 1,134,295 1

Sort (cost=319,063,204.78..319,611,186.88 rows=219,192,840 width=126) (actual time=7,427.212..7,736.971 rows=1,134,295 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 242,792kB
3. 8,581.389 8,581.389 ↑ 193.2 1,134,295 1

Subquery Scan on ufc (cost=219154280.94..231757869.24 rows=219192840 width=126) (actuWindowAgg (cost=231,141,388.94..242,649,013.04 rows=219,192,840 width=170) (actual time=7,460.041..8,581.389 rows=1,134,295 loops=1)

4. 1,933.510 7,701.634 ↑ 193.2 1,134,295 1

Sort (cost=231,141,388.94..231,689,371.04 rows=219,192,840 width=166) (actual time=7,459.565..7,701.634 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 291,832kB
5. 0.000 5,768.124 ↑ 193.2 1,134,295 1

WindowAgg (cost=123,920,322.50..128,852,161.40 rows=219,192,840 width=166) (actual time=4,557.574..5,768.124 rows=1,134,295 loops=1)

6. 1,868.166 4,987.539 ↑ 193.2 1,134,295 1

Sort (cost=123,920,322.50..124,468,304.60 rows=219,192,840 width=158) (actual time=4,557.503..4,987.539 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, uracl.user_name, tf.target_approval_level
  • Sort Method: external merge Disk: 282,944kB
7. 151.782 3,119.373 ↑ 193.2 1,134,295 1

Nested Loop (cost=123,120.01..24,627,878.95 rows=219,192,840 width=158) (actual time=1,983.300..3,119.373 rows=1,134,295 loops=1)

  • Join Filter: ((uracl.institute_id = i.id) OR (uracl.institute_id IS NULL))
  • Rows Removed by Join Filter: 64,721
8. 489.406 2,900.979 ↑ 166.8 33,306 1

Merge Left Join (cost=122,669.47..14,874,889.03 rows=5,557,001 width=126) (actual time=1,975.207..2,900.979 rows=33,306 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 = 0) 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,693,058
9. 0.249 1,610.486 ↑ 2,124.0 96 1

Sort (cost=106,292.47..106,802.23 rows=203,901 width=130) (actual time=1,610.401..1,610.486 rows=96 loops=1)

  • Sort Key: uracl.registry_id
  • Sort Method: quicksort Memory: 50kB
10. 0.084 1,610.237 ↑ 2,124.0 96 1

Subquery Scan on uracl (cost=22.75..74,370.44 rows=203,901 width=130) (actual time=1,527.823..1,610.237 rows=96 loops=1)

11. 7.692 1,610.153 ↑ 2,124.0 96 1

Nested Loop (cost=22.75..72,331.43 rows=203,901 width=214) (actual time=1,527.819..1,610.153 rows=96 loops=1)

12. 0.323 0.323 ↑ 1.0 514 1

Seq Scan on users u (cost=0.00..19.14 rows=514 width=34) (actual time=0.014..0.323 rows=514 loops=1)

13. 10.280 1,602.138 ↓ 0.0 0 514

Hash Join (cost=22.75..60.25 rows=397 width=68) (actual time=3.114..3.117 rows=0 loops=514)

  • Hash Cond: (racl.trustee_id = trustee_list_id.trustee_id)
14. 1,572.326 1,572.326 ↑ 1.0 96 514

Seq Scan on registry_acl racl (cost=0.00..21.29 rows=96 width=84) (actual time=3.028..3.059 rows=96 loops=514)

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

Hash (cost=10.25..10.25 rows=1,000 width=16) (actual time=0.038..0.038 rows=1 loops=514)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 18.504 18.504 ↑ 1,000.0 1 514

Function Scan on get_trustee_list trustee_list_id (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.036..0.036 rows=1 loops=514)

17. 491.545 801.087 ↓ 162.9 2,726,209 1

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

  • Sort Key: tf.registry_id
  • Sort Method: external sort Disk: 3,960kB
18. 7.301 309.542 ↓ 1.7 28,399 1

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

19. 224.437 302.241 ↓ 1.7 28,399 1

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

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

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

21. 1.039 6.192 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
22. 2.082 5.153 ↑ 1.0 2,743 1

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

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

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

24. 0.790 1.883 ↑ 1.0 2,743 1

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

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

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
26. 58.541 66.612 ↑ 3.2 36 33,306

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

27. 0.006 8.071 ↑ 3.2 36 1

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

28. 0.825 8.065 ↑ 3.2 36 1

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

  • Group Key: i_1.id
29. 0.940 7.240 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.institute_id = i_1.id)
30. 2.810 6.198 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.attendance_id = a_1.id)
31. 1.507 1.507 ↑ 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.012..1.507 rows=6,579 loops=1)

32. 0.744 1.881 ↑ 1.0 2,743 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
35. 0.066 0.066 ↑ 1.0 117 1

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

36. 618.330 5,567.371 ↑ 193.2 1,134,295 1

WindowAgg (cost=219,154,280.94..229,565,940.84 rows=219,192,840 width=170) (actual time=4,759.098..5,567.371 rows=1,134,295 loops=1)

37. 1,123.909 4,949.041 ↑ 193.2 1,134,295 1

Sort (cost=219,154,280.94..219,702,263.04 rows=219,192,840 width=150) (actual time=4,758.660..4,949.041 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 274,056kB
38. 393.775 3,825.132 ↑ 193.2 1,134,295 1

WindowAgg (cost=117,926,768.50..122,858,607.40 rows=219,192,840 width=150) (actual time=3,155.219..3,825.132 rows=1,134,295 loops=1)

39. 1,355.184 3,431.357 ↑ 193.2 1,134,295 1

Sort (cost=117,926,768.50..118,474,750.60 rows=219,192,840 width=142) (actual time=3,155.090..3,431.357 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, uracl.user_name, tf.target_approval_level
  • Sort Method: external merge Disk: 265,176kB
40. 74.789 2,076.173 ↑ 193.2 1,134,295 1

Nested Loop (cost=123,120.01..24,627,878.95 rows=219,192,840 width=142) (actual time=1,312.447..2,076.173 rows=1,134,295 loops=1)

  • Join Filter: ((uracl.institute_id = i.id) OR (uracl.institute_id IS NULL))
  • Rows Removed by Join Filter: 64,721
41. 340.154 1,934.772 ↑ 166.8 33,306 1

Merge Left Join (cost=122,669.47..14,874,889.03 rows=5,557,001 width=110) (actual time=1,309.302..1,934.772 rows=33,306 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 = 0) 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,693,058
42. 0.080 1,194.982 ↑ 2,124.0 96 1

Sort (cost=106,292.47..106,802.23 rows=203,901 width=130) (actual time=1,194.948..1,194.982 rows=96 loops=1)

  • Sort Key: uracl.registry_id
  • Sort Method: quicksort Memory: 50kB
43. 0.016 1,194.902 ↑ 2,124.0 96 1

Subquery Scan on uracl (cost=22.75..74,370.44 rows=203,901 width=130) (actual time=1,168.839..1,194.902 rows=96 loops=1)

44. 1.801 1,194.886 ↑ 2,124.0 96 1

Nested Loop (cost=22.75..72,331.43 rows=203,901 width=214) (actual time=1,168.837..1,194.886 rows=96 loops=1)

45. 0.091 0.091 ↑ 1.0 514 1

Seq Scan on users u (cost=0.00..19.14 rows=514 width=34) (actual time=0.022..0.091 rows=514 loops=1)

46. 3.598 1,192.994 ↓ 0.0 0 514

Hash Join (cost=22.75..60.25 rows=397 width=68) (actual time=2.319..2.321 rows=0 loops=514)

  • Hash Cond: (racl.trustee_id = trustee_list_id.trustee_id)
47. 1,184.256 1,184.256 ↑ 1.0 96 514

Seq Scan on registry_acl racl (cost=0.00..21.29 rows=96 width=84) (actual time=2.293..2.304 rows=96 loops=514)

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

Hash (cost=10.25..10.25 rows=1,000 width=16) (actual time=0.010..0.010 rows=1 loops=514)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 4.626 4.626 ↑ 1,000.0 1 514

Function Scan on get_trustee_list trustee_list_id (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.009..0.009 rows=1 loops=514)

50. 300.524 399.636 ↓ 162.9 2,726,209 1

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

  • Sort Key: tf.registry_id
  • Sort Method: external sort Disk: 3,960kB
51. 2.562 99.112 ↓ 1.7 28,399 1

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

52. 70.584 96.550 ↓ 1.7 28,399 1

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

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

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

54. 0.339 2.318 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
55. 0.752 1.979 ↑ 1.0 2,743 1

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

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

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

57. 0.285 0.734 ↑ 1.0 2,743 1

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

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

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
59. 63.484 66.612 ↑ 3.2 36 33,306

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

60. 0.006 3.128 ↑ 3.2 36 1

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

61. 0.291 3.122 ↑ 3.2 36 1

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

  • Group Key: i_1.id
62. 0.349 2.831 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.institute_id = i_1.id)
63. 0.758 2.360 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.attendance_id = a_1.id)
64. 0.574 0.574 ↑ 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.014..0.574 rows=6,579 loops=1)

65. 0.340 1.028 ↑ 1.0 2,743 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
68. 0.090 0.090 ↑ 1.0 117 1

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

Execution time : 8,485.626 ms