explain.depesz.com

PostgreSQL's explain analyze made readable

Result: km2t : Optimization for: 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; plan #hUBw

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 723.991 9,075.035 ↑ 2,736,717.4 1,040 1

GroupAggregate (cost=5,002,650,526.06..5,409,961,272.58 rows=2,846,186,078 width=186) (actual time=8,007.660..9,075.035 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 110
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.514 ms, Inlining 29.952 ms, Optimization 742.545 ms, Emission 464.913 ms, Total 1248.924 ms
2. 2,456.504 8,351.044 ↑ 2,433.2 1,197,701 1

Sort (cost=5,002,650,526.06..5,009,936,081.56 rows=2,914,222,198 width=126) (actual time=8,007.637..8,351.044 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,080kB
3. 79.296 5,894.540 ↑ 2,433.2 1,197,701 1

Subquery Scan on ufc (cost=3,430,691,810.51..3,598,259,586.90 rows=2,914,222,198 width=126) (actual time=4,869.727..5,894.540 rows=1,197,701 loops=1)

4. 734.491 5,815.244 ↑ 2,433.2 1,197,701 1

WindowAgg (cost=3,430,691,810.51..3,569,117,364.92 rows=2,914,222,198 width=170) (actual time=4,869.725..5,815.244 rows=1,197,701 loops=1)

5. 1,283.562 5,080.753 ↑ 2,433.2 1,197,701 1

Sort (cost=3,430,691,810.51..3,437,977,366.01 rows=2,914,222,198 width=150) (actual time=4,869.306..5,080.753 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,056kB
6. 452.196 3,797.191 ↑ 2,433.2 1,197,701 1

WindowAgg (cost=1,811,320,071.89..1,876,890,071.35 rows=2,914,222,198 width=150) (actual time=3,045.191..3,797.191 rows=1,197,701 loops=1)

7. 1,500.029 3,344.995 ↑ 2,433.2 1,197,701 1

Sort (cost=1,811,320,071.89..1,818,605,627.39 rows=2,914,222,198 width=142) (actual time=3,045.102..3,344.995 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. 95.484 1,844.966 ↑ 2,433.2 1,197,701 1

Nested Loop (cost=15,910.16..307,321,932.73 rows=2,914,222,198 width=142) (actual time=1,361.990..1,844.966 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. 304.886 1,670.508 ↑ 1,871.0 39,487 1

Hash Left Join (cost=15,459.62..177,659,124.13 rows=73,881,684 width=110) (actual time=1,359.341..1,670.508 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.223 1,246.850 ↑ 2,570.0 96 1

Hash Join (cost=47.45..14,872.36 rows=246,720 width=130) (actual time=1,240.531..1,246.850 rows=96 loops=1)

  • Hash Cond: ((get_trustee_list(users.id)) = racl.trustee_id)
11. 1,245.388 1,245.555 ↑ 1,000.0 514 1

ProjectSet (cost=0.00..2,720.21 rows=514,000 width=356) (actual time=1,239.410..1,245.555 rows=514 loops=1)

12. 0.167 0.167 ↑ 1.0 514 1

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

13. 0.020 1.072 ↑ 1.0 96 1

Hash (cost=46.25..46.25 rows=96 width=112) (actual time=1.071..1.072 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
14. 0.009 1.052 ↑ 1.0 96 1

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

15. 1.043 1.043 ↑ 1.0 96 1

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

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

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

  • Buckets: 32,768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 4,694kB
17. 2.881 110.823 ↓ 1.7 28,399 1

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

18. 81.072 107.942 ↓ 1.7 28,399 1

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

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

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

20. 0.353 2.403 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
21. 0.770 2.050 ↑ 1.0 2,743 1

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

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

23. 0.285 0.757 ↑ 1.0 2,743 1

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

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

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

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

26. 0.003 2.651 ↑ 3.2 36 1

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

27. 0.265 2.648 ↑ 3.2 36 1

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

  • Group Key: i_1.id
28. 0.322 2.383 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.institute_id = i_1.id)
29. 0.708 2.006 ↑ 1.0 2,743 1

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

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

31. 0.304 0.760 ↑ 1.0 2,743 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
34. 0.030 0.030 ↑ 1.0 117 1

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

Execution time : 9,176.047 ms