explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 774.432 10,766.188 ↑ 2,262,607.0 1,040 1

GroupAggregate (cost=4,126,026,771.16..4,462,774,779.08 rows=2,353,111,263 width=186) (actual time=9,616.603..10,766.188 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 93
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 21.024 ms, Inlining 53.849 ms, Optimization 756.456 ms, Emission 561.488 ms, Total 1392.818 ms
2. 2,824.262 9,991.756 ↑ 2,011.7 1,197,701 1

Sort (cost=4,126,026,771.16..4,132,050,172.99 rows=2,409,360,733 width=126) (actual time=9,616.580..9,991.756 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,088kB
3. 90.386 7,167.494 ↑ 2,011.7 1,197,701 1

Subquery Scan on ufc (cost=2,829,701,426.64..2,968,239,668.79 rows=2,409,360,733 width=126) (actual time=5,914.775..7,167.494 rows=1,197,701 loops=1)

4. 913.955 7,077.108 ↑ 2,011.7 1,197,701 1

WindowAgg (cost=2,829,701,426.64..2,944,146,061.46 rows=2,409,360,733 width=170) (actual time=5,914.774..7,077.108 rows=1,197,701 loops=1)

5. 1,301.778 6,163.153 ↑ 2,011.7 1,197,701 1

Sort (cost=2,829,701,426.64..2,835,724,828.48 rows=2,409,360,733 width=150) (actual time=5,914.364..6,163.153 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,048kB
6. 440.498 4,861.375 ↑ 2,011.7 1,197,701 1

WindowAgg (cost=1,494,176,912.78..1,548,387,529.27 rows=2,409,360,733 width=150) (actual time=4,128.042..4,861.375 rows=1,197,701 loops=1)

7. 1,562.370 4,420.877 ↑ 2,011.7 1,197,701 1

Sort (cost=1,494,176,912.78..1,500,200,314.61 rows=2,409,360,733 width=142) (actual time=4,127.850..4,420.877 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, users.name, tf.target_approval_level
  • Sort Method: external merge Disk: 278,664kB
8. 110.480 2,858.507 ↑ 2,011.7 1,197,701 1

Nested Loop (cost=15,885.72..254,038,607.91 rows=2,409,360,733 width=142) (actual time=1,518.126..2,858.507 rows=1,197,701 loops=1)

  • Join Filter: ((racl.institute_id = i.id) OR (racl.institute_id IS NULL))
  • Rows Removed by Join Filter: 223,831
9. 365.927 2,669.053 ↑ 1,546.9 39,487 1

Hash Left Join (cost=15,435.17..146,838,574.33 rows=61,082,382 width=110) (actual time=1,515.162..2,669.053 rows=39,487 loops=1)

  • Hash 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. 2.291 2,172.171 ↑ 2,124.0 96 1

Nested Loop (cost=23.00..22,343.32 rows=203,901 width=130) (actual time=1,383.754..2,172.171 rows=96 loops=1)

11. 0.286 0.286 ↑ 1.0 514 1

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

12. 13.878 2,169.594 ↓ 0.0 0 514

Hash Join (cost=23.00..62.42 rows=397 width=96) (actual time=4.115..4.221 rows=0 loops=514)

  • Hash Cond: (racl.trustee_id = trustee_id.trustee_id)
13. 34.952 2,137.726 ↑ 1.0 96 514

Nested Loop (cost=0.25..23.46 rows=96 width=112) (actual time=2.738..4.159 rows=96 loops=514)

14. 1,411.958 1,411.958 ↑ 1.0 96 514

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 567
15. 690.816 690.816 ↑ 1.0 1 49,344

Function Scan on to_registry_perm_flags permissions (cost=0.25..0.26 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=49,344)

16. 1.028 17.990 ↑ 1,000.0 1 514

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 16.962 16.962 ↑ 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.033..0.033 rows=1 loops=514)

18. 7.256 130.955 ↓ 1.7 28,399 1

Hash (cost=15,202.99..15,202.99 rows=16,735 width=124) (actual time=130.954..130.955 rows=28,399 loops=1)

  • Buckets: 32,768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 4,694kB
19. 2.515 123.699 ↓ 1.7 28,399 1

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

20. 84.322 121.184 ↓ 1.7 28,399 1

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

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

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

22. 1.448 8.690 ↑ 1.0 2,743 1

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

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

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

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

25. 0.563 1.537 ↑ 1.0 2,743 1

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

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

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

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

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

29. 0.306 2.946 ↑ 3.2 36 1

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

  • Group Key: i_1.id
30. 0.351 2.640 ↑ 1.0 2,743 1

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

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

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

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

33. 0.264 0.733 ↑ 1.0 2,743 1

Hash (cost=186.24..186.24 rows=2,743 width=16) (actual time=0.733..0.733 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.004..0.469 rows=2,743 loops=1)

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

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

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

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

Execution time : 10,908.733 ms