explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 716.345 10,142.058 ↑ 493,701.0 1,040 1

GroupAggregate (cost=775,057,348.21..848,535,766.37 rows=513,449,043 width=186) (actual time=9,082.376..10,142.058 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 14.167 ms, Inlining 33.835 ms, Optimization 768.798 ms, Emission 632.930 ms, Total 1449.730 ms
2. 2,882.082 9,425.713 ↑ 438.9 1,197,701 1

Sort (cost=775,057,348.21..776,371,654.51 rows=525,722,521 width=126) (actual time=9,082.352..9,425.713 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,072kB
3. 79.024 6,543.631 ↑ 438.9 1,197,701 1

Subquery Scan on ufc (cost=532,113,463.54..562,342,508.50 rows=525,722,521 width=126) (actual time=5,521.135..6,543.631 rows=1,197,701 loops=1)

4. 732.910 6,464.607 ↑ 438.9 1,197,701 1

WindowAgg (cost=532,113,463.54..557,085,283.29 rows=525,722,521 width=170) (actual time=5,521.133..6,464.607 rows=1,197,701 loops=1)

5. 1,261.256 5,731.697 ↑ 438.9 1,197,701 1

Sort (cost=532,113,463.54..533,427,769.84 rows=525,722,521 width=150) (actual time=5,520.720..5,731.697 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,056kB
6. 455.187 4,470.441 ↑ 438.9 1,197,701 1

WindowAgg (cost=286,007,039.11..297,835,795.83 rows=525,722,521 width=150) (actual time=3,704.071..4,470.441 rows=1,197,701 loops=1)

7. 1,496.854 4,015.254 ↑ 438.9 1,197,701 1

Sort (cost=286,007,039.11..287,321,345.41 rows=525,722,521 width=142) (actual time=3,703.987..4,015.254 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. 89.578 2,518.400 ↑ 438.9 1,197,701 1

Nested Loop (cost=144,951.86..58,916,985.40 rows=525,722,521 width=142) (actual time=1,669.890..2,518.400 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. 383.375 2,349.848 ↑ 337.5 39,487 1

Merge Left Join (cost=144,501.32..35,525,583.34 rows=13,328,176 width=110) (actual time=1,667.212..2,349.848 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.141 1,448.399 ↑ 2,124.0 96 1

Sort (cost=117,494.04..118,003.79 rows=203,901 width=130) (actual time=1,448.321..1,448.399 rows=96 loops=1)

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

Subquery Scan on uracl (cost=19,305.21..85,572.01 rows=203,901 width=130) (actual time=1,444.643..1,448.258 rows=96 loops=1)

12. 3.793 1,448.235 ↑ 2,124.0 96 1

Hash Join (cost=19,305.21..83,533.00 rows=203,901 width=214) (actual time=1,444.638..1,448.235 rows=96 loops=1)

  • Hash Cond: (racl.trustee_id = ut.trustee_id)
13. 1,437.194 1,437.194 ↑ 1.0 96 1

Seq Scan on registry_acl racl (cost=0.00..21.29 rows=96 width=84) (actual time=1,437.125..1,437.194 rows=96 loops=1)

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

Hash (cost=7,860.21..7,860.21 rows=514,000 width=50) (actual time=7.247..7.248 rows=514 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 527kB
15. 0.081 6.855 ↑ 1,000.0 514 1

Subquery Scan on ut (cost=0.00..7,860.21 rows=514,000 width=50) (actual time=0.264..6.855 rows=514 loops=1)

16. 6.651 6.774 ↑ 1,000.0 514 1

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

17. 0.123 0.123 ↑ 1.0 514 1

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

18. 310.333 518.074 ↓ 68.2 2,737,087 1

Materialize (cost=27,007.28..27,207.97 rows=40,138 width=124) (actual time=203.684..518.074 rows=2,737,087 loops=1)

19. 27.110 207.741 ↑ 1.0 39,182 1

Sort (cost=27,007.28..27,107.62 rows=40,138 width=124) (actual time=203.665..207.741 rows=39,182 loops=1)

  • Sort Key: tf.registry_id
  • Sort Method: external merge Disk: 5,440kB
20. 4.237 180.631 ↑ 1.0 40,138 1

Subquery Scan on tf (cost=524.06..21,330.69 rows=40,138 width=124) (actual time=2.858..180.631 rows=40,138 loops=1)

21. 120.342 176.394 ↑ 1.0 40,138 1

Hash Join (cost=524.06..20,929.31 rows=40,138 width=124) (actual time=2.856..176.394 rows=40,138 loops=1)

  • Hash Cond: (ap.attendance_id = a.id)
22. 19.796 54.613 ↑ 1.0 40,138 1

Hash Join (cost=272.03..10,386.84 rows=40,138 width=194) (actual time=1.371..54.613 rows=40,138 loops=1)

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

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

24. 0.717 1.349 ↑ 1.0 6,579 1

Hash (cost=189.79..189.79 rows=6,579 width=48) (actual time=1.349..1.349 rows=6,579 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 578kB
25. 0.632 0.632 ↑ 1.0 6,579 1

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

26. 0.779 1.439 ↑ 1.0 6,579 1

Hash (cost=169.79..169.79 rows=6,579 width=32) (actual time=1.439..1.439 rows=6,579 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 476kB
27. 0.660 0.660 ↑ 1.0 6,579 1

Seq Scan on attendances a (cost=0.00..169.79 rows=6,579 width=32) (actual time=0.008..0.660 rows=6,579 loops=1)

28. 76.312 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)

29. 0.003 2.662 ↑ 3.2 36 1

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

30. 0.270 2.659 ↑ 3.2 36 1

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

  • Group Key: i_1.id
31. 0.339 2.389 ↑ 1.0 2,743 1

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

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

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

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

34. 0.256 0.728 ↑ 1.0 2,743 1

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

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

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

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

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

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

Execution time : 10,558.128 ms