explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 777.701 10,987.821 ↑ 781,308.3 1,893 1

GroupAggregate (cost=2,588,131,022.18..2,799,789,468.32 rows=1,479,016,609 width=186) (actual time=9,834.934..10,987.821 rows=1,893 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 94
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 19.647 ms, Inlining 43.701 ms, Optimization 824.437 ms, Emission 595.976 ms, Total 1483.761 ms
2. 2,896.526 10,210.120 ↑ 1,257.0 1,204,783 1

Sort (cost=2,588,131,022.18..2,591,916,950.64 rows=1,514,371,385 width=126) (actual time=9,834.908..10,210.120 rows=1,204,783 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 256,208kB
3. 90.583 7,313.594 ↑ 1,257.0 1,204,783 1

Subquery Scan on ufc (cost=1,778,415,734.27..1,865,492,088.91 rows=1,514,371,385 width=126) (actual time=6,130.135..7,313.594 rows=1,204,783 loops=1)

4. 856.429 7,223.011 ↑ 1,257.0 1,204,783 1

WindowAgg (cost=1,778,415,734.27..1,850,348,375.06 rows=1,514,371,385 width=170) (actual time=6,130.133..7,223.011 rows=1,204,783 loops=1)

5. 1,461.365 6,366.582 ↑ 1,257.0 1,204,783 1

Sort (cost=1,778,415,734.27..1,782,201,662.73 rows=1,514,371,385 width=150) (actual time=6,129.729..6,366.582 rows=1,204,783 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 289,184kB
6. 569.850 4,905.217 ↑ 1,257.0 1,204,783 1

WindowAgg (cost=944,062,332.33..978,135,688.49 rows=1,514,371,385 width=150) (actual time=3,978.662..4,905.217 rows=1,204,783 loops=1)

7. 1,708.700 4,335.367 ↑ 1,257.0 1,204,783 1

Sort (cost=944,062,332.33..947,848,260.79 rows=1,514,371,385 width=142) (actual time=3,978.472..4,335.367 rows=1,204,783 loops=1)

  • Sort Key: tf.attendance_id, users.name, tf.target_approval_level
  • Sort Method: external merge Disk: 279,720kB
8. 131.280 2,626.667 ↑ 1,257.0 1,204,783 1

Nested Loop (cost=401,365.05..169,662,651.56 rows=1,514,371,385 width=142) (actual time=1,664.156..2,626.667 rows=1,204,783 loops=1)

  • Join Filter: ((racl.institute_id = i.id) OR (racl.institute_id IS NULL))
  • Rows Removed by Join Filter: 237,161
9. 423.605 2,415.279 ↑ 958.5 40,054 1

Merge Left Join (cost=400,914.51..102,283,338.07 rows=38,392,513 width=110) (actual time=1,658.172..2,415.279 rows=40,054 loops=1)

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

Sort (cost=384,537.51..388,057.99 rows=1,408,190 width=130) (actual time=1,500.563..1,500.690 rows=663 loops=1)

  • Sort Key: racl.registry_id
  • Sort Method: quicksort Memory: 196kB
11. 0.608 1,500.090 ↑ 2,124.0 663 1

Hash Join (cost=41.68..48,195.22 rows=1,408,190 width=130) (actual time=1,488.606..1,500.090 rows=663 loops=1)

  • Hash Cond: (trustee_id.trustee_id = racl.trustee_id)
12. 0.361 1,482.473 ↑ 1,000.0 514 1

Nested Loop (cost=0.25..10,299.39 rows=514,000 width=50) (actual time=1,471.564..1,482.473 rows=514 loops=1)

13. 0.250 0.250 ↑ 1.0 514 1

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

14. 1,481.862 1,481.862 ↑ 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=2.882..2.883 rows=1 loops=514)

15. 0.388 17.009 ↑ 1.0 663 1

Hash (cost=33.14..33.14 rows=663 width=112) (actual time=17.009..17.009 rows=663 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 117kB
16. 0.485 16.621 ↑ 1.0 663 1

Nested Loop (cost=0.25..33.14 rows=663 width=112) (actual time=0.231..16.621 rows=663 loops=1)

17. 0.224 0.224 ↑ 1.0 663 1

Seq Scan on registry_acl racl (cost=0.00..19.63 rows=663 width=84) (actual time=0.010..0.224 rows=663 loops=1)

18. 15.912 15.912 ↑ 1.0 1 663

Function Scan on to_registry_perm_flags permissions (cost=0.25..0.26 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=663)

19. 349.661 490.984 ↓ 162.9 2,726,209 1

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

  • Sort Key: tf.registry_id
  • Sort Method: external sort Disk: 3,960kB
20. 2.930 141.323 ↓ 1.7 28,399 1

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

21. 94.189 138.393 ↓ 1.7 28,399 1

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

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

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

23. 0.533 4.406 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
24. 1.390 3.873 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap.attendance_id = a.id)
25. 0.855 0.855 ↑ 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.855 rows=6,579 loops=1)

26. 0.622 1.628 ↑ 1.0 2,743 1

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

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

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
28. 74.141 80.108 ↑ 3.2 36 40,054

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

29. 0.005 5.967 ↑ 3.2 36 1

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

30. 0.533 5.962 ↑ 3.2 36 1

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

  • Group Key: i_1.id
31. 0.594 5.429 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.institute_id = i_1.id)
32. 2.213 4.779 ↑ 1.0 2,743 1

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

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

34. 0.392 1.095 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 161kB
35. 0.703 0.703 ↑ 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.703 rows=2,743 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
37. 0.036 0.036 ↑ 1.0 117 1

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

Execution time : 11,099.902 ms