explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Al8j : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #P1Js; plan #PQHU; plan #6cOj; plan #J8kif; plan #dMxR; plan #sGcK

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 760.727 10,451.694 ↑ 206,000.6 1,040 1

GroupAggregate (cost=319,312,666.91..349,972,097.55 rows=214,240,639 width=186) (actual time=9,326.434..10,451.694 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 95
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.019 ms, Inlining 38.578 ms, Optimization 851.243 ms, Emission 517.624 ms, Total 1418.464 ms
2. 2,649.155 9,690.967 ↑ 183.2 1,197,701 1

Sort (cost=319,312,666.91..319,861,071.22 rows=219,361,725 width=126) (actual time=9,326.401..9,690.967 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,080kB
3. 85.440 7,041.812 ↑ 183.2 1,197,701 1

Subquery Scan on ufc (cost=219,325,540.45..231,938,839.64 rows=219,361,725 width=126) (actual time=5,988.147..7,041.812 rows=1,197,701 loops=1)

4. 752.278 6,956.372 ↑ 183.2 1,197,701 1

WindowAgg (cost=219,325,540.45..229,745,222.39 rows=219,361,725 width=170) (actual time=5,988.145..6,956.372 rows=1,197,701 loops=1)

5. 1,353.779 6,204.094 ↑ 183.2 1,197,701 1

Sort (cost=219,325,540.45..219,873,944.76 rows=219,361,725 width=150) (actual time=5,987.630..6,204.094 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,048kB
6. 497.230 4,850.315 ↑ 183.2 1,197,701 1

WindowAgg (cost=118,018,820.37..122,954,459.18 rows=219,361,725 width=150) (actual time=4,035.949..4,850.315 rows=1,197,701 loops=1)

7. 1,659.245 4,353.085 ↑ 183.2 1,197,701 1

Sort (cost=118,018,820.37..118,567,224.68 rows=219,361,725 width=142) (actual time=4,035.835..4,353.085 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. 133.241 2,693.840 ↑ 183.2 1,197,701 1

Nested Loop (cost=123,186.31..24,646,819.10 rows=219,361,725 width=142) (actual time=1,662.178..2,693.840 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. 453.824 2,481.625 ↑ 140.8 39,487 1

Merge Left Join (cost=122,735.77..14,886,314.26 rows=5,561,283 width=110) (actual time=1,657.798..2,481.625 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.174 1,440.252 ↑ 2,125.6 96 1

Sort (cost=106,358.77..106,868.92 rows=204,058 width=130) (actual time=1,440.136..1,440.252 rows=96 loops=1)

  • Sort Key: uracl.registry_id
  • Sort Method: quicksort Memory: 50kB
11. 0.023 1,440.078 ↑ 2,125.6 96 1

Subquery Scan on uracl (cost=22.75..74,411.26 rows=204,058 width=130) (actual time=1,409.978..1,440.078 rows=96 loops=1)

12. 2.293 1,440.055 ↑ 2,125.6 96 1

Nested Loop (cost=22.75..72,370.68 rows=204,058 width=214) (actual time=1,409.976..1,440.055 rows=96 loops=1)

13. 0.104 0.104 ↑ 1.0 514 1

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

14. 4.112 1,437.658 ↓ 0.0 0 514

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

  • Hash Cond: (racl.trustee_id = trustee_id.trustee_id)
15. 1,427.378 1,427.378 ↑ 1.0 96 514

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 6.168 6.168 ↑ 1,000.0 1 514

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

18. 397.112 587.549 ↓ 162.9 2,726,209 1

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

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

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

20. 141.658 185.176 ↓ 1.7 28,399 1

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

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

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

22. 0.348 2.695 ↑ 1.0 2,743 1

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

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

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

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

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

25. 0.364 1.062 ↑ 1.0 2,743 1

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

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

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
27. 74.608 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.003 4.366 ↑ 3.2 36 1

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

29. 0.460 4.363 ↑ 3.2 36 1

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

  • Group Key: i_1.id
30. 0.604 3.903 ↑ 1.0 2,743 1

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

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

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

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

33. 0.291 0.873 ↑ 1.0 2,743 1

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

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

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

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

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

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

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

Execution time : 10,552.468 ms