explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MNIC

Settings
# exclusive inclusive rows x rows loops node
1. 2.689 8,947.822 ↓ 5.7 86 1

GroupAggregate (cost=3,882.69..3,883.29 rows=15 width=97) (actual time=8,944.864..8,947.822 rows=86 loops=1)

  • Group Key: mq.qualification_library_item_id, qli.name, qt.name
2. 2.945 8,945.133 ↓ 379.1 5,686 1

Sort (cost=3,882.69..3,882.73 rows=15 width=90) (actual time=8,944.843..8,945.133 rows=5,686 loops=1)

  • Sort Key: mq.qualification_library_item_id, qli.name, qt.name
  • Sort Method: quicksort Memory: 915kB
3. 3,112.803 8,942.188 ↓ 379.1 5,686 1

Gather (cost=3,549.50..3,882.40 rows=15 width=90) (actual time=8,542.596..8,942.188 rows=5,686 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.166 5,829.385 ↓ 270.7 1,895 3 / 3

Parallel Append (cost=2,549.50..2,880.90 rows=7 width=90) (actual time=5,671.818..5,829.385 rows=1,895 loops=3)

5. 0.137 2,976.327 ↓ 877.0 877 1 / 3

Nested Loop (cost=2,549.50..2,880.86 rows=1 width=90) (actual time=8,479.080..8,928.982 rows=877 loops=1)

6. 1.692 2,975.898 ↓ 877.0 877 1 / 3

Nested Loop (cost=2,549.36..2,880.55 rows=1 width=92) (actual time=8,479.076..8,927.694 rows=877 loops=1)

  • Join Filter: (mq.qualification_library_item_id = qli.id)
  • Rows Removed by Join Filter: 42,710
7. 1.191 2,973.329 ↓ 877.0 877 1 / 3

Nested Loop (cost=2,549.36..2,875.45 rows=1 width=385) (actual time=8,479.066..8,919.988 rows=877 loops=1)

8. 89.767 2,971.846 ↓ 877.0 877 1 / 3

Nested Loop Left Join (cost=2,549.22..2,875.23 rows=1 width=64) (actual time=8,479.025..8,915.537 rows=877 loops=1)

  • Join Filter: ((qi_current.person_id = mq.person_id) AND (qi_current.qualification_library_item_id = mq.qualification_library_item_id))
  • Rows Removed by Join Filter: 3,812,593
9. 0.224 1.133 ↓ 877.0 877 1 / 3

Hash Join (cost=98.74..424.72 rows=1 width=32) (actual time=2.033..3.400 rows=877 loops=1)

  • Hash Cond: (mq.job_id = j.id)
  • Join Filter: (j.effective_dates && mq.effective_dates)
10. 0.540 0.540 ↓ 5.4 902 1 / 3

Seq Scan on mandatory_qualifications mq (cost=0.00..325.54 rows=168 width=70) (actual time=0.903..1.620 rows=902 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((deleted_at IS NULL) OR (deleted_at > CURRENT_TIMESTAMP)) AND ((job_restricted_effective_dates IS NULL) OR (job_restricted_effective_dates @> CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 6,975
11. 0.114 0.369 ↑ 1.0 1,703 1 / 3

Hash (cost=76.78..76.78 rows=1,757 width=37) (actual time=1.108..1.108 rows=1,703 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 133kB
12. 0.255 0.255 ↑ 1.0 1,703 1 / 3

Seq Scan on jobs j (cost=0.00..76.78 rows=1,757 width=37) (actual time=0.017..0.765 rows=1,703 loops=1)

  • Filter: ((id IS NULL) OR ((deleted_at IS NULL) AND (effective_dates @> CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 1,282
13. 57.866 2,880.945 ↓ 4,348.0 4,348 877 / 3

Sort (cost=2,450.48..2,450.49 rows=1 width=451) (actual time=9.666..9.855 rows=4,348 loops=877)

  • Sort Key: qli_2.name, qi_current.issue_date DESC
  • Worker 1: Sort Method: quicksort Memory: 825kB
14. 628.941 2,823.079 ↓ 4,348.0 4,348 1 / 3

Nested Loop Left Join (cost=2,292.45..2,450.47 rows=1 width=451) (actual time=37.193..8,469.236 rows=4,348 loops=1)

  • Join Filter: (_qi_3.id = ((array_agg(_qi_2.id))[1]))
  • Rows Removed by Join Filter: 21,992,132
15. 6.904 108.547 ↓ 4,348.0 4,348 1 / 3

Nested Loop Left Join (cost=1,615.34..1,693.41 rows=1 width=115) (actual time=27.812..325.642 rows=4,348 loops=1)

  • Join Filter: ((((array_agg(_qi_2.person_id))[1]) = _vcqi.person_id) AND (((array_agg(_qi_2.qualification_library_item_id))[1]) = _vcqi.qualification_library_item_id))
  • Rows Removed by Join Filter: 226,044
16. 0.253 19.032 ↓ 4,348.0 4,348 1 / 3

Nested Loop (cost=1,128.03..1,206.04 rows=1 width=131) (actual time=23.765..57.095 rows=4,348 loops=1)

17. 0.638 17.329 ↓ 4,348.0 4,348 1 / 3

Nested Loop (cost=1,127.90..1,205.78 rows=1 width=147) (actual time=23.742..51.988 rows=4,348 loops=1)

18. 1.124 15.242 ↓ 4,348.0 4,348 1 / 3

Nested Loop (cost=1,127.76..1,205.62 rows=1 width=104) (actual time=23.738..45.726 rows=4,348 loops=1)

  • Join Filter: (_vcqi.id = qi_current.id)
19. 0.873 9.770 ↓ 4,348.0 4,348 1 / 3

Hash Join (cost=1,127.47..1,205.21 rows=1 width=80) (actual time=23.716..29.310 rows=4,348 loops=1)

  • Hash Cond: (_qi.id = _vcqi.id)
20. 0.674 3.544 ↓ 20.0 5,058 1 / 3

Hash Join (cost=677.11..753.89 rows=253 width=32) (actual time=7.648..10.632 rows=5,058 loops=1)

  • Hash Cond: ((CASE WHEN ((_status.name = 'Approved'::text) AND (_qi.expiry_date IS NOT NULL) AND (_qi.expiry_date <= CURRENT_TIMESTAMP)) THEN '57cd960d-f260-4ce0-82ac-a86b5c29d932'::uuid WHEN ((_status.name = 'Approved'::text) AND (_qi.issue_date IS NOT NULL) AND (_qi.issue_date > CURRENT_TIMESTAMP)) THEN '42d2be32-5a19-466b-b927-59edf3635eae'::uuid WHEN ((_status.name = 'Approved'::text) AND (_qi.expiry_date IS NOT NULL) AND (_li.upcoming_expiry_warning_period > 0) AND (_qi.expiry_date <= (CURRENT_TIMESTAMP + ((_li.upcoming_expiry_warning_period)::double precision * '1 day'::interval)))) THEN 'e2e074be-3b57-471f-b8c6-335b53e43edc'::uuid ELSE CASE WHEN (_status.name = 'Approved'::text) THEN '3e1cb498-8373-4fcc-9f44-ba12c69050e7'::uuid ELSE _qi.qualification_instance_approval_status_id END END) = status.id)
21. 1.146 2.860 ↑ 1.0 5,058 1 / 3

Sort (cost=675.89..688.53 rows=5,058 width=100) (actual time=7.611..8.581 rows=5,058 loops=1)

  • Sort Key: _qi.person_id, _qi.qualification_library_item_id, _qi.issue_date DESC
  • Worker 1: Sort Method: quicksort Memory: 904kB
22. 1.038 1.715 ↑ 1.0 5,058 1 / 3

Hash Join (cost=6.32..364.71 rows=5,058 width=100) (actual time=0.075..5.144 rows=5,058 loops=1)

  • Hash Cond: (_qi.qualification_library_item_id = _li.id)
23. 0.549 0.664 ↑ 1.0 5,058 1 / 3

Hash Join (cost=1.23..168.71 rows=5,058 width=112) (actual time=0.019..1.991 rows=5,058 loops=1)

  • Hash Cond: (_qi.qualification_instance_approval_status_id = _status.id)
24. 0.112 0.112 ↑ 1.0 5,058 1 / 3

Seq Scan on qualification_instances _qi (cost=0.00..148.58 rows=5,058 width=80) (actual time=0.002..0.337 rows=5,058 loops=1)

25. 0.002 0.003 ↑ 1.0 10 1 / 3

Hash (cost=1.10..1.10 rows=10 width=48) (actual time=0.008..0.008 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.001 0.001 ↑ 1.0 10 1 / 3

Seq Scan on qualification_instance_approval_statuses _status (cost=0.00..1.10 rows=10 width=48) (actual time=0.002..0.003 rows=10 loops=1)

27. 0.006 0.013 ↑ 1.0 93 1 / 3

Hash (cost=3.93..3.93 rows=93 width=20) (actual time=0.039..0.039 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
28. 0.007 0.007 ↑ 1.0 93 1 / 3

Seq Scan on qualification_library_items _li (cost=0.00..3.93 rows=93 width=20) (actual time=0.006..0.021 rows=93 loops=1)

29. 0.002 0.010 ↑ 1.0 10 1 / 3

Hash (cost=1.10..1.10 rows=10 width=16) (actual time=0.030..0.030 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.008 0.008 ↑ 1.0 10 1 / 3

Seq Scan on qualification_instance_approval_statuses status (cost=0.00..1.10 rows=10 width=16) (actual time=0.023..0.024 rows=10 loops=1)

31. 0.287 5.353 ↓ 241.6 4,348 1 / 3

Hash (cost=450.13..450.13 rows=18 width=48) (actual time=16.058..16.058 rows=4,348 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 404kB
32. 0.181 5.066 ↓ 241.6 4,348 1 / 3

Subquery Scan on _vcqi (cost=449.19..450.13 rows=18 width=48) (actual time=9.785..15.197 rows=4,348 loops=1)

33. 1.538 4.885 ↓ 241.6 4,348 1 / 3

GroupAggregate (cost=449.19..449.95 rows=18 width=80) (actual time=9.784..14.654 rows=4,348 loops=1)

  • Group Key: _qi_1.person_id, _qi_1.qualification_library_item_id
34. 0.858 3.347 ↓ 269.0 4,842 1 / 3

Sort (cost=449.19..449.23 rows=18 width=76) (actual time=9.772..10.041 rows=4,842 loops=1)

  • Sort Key: _qi_1.person_id, _qi_1.qualification_library_item_id, (CASE WHEN ((_qi_1.qualification_instance_approval_status_id = 'af4e9e71-1354-4e0f-b370-08874accb138'::uuid) AND ((_qi_1.expiry_date IS NULL) OR (_qi_1.expiry_date > CURRENT_TIMESTAMP))) THEN 2 WHEN (_qi_1.qualification_instance_approval_status_id = 'a45e9700-c84b-47c9-aa3b-4a87599c0eef'::uuid) THEN 1 WHEN ((_qi_1.qualification_instance_approval_status_id = 'af4e9e71-1354-4e0f-b370-08874accb138'::uuid) AND (_qi_1.expiry_date IS NOT NULL) AND (_qi_1.expiry_date <= CURRENT_TIMESTAMP)) THEN 1 ELSE 0 END) DESC, _qi_1.issue_date DESC
  • Worker 1: Sort Method: quicksort Memory: 873kB
35. 0.867 2.489 ↓ 269.0 4,842 1 / 3

Hash Join (cost=248.94..448.81 rows=18 width=76) (actual time=4.004..7.468 rows=4,842 loops=1)

  • Hash Cond: ((_qi_1.person_id = qi.person_id) AND (_qi_1.qualification_library_item_id = qi.qualification_library_item_id))
  • Join Filter: (((count(qi.id)) = 1) OR ((_qi_1.issue_date <= CURRENT_TIMESTAMP) AND ((_qi_1.qualification_instance_approval_status_id = 'af4e9e71-1354-4e0f-b370-08874accb138'::uuid) OR (_qi_1.qualification_instance_approval_status_id = 'a45e9700-c84b-47c9-aa3b-4a87599c0eef'::uuid))))
  • Rows Removed by Join Filter: 52
36. 0.297 0.297 ↑ 1.0 4,894 1 / 3

Seq Scan on qualification_instances _qi_1 (cost=0.00..173.87 rows=4,894 width=80) (actual time=0.011..0.891 rows=4,894 loops=1)

  • Filter: ((deleted_at IS NULL) OR (deleted_at > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 164
37. 0.264 1.325 ↓ 4.0 4,348 1 / 3

Hash (cost=232.50..232.50 rows=1,096 width=40) (actual time=3.975..3.975 rows=4,348 loops=1)

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 370kB
38. 0.757 1.061 ↓ 4.0 4,348 1 / 3

HashAggregate (cost=210.58..221.54 rows=1,096 width=40) (actual time=2.580..3.183 rows=4,348 loops=1)

  • Group Key: qi.person_id, qi.qualification_library_item_id
39. 0.304 0.304 ↑ 1.0 4,894 1 / 3

Seq Scan on qualification_instances qi (cost=0.00..173.87 rows=4,894 width=48) (actual time=0.002..0.912 rows=4,894 loops=1)

  • Filter: ((deleted_at IS NULL) OR (deleted_at > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 164
40. 4.348 4.348 ↑ 1.0 1 4,348 / 3

Index Scan using qualification_instances_pkey on qualification_instances qi_current (cost=0.28..0.39 rows=1 width=56) (actual time=0.003..0.003 rows=1 loops=4,348)

  • Index Cond: (id = _qi.id)
41. 1.449 1.449 ↑ 1.0 1 4,348 / 3

Index Scan using qualification_library_items_pkey on qualification_library_items qli_2 (cost=0.14..0.16 rows=1 width=59) (actual time=0.001..0.001 rows=1 loops=4,348)

  • Index Cond: (id = qi_current.qualification_library_item_id)
42. 1.449 1.449 ↑ 1.0 1 4,348 / 3

Index Only Scan using qualification_types_pkey on qualification_types qt_1 (cost=0.14..0.24 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=4,348)

  • Index Cond: (id = qli_2.qualification_type_id)
  • Heap Fetches: 0
43. 76.815 82.612 ↓ 52.0 52 4,348 / 3

GroupAggregate (cost=487.30..487.35 rows=1 width=80) (actual time=0.003..0.057 rows=52 loops=4,348)

  • Group Key: _qi_2.person_id, _qi_2.qualification_library_item_id
44. 4.482 5.797 ↓ 52.0 52 4,348 / 3

Sort (cost=487.30..487.31 rows=1 width=72) (actual time=0.001..0.004 rows=52 loops=4,348)

  • Sort Key: _qi_2.person_id, _qi_2.qualification_library_item_id, _qi_2.issue_date
  • Worker 1: Sort Method: quicksort Memory: 30kB
45. 0.033 1.315 ↓ 52.0 52 1 / 3

Hash Join (cost=233.40..487.29 rows=1 width=72) (actual time=2.809..3.945 rows=52 loops=1)

  • Hash Cond: ((_qi_2.person_id = _cqic.person_id) AND (_qi_2.qualification_library_item_id = _cqic.qualification_library_item_id))
46. 0.360 0.360 ↑ 1.1 689 1 / 3

Seq Scan on qualification_instances _qi_2 (cost=0.00..249.74 rows=791 width=56) (actual time=0.008..1.081 rows=689 loops=1)

  • Filter: (((deleted_at IS NULL) OR (deleted_at > CURRENT_TIMESTAMP)) AND ((qualification_instance_approval_status_id = '6746ca8c-5c00-414b-a8eb-853ef14ddc31'::uuid) OR (qualification_instance_approval_status_id = '24b42a72-58b5-47cd-90de-c8d38821ea02'::uuid) OR (qualification_instance_approval_status_id = '3c1a432a-c883-48e6-a1b4-9b42fcc6265f'::uuid) OR ((qualification_instance_approval_status_id = 'af4e9e71-1354-4e0f-b370-08874accb138'::uuid) AND (issue_date > CURRENT_TIMESTAMP))))
  • Rows Removed by Filter: 4,369
47. 0.024 0.922 ↓ 1.4 493 1 / 3

Hash (cost=227.93..227.93 rows=365 width=32) (actual time=2.765..2.765 rows=493 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
48. 0.019 0.897 ↓ 1.4 493 1 / 3

Subquery Scan on _cqic (cost=210.58..227.93 rows=365 width=32) (actual time=2.181..2.692 rows=493 loops=1)

49. 0.645 0.878 ↓ 1.4 493 1 / 3

HashAggregate (cost=210.58..224.28 rows=365 width=40) (actual time=2.180..2.635 rows=493 loops=1)

  • Group Key: qi_1.person_id, qi_1.qualification_library_item_id
  • Filter: (count(qi_1.id) > 1)
  • Rows Removed by Filter: 3,855
50. 0.233 0.233 ↑ 1.0 4,894 1 / 3

Seq Scan on qualification_instances qi_1 (cost=0.00..173.87 rows=4,894 width=48) (actual time=0.002..0.699 rows=4,894 loops=1)

  • Filter: ((deleted_at IS NULL) OR (deleted_at > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 164
51. 1,695.718 2,085.591 ↓ 20.0 5,058 4,348 / 3

Hash Join (cost=677.11..753.89 rows=253 width=32) (actual time=0.002..1.439 rows=5,058 loops=4,348)

  • Hash Cond: ((CASE WHEN ((_status_1.name = 'Approved'::text) AND (_qi_3.expiry_date IS NOT NULL) AND (_qi_3.expiry_date <= CURRENT_TIMESTAMP)) THEN '57cd960d-f260-4ce0-82ac-a86b5c29d932'::uuid WHEN ((_status_1.name = 'Approved'::text) AND (_qi_3.issue_date IS NOT NULL) AND (_qi_3.issue_date > CURRENT_TIMESTAMP)) THEN '42d2be32-5a19-466b-b927-59edf3635eae'::uuid WHEN ((_status_1.name = 'Approved'::text) AND (_qi_3.expiry_date IS NOT NULL) AND (_li_1.upcoming_expiry_warning_period > 0) AND (_qi_3.expiry_date <= (CURRENT_TIMESTAMP + ((_li_1.upcoming_expiry_warning_period)::double precision * '1 day'::interval)))) THEN 'e2e074be-3b57-471f-b8c6-335b53e43edc'::uuid ELSE CASE WHEN (_status_1.name = 'Approved'::text) THEN '3e1cb498-8373-4fcc-9f44-ba12c69050e7'::uuid ELSE _qi_3.qualification_instance_approval_status_id END END) = status_1.id)
52. 388.185 389.871 ↑ 1.0 5,058 4,348 / 3

Sort (cost=675.89..688.53 rows=5,058 width=100) (actual time=0.002..0.269 rows=5,058 loops=4,348)

  • Sort Key: _qi_3.person_id, _qi_3.qualification_library_item_id, _qi_3.issue_date DESC
  • Worker 1: Sort Method: quicksort Memory: 904kB
53. 1.027 1.686 ↑ 1.0 5,058 1 / 3

Hash Join (cost=6.32..364.71 rows=5,058 width=100) (actual time=0.066..5.058 rows=5,058 loops=1)

  • Hash Cond: (_qi_3.qualification_library_item_id = _li_1.id)
54. 0.537 0.649 ↑ 1.0 5,058 1 / 3

Hash Join (cost=1.23..168.71 rows=5,058 width=112) (actual time=0.022..1.946 rows=5,058 loops=1)

  • Hash Cond: (_qi_3.qualification_instance_approval_status_id = _status_1.id)
55. 0.109 0.109 ↑ 1.0 5,058 1 / 3

Seq Scan on qualification_instances _qi_3 (cost=0.00..148.58 rows=5,058 width=80) (actual time=0.003..0.328 rows=5,058 loops=1)

56. 0.002 0.003 ↑ 1.0 10 1 / 3

Hash (cost=1.10..1.10 rows=10 width=48) (actual time=0.008..0.008 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
57. 0.001 0.001 ↑ 1.0 10 1 / 3

Seq Scan on qualification_instance_approval_statuses _status_1 (cost=0.00..1.10 rows=10 width=48) (actual time=0.002..0.003 rows=10 loops=1)

58. 0.006 0.010 ↑ 1.0 93 1 / 3

Hash (cost=3.93..3.93 rows=93 width=20) (actual time=0.031..0.031 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
59. 0.004 0.004 ↑ 1.0 93 1 / 3

Seq Scan on qualification_library_items _li_1 (cost=0.00..3.93 rows=93 width=20) (actual time=0.002..0.013 rows=93 loops=1)

60. 0.001 0.002 ↑ 1.0 10 1 / 3

Hash (cost=1.10..1.10 rows=10 width=16) (actual time=0.005..0.005 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
61. 0.001 0.001 ↑ 1.0 10 1 / 3

Seq Scan on qualification_instance_approval_statuses status_1 (cost=0.00..1.10 rows=10 width=16) (actual time=0.002..0.003 rows=10 loops=1)

62. 0.292 0.292 ↑ 1.0 1 877 / 3

Index Only Scan using qualification_library_items_pkey on qualification_library_items qli_1 (cost=0.14..0.21 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=877)

  • Index Cond: (id = mq.qualification_library_item_id)
  • Heap Fetches: 0
63. 0.877 0.877 ↑ 1.9 50 877 / 3

Seq Scan on qualification_library_items qli (cost=0.00..3.93 rows=93 width=59) (actual time=0.001..0.003 rows=50 loops=877)

64. 0.292 0.292 ↑ 1.0 1 877 / 3

Index Scan using qualification_types_pkey on qualification_types qt (cost=0.14..0.28 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=877)

  • Index Cond: (id = qli.qualification_type_id)
65. 0.240 2,852.892 ↓ 343.5 4,809 1 / 3

Subquery Scan on view_group_compliance_overview (cost=2,555.70..2,679.61 rows=14 width=90) (actual time=8,536.373..8,558.675 rows=4,809 loops=1)

66. 4.445 2,852.652 ↓ 343.5 4,809 1 / 3

Hash Join (cost=2,555.70..2,679.47 rows=14 width=862) (actual time=8,536.372..8,557.955 rows=4,809 loops=1)

  • Hash Cond: (qli_3.qualification_type_id = qt_2.id)
67. 0.415 2,848.207 ↓ 343.5 4,809 1 / 3

Hash Join (cost=2,554.47..2,678.09 rows=14 width=107) (actual time=8,536.334..8,544.621 rows=4,809 loops=1)

  • Hash Cond: (mrgqli.qli_id = qli_3.id)
68. 0.462 2,847.792 ↓ 343.5 4,809 1 / 3

Hash Left Join (cost=2,549.38..2,672.96 rows=14 width=64) (actual time=8,536.266..8,543.377 rows=4,809 loops=1)

  • Hash Cond: ((jmrq.person_id = cq.person_id) AND (mrgqli.qli_id = cq.qualification_library_item_id))
69. 0.760 2.458 ↓ 343.5 4,809 1 / 3

Nested Loop (cost=98.87..222.33 rows=14 width=32) (actual time=1.642..7.374 rows=4,809 loops=1)

  • Join Filter: (mrg.id = mrgqli.mrg_id)
  • Rows Removed by Join Filter: 19,858
70. 0.004 0.004 ↑ 1.0 17 1 / 3

Seq Scan on mandatory_requirement_group_qualification_library_item mrgqli (cost=0.00..1.29 rows=17 width=32) (actual time=0.005..0.012 rows=17 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 12
71. 0.481 1.694 ↓ 362.8 1,451 17 / 3

Materialize (cost=98.87..220.03 rows=4 width=48) (actual time=0.081..0.299 rows=1,451 loops=17)

72. 0.335 1.213 ↓ 362.8 1,451 1 / 3

Nested Loop (cost=98.87..220.01 rows=4 width=48) (actual time=1.372..3.639 rows=1,451 loops=1)

73. 0.215 0.878 ↓ 362.8 1,451 1 / 3

Hash Join (cost=98.74..219.41 rows=4 width=32) (actual time=1.362..2.633 rows=1,451 loops=1)

  • Hash Cond: (jmrq.job_id = j_1.id)
  • Join Filter: (j_1.effective_dates && jmrq.effective_dates)
74. 0.226 0.226 ↓ 1.1 1,558 1 / 3

Seq Scan on job_mandatory_requirement_group jmrq (cost=0.00..116.78 rows=1,481 width=70) (actual time=0.035..0.679 rows=1,558 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((deleted_at IS NULL) OR (deleted_at > CURRENT_TIMESTAMP)) AND ((job_restricted_effective_dates IS NULL) OR (job_restricted_effective_dates @> CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 481
75. 0.129 0.436 ↑ 1.0 1,703 1 / 3

Hash (cost=76.78..76.78 rows=1,757 width=37) (actual time=1.308..1.308 rows=1,703 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 133kB
76. 0.307 0.307 ↑ 1.0 1,703 1 / 3

Seq Scan on jobs j_1 (cost=0.00..76.78 rows=1,757 width=37) (actual time=0.006..0.921 rows=1,703 loops=1)

  • Filter: ((id IS NULL) OR ((deleted_at IS NULL) AND (effective_dates @> CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 1,282
77. 0.000 0.000 ↑ 1.0 1 1,451 / 3

Index Only Scan using mandatory_requirement_groups_pkey on mandatory_requirement_groups mrg (cost=0.13..0.16 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1,451)

  • Index Cond: (id = jmrq.mrg_id)
  • Heap Fetches: 0
78. 0.388 2,844.873 ↓ 4,348.0 4,348 1 / 3

Hash (cost=2,450.50..2,450.50 rows=1 width=80) (actual time=8,534.617..8,534.618 rows=4,348 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 473kB
79. 0.267 2,844.485 ↓ 4,348.0 4,348 1 / 3

Subquery Scan on cq (cost=2,450.48..2,450.50 rows=1 width=80) (actual time=8,532.396..8,533.455 rows=4,348 loops=1)

80. 2.796 2,844.218 ↓ 4,348.0 4,348 1 / 3

Sort (cost=2,450.48..2,450.49 rows=1 width=451) (actual time=8,532.393..8,532.653 rows=4,348 loops=1)

  • Sort Key: qli_4.name, qi_current_1.issue_date DESC
  • Sort Method: quicksort Memory: 825kB
81. 2,730.086 2,841.421 ↓ 4,348.0 4,348 1 / 3

Nested Loop Left Join (cost=2,292.45..2,450.47 rows=1 width=451) (actual time=38.308..8,524.264 rows=4,348 loops=1)

  • Join Filter: (_qi_7.id = ((array_agg(_qi_6.id))[1]))
  • Rows Removed by Join Filter: 21,992,132
82. 90.818 111.335 ↓ 4,348.0 4,348 1 / 3

Nested Loop Left Join (cost=1,615.34..1,693.41 rows=1 width=115) (actual time=28.995..334.005 rows=4,348 loops=1)

  • Join Filter: ((((array_agg(_qi_6.person_id))[1]) = _vcqi_1.person_id) AND (((array_agg(_qi_6.qualification_library_item_id))[1]) = _vcqi_1.qualification_library_item_id))
  • Rows Removed by Join Filter: 226,044
83. 1.785 20.517 ↓ 4,348.0 4,348 1 / 3

Nested Loop (cost=1,128.03..1,206.04 rows=1 width=131) (actual time=24.845..61.552 rows=4,348 loops=1)

84. 2.338 18.732 ↓ 4,348.0 4,348 1 / 3

Nested Loop (cost=1,127.90..1,205.78 rows=1 width=147) (actual time=24.836..56.197 rows=4,348 loops=1)

85. 6.247 16.395 ↓ 4,348.0 4,348 1 / 3

Nested Loop (cost=1,127.76..1,205.62 rows=1 width=104) (actual time=24.814..49.184 rows=4,348 loops=1)

  • Join Filter: (_vcqi_1.id = qi_current_1.id)
86. 6.586 10.147 ↓ 4,348.0 4,348 1 / 3

Hash Join (cost=1,127.47..1,205.21 rows=1 width=80) (actual time=24.801..30.442 rows=4,348 loops=1)

  • Hash Cond: (_qi_4.id = _vcqi_1.id)
87. 0.654 3.561 ↓ 20.0 5,058 1 / 3

Hash Join (cost=677.11..753.89 rows=253 width=32) (actual time=7.765..10.684 rows=5,058 loops=1)

  • Hash Cond: ((CASE WHEN ((_status_2.name = 'Approved'::text) AND (_qi_4.expiry_date IS NOT NULL) AND (_qi_4.expiry_date <= CURRENT_TIMESTAMP)) THEN '57cd960d-f260-4ce0-82ac-a86b5c29d932'::uuid WHEN ((_status_2.name = 'Approved'::text) AND (_qi_4.issue_date IS NOT NULL) AND (_qi_4.issue_date > CURRENT_TIMESTAMP)) THEN '42d2be32-5a19-466b-b927-59edf3635eae'::uuid WHEN ((_status_2.name = 'Approved'::text) AND (_qi_4.expiry_date IS NOT NULL) AND (_li_2.upcoming_expiry_warning_period > 0) AND (_qi_4.expiry_date <= (CURRENT_TIMESTAMP + ((_li_2.upcoming_expiry_warning_period)::double precision * '1 day'::interval)))) THEN 'e2e074be-3b57-471f-b8c6-335b53e43edc'::uuid ELSE CASE WHEN (_status_2.name = 'Approved'::text) THEN '3e1cb498-8373-4fcc-9f44-ba12c69050e7'::uuid ELSE _qi_4.qualification_instance_approval_status_id END END) = status_2.id)
88. 1.130 2.904 ↑ 1.0 5,058 1 / 3

Sort (cost=675.89..688.53 rows=5,058 width=100) (actual time=7.746..8.711 rows=5,058 loops=1)

  • Sort Key: _qi_4.person_id, _qi_4.qualification_library_item_id, _qi_4.issue_date DESC
  • Sort Method: quicksort Memory: 904kB
89. 1.051 1.774 ↑ 1.0 5,058 1 / 3

Hash Join (cost=6.32..364.71 rows=5,058 width=100) (actual time=0.055..5.322 rows=5,058 loops=1)

  • Hash Cond: (_qi_4.qualification_library_item_id = _li_2.id)
90. 0.584 0.713 ↑ 1.0 5,058 1 / 3

Hash Join (cost=1.23..168.71 rows=5,058 width=112) (actual time=0.014..2.139 rows=5,058 loops=1)

  • Hash Cond: (_qi_4.qualification_instance_approval_status_id = _status_2.id)
91. 0.127 0.127 ↑ 1.0 5,058 1 / 3

Seq Scan on qualification_instances _qi_4 (cost=0.00..148.58 rows=5,058 width=80) (actual time=0.002..0.381 rows=5,058 loops=1)

92. 0.001 0.002 ↑ 1.0 10 1 / 3

Hash (cost=1.10..1.10 rows=10 width=48) (actual time=0.006..0.006 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
93. 0.001 0.001 ↑ 1.0 10 1 / 3

Seq Scan on qualification_instance_approval_statuses _status_2 (cost=0.00..1.10 rows=10 width=48) (actual time=0.002..0.003 rows=10 loops=1)

94. 0.004 0.010 ↑ 1.0 93 1 / 3

Hash (cost=3.93..3.93 rows=93 width=20) (actual time=0.029..0.029 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
95. 0.005 0.005 ↑ 1.0 93 1 / 3

Seq Scan on qualification_library_items _li_2 (cost=0.00..3.93 rows=93 width=20) (actual time=0.003..0.016 rows=93 loops=1)

96. 0.003 0.003 ↑ 1.0 10 1 / 3

Hash (cost=1.10..1.10 rows=10 width=16) (actual time=0.010..0.010 rows=10 loops=1)