explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hUBw : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 724.320 8,776.148 ↑ 2,737,752.2 1,040 1

GroupAggregate (cost=5,004,498,490.40..5,411,963,243.71 rows=2,847,262,239 width=186) (actual time=7,712.231..8,776.148 rows=1,040 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 13.067 ms, Inlining 37.286 ms, Optimization 690.811 ms, Emission 450.756 ms, Total 1191.920 ms
2. 2,385.518 8,051.828 ↑ 2,434.1 1,197,701 1

Sort (cost=5,004,498,490.40..5,011,786,800.61 rows=2,915,324,084 width=126) (actual time=7,712.201..8,051.828 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,080kB
3. 79.426 5,666.310 ↑ 2,434.1 1,197,701 1

Subquery Scan on ufc (cost=3,431,937,459.94..3,599,568,594.77 rows=2,915,324,084 width=126) (actual time=4,689.416..5,666.310 rows=1,197,701 loops=1)

4. 695.660 5,586.884 ↑ 2,434.1 1,197,701 1

WindowAgg (cost=3,431,937,459.94..3,570,415,353.93 rows=2,915,324,084 width=170) (actual time=4,689.414..5,586.884 rows=1,197,701 loops=1)

5. 1,236.731 4,891.224 ↑ 2,434.1 1,197,701 1

Sort (cost=3,431,937,459.94..3,439,225,770.15 rows=2,915,324,084 width=150) (actual time=4,688.999..4,891.224 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,056kB
6. 434.417 3,654.493 ↑ 2,434.1 1,197,701 1

WindowAgg (cost=1,811,945,482.42..1,877,540,274.31 rows=2,915,324,084 width=150) (actual time=2,930.526..3,654.493 rows=1,197,701 loops=1)

7. 1,443.224 3,220.076 ↑ 2,434.1 1,197,701 1

Sort (cost=1,811,945,482.42..1,819,233,792.63 rows=2,915,324,084 width=142) (actual time=2,930.435..3,220.076 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. 88.313 1,776.852 ↑ 2,434.1 1,197,701 1

Nested Loop (cost=15,910.41..307,370,726.79 rows=2,915,324,084 width=142) (actual time=1,291.657..1,776.852 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. 313.951 1,609.565 ↑ 1,871.7 39,487 1

Hash Left Join (cost=15,459.87..177,658,890.51 rows=73,909,620 width=110) (actual time=1,288.547..1,609.565 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 (racl.permissions))))
  • Rows Removed by Join Filter: 2,686,870
10. 0.296 1,189.354 ↑ 2,570.0 96 1

Nested Loop (cost=47.70..14,638.74 rows=246,720 width=130) (actual time=1,182.252..1,189.354 rows=96 loops=1)

11. 0.176 0.176 ↑ 1.0 514 1

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

12. 0.473 1,188.882 ↓ 0.0 0 514

Hash Join (cost=47.70..71.25 rows=480 width=96) (actual time=2.313..2.313 rows=0 loops=514)

  • Hash Cond: (trustee_id.trustee_id = racl.trustee_id)
13. 1,187.340 1,187.340 ↑ 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.310..2.310 rows=1 loops=514)

14. 0.019 1.069 ↑ 1.0 96 1

Hash (cost=46.25..46.25 rows=96 width=112) (actual time=1.068..1.069 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
15. 0.010 1.050 ↑ 1.0 96 1

Subquery Scan on racl (cost=0.00..46.25 rows=96 width=112) (actual time=0.142..1.050 rows=96 loops=1)

16. 1.040 1.040 ↑ 1.0 96 1

Seq Scan on registry_acl racl_1 (cost=0.00..45.29 rows=96 width=180) (actual time=0.140..1.040 rows=96 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 567
17. 6.346 106.260 ↓ 1.7 28,399 1

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

  • Buckets: 32,768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 4,694kB
18. 2.379 99.914 ↓ 1.7 28,399 1

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

19. 72.028 97.535 ↓ 1.7 28,399 1

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

  • Hash Cond: (f.attendance_period_id = ap.id)
20. 23.172 23.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.284..23.172 rows=40,138 loops=1)

21. 0.339 2.335 ↑ 1.0 2,743 1

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

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

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

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

24. 0.285 0.736 ↑ 1.0 2,743 1

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

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

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
26. 75.863 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)

27. 0.003 3.111 ↑ 3.2 36 1

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

28. 0.329 3.108 ↑ 3.2 36 1

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

  • Group Key: i_1.id
29. 0.382 2.779 ↑ 1.0 2,743 1

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

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

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

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

32. 0.268 0.750 ↑ 1.0 2,743 1

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

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

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

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

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

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

Execution time : 9,434.173 ms