explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ycS3

Settings
# exclusive inclusive rows x rows loops node
1. 297.244 662.113 ↓ 940.0 940 1

Nested Loop Left Join (cost=3,832.64..3,832.70 rows=1 width=344) (actual time=80.514..662.113 rows=940 loops=1)

  • Join Filter: ((v2shifts.location_id = v1shifts.locid) AND (v2shifts.shift_type_id = v1shifts.shtid) AND (v2shifts.shift_time_template_id = v1shifts.sttid) AND (v2shifts.start_time = v1shifts.dt))
  • Rows Removed by Join Filter: 696208
2.          

CTE requirements

3. 1.326 5.440 ↓ 849.0 849 1

Nested Loop (cost=1.14..132.61 rows=1 width=44) (actual time=0.032..5.440 rows=849 loops=1)

4. 1.567 1.567 ↓ 849.0 849 1

Index Scan using shift_requirement_shift_template_id_shift_type_id_idx on shift_requirement (cost=0.57..131.77 rows=1 width=24) (actual time=0.023..1.567 rows=849 loops=1)

  • Index Cond: ((shift_template_id = 9542) AND (shift_type_id = ANY ('{125859,125860,125861,125862,323412,125863,125864,323491,323515,323521,125866,217362,125868,125869,125871,125873,516857,516924,516991,323526,323531,125874,125876,125877,217340,323453,125878,125879,323455,125882,142851,691623,691624,691622,323586,142852,725149,323438,323626,517057,517123,125897,311529,125900,125901,125905,323474,125907,125908,125906,125904,125902,323551,323469,323490,125880,125883,125884,125896,125898,323656,323655,323470,125903,323654,323467,323441,125899,323472,323466,323652,323649,323647,125895,323471,125894,323640,323636,323637,323634,125893,323632,323631,323629,323617,323616,323614,323439,125892,125891,323605,323611,323609,323607,323603,323602,125890,323598,125889,323596,125888,323593,323463,323424,125887,125886,323591,323589,125885,323588,323585,323461,323459,323458,323457,323583,125881,323454,323451,323452,323582,323580,323449,323450,323578,323576,323574,323572,323570,323568,323567,323565,323564,323559,323561,323447,323446,323555,323553,323544,323543,323549,323547,323546,323541,323445,323443,323536,323535,323462,323410,323408,323407,323406,323533,125875,323529,323528,125872,323436,125870,323435,323524,323522,323428,323657,125867,323420,323417,323416,125865,323513,323511,323519,323508,323433,323495,323493,323414,323411,323403,323404,323489,323487,323486,323482,323481,323485,323484,323476,323479,323477}'::integer[])))
  • Filter: ((required_staff > '0'::double precision) AND (location_id = ANY ('{138551,138559,50605,50591,138552,50592,138553,50606,50593,138554,50594,138555,138556,138557,138558,65288,50595,50635,50596,46168,65289,50597,137937,50636,50598,50599,50610,89740}'::integer[])))
  • Rows Removed by Filter: 517
5. 2.547 2.547 ↑ 1.0 1 849

Index Only Scan using shift_time_id_stop_time_start_time_shift_time_template_id_idx on shift_time (cost=0.57..0.83 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=849)

  • Index Cond: ((id = shift_requirement.shift_time_id) AND (start_time >= '3004-01-01 00:00:00'::timestamp without time zone) AND (start_time <= '3004-01-21 23:59:59'::timestamp without time zone))
  • Filter: (shift_time_template_id = ANY ('{462992,463100,465706,465708,138618,138614,138663,465711,138703,465712,138682,463086,463107,138655,465698,463085,462991,464809,138683,465707,463087,465677,462995,465709,462993,138619,138616,138685,138684,138617}'::integer[]))
  • Heap Fetches: 0
6.          

CTE v1shifts

7. 2.009 63.829 ↓ 821.0 821 1

Nested Loop Left Join (cost=3.56..3,690.77 rows=1 width=98) (actual time=0.907..63.829 rows=821 loops=1)

8. 1.863 61.820 ↓ 821.0 821 1

Nested Loop Left Join (cost=3.14..3,690.31 rows=1 width=110) (actual time=0.897..61.820 rows=821 loops=1)

  • Filter: ((((pst.id IS NULL) AND (st.start_time >= '3004-01-01 00:00:00'::timestamp without time zone)) OR ((pst.id IS NOT NULL) AND (pst.start_time >= '3004-01-01 00:00:00'::timestamp without time zone))) AND (((pst.id IS NULL) AND (st.start_time < '3004-01-21 23:59:59'::timestamp without time zone)) OR ((pst.id IS NOT NULL) AND (pst.start_time < '3004-01-21 23:59:59'::timestamp without time zone))) AND ((st.shift_time_template_id = ANY ('{462992,463100,465706,465708,138618,138614,138663,465711,138703,465712,138682,463086,463107,138655,465698,463085,462991,464809,138683,465707,463087,465677,462995,465709,462993,138619,138616,138685,138684,138617}'::integer[])) OR ((pst.start_time >= '3004-01-01 00:00:00'::timestamp without time zone) AND (pst.start_time <= '3004-01-21 23:59:59'::timestamp without time zone) AND (NOT (psh.shift_template_id IS DISTINCT FROM 9542)) AND (psh.status_id = ANY ('{12,13,14}'::integer[])) AND (psh.location_id = ANY ('{138551,138559,50605,50591,138552,50592,138553,50606,50593,138554,50594,138555,138556,138557,138558,65288,50595,50635,50596,46168,65289,50597,137937,50636,50598,50599,50610,89740}'::integer[])) AND (psh.shift_type_id = ANY ('{125859,125860,125861,125862,323412,125863,125864,323491,323515,323521,125866,217362,125868,125869,125871,125873,516857,516924,516991,323526,323531,125874,125876,125877,217340,323453,125878,125879,323455,125882,142851,691623,691624,691622,323586,142852,725149,323438,323626,517057,517123,125897,311529,125900,125901,125905,323474,125907,125908,125906,125904,125902,323551,323469,323490,125880,125883,125884,125896,125898,323656,323655,323470,125903,323654,323467,323441,125899,323472,323466,323652,323649,323647,125895,323471,125894,323640,323636,323637,323634,125893,323632,323631,323629,323617,323616,323614,323439,125892,125891,323605,323611,323609,323607,323603,323602,125890,323598,125889,323596,125888,323593,323463,323424,125887,125886,323591,323589,125885,323588,323585,323461,323459,323458,323457,323583,125881,323454,323451,323452,323582,323580,323449,323450,323578,323576,323574,323572,323570,323568,323567,323565,323564,323559,323561,323447,323446,323555,323553,323544,323543,323549,323547,323546,323541,323445,323443,323536,323535,323462,323410,323408,323407,323406,323533,125875,323529,323528,125872,323436,125870,323435,323524,323522,323428,323657,125867,323420,323417,323416,125865,323513,323511,323519,323508,323433,323495,323493,323414,323411,323403,323404,323489,323487,323486,323482,323481,323485,323484,323476,323479,323477}'::integer[])) AND (pst.shift_time_template_id = ANY ('{462992,463100,465706,465708,138618,138614,138663,465711,138703,465712,138682,463086,463107,138655,465698,463085,462991,464809,138683,465707,463087,465677,462995,465709,462993,138619,138616,138685,138684,138617}'::integer[])))))
9. 1.407 59.957 ↓ 821.0 821 1

Nested Loop Left Join (cost=2.57..3,689.34 rows=1 width=118) (actual time=0.891..59.957 rows=821 loops=1)

10. 1.135 58.550 ↓ 821.0 821 1

Nested Loop Left Join (cost=2.00..3,688.54 rows=1 width=98) (actual time=0.887..58.550 rows=821 loops=1)

11. 1.427 54.952 ↓ 821.0 821 1

Nested Loop (cost=1.57..3,688.09 rows=1 width=82) (actual time=0.879..54.952 rows=821 loops=1)

12. 1.537 51.062 ↓ 821.0 821 1

Nested Loop (cost=1.14..3,687.43 rows=1 width=40) (actual time=0.867..51.062 rows=821 loops=1)

13. 47.062 47.062 ↓ 821.0 821 1

Index Scan using shift_location_id_shift_type_id_employee_id_idx on shift sh (cost=0.57..3,686.63 rows=1 width=24) (actual time=0.852..47.062 rows=821 loops=1)

  • Index Cond: ((location_id = ANY ('{138551,138559,50605,50591,138552,50592,138553,50606,50593,138554,50594,138555,138556,138557,138558,65288,50595,50635,50596,46168,65289,50597,137937,50636,50598,50599,50610,89740}'::integer[])) AND (shift_type_id = ANY ('{125859,125860,125861,125862,323412,125863,125864,323491,323515,323521,125866,217362,125868,125869,125871,125873,516857,516924,516991,323526,323531,125874,125876,125877,217340,323453,125878,125879,323455,125882,142851,691623,691624,691622,323586,142852,725149,323438,323626,517057,517123,125897,311529,125900,125901,125905,323474,125907,125908,125906,125904,125902,323551,323469,323490,125880,125883,125884,125896,125898,323656,323655,323470,125903,323654,323467,323441,125899,323472,323466,323652,323649,323647,125895,323471,125894,323640,323636,323637,323634,125893,323632,323631,323629,323617,323616,323614,323439,125892,125891,323605,323611,323609,323607,323603,323602,125890,323598,125889,323596,125888,323593,323463,323424,125887,125886,323591,323589,125885,323588,323585,323461,323459,323458,323457,323583,125881,323454,323451,323452,323582,323580,323449,323450,323578,323576,323574,323572,323570,323568,323567,323565,323564,323559,323561,323447,323446,323555,323553,323544,323543,323549,323547,323546,323541,323445,323443,323536,323535,323462,323410,323408,323407,323406,323533,125875,323529,323528,125872,323436,125870,323435,323524,323522,323428,323657,125867,323420,323417,323416,125865,323513,323511,323519,323508,323433,323495,323493,323414,323411,323403,323404,323489,323487,323486,323482,323481,323485,323484,323476,323479,323477}'::integer[])) AND (employee_id IS NOT NULL))
  • Filter: ((NOT (shift_template_id IS DISTINCT FROM 9542)) AND (status_id = 6))
  • Rows Removed by Filter: 62309
14. 2.463 2.463 ↑ 1.0 1 821

Index Only Scan using shift_time_id_stop_time_start_time_shift_time_template_id_idx on shift_time st (cost=0.57..0.79 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=821)

  • Index Cond: ((id = sh.shift_time_id) AND (start_time >= '3003-12-30 00:00:00'::timestamp without time zone) AND (start_time <= '3004-01-23 23:59:59'::timestamp without time zone))
  • Heap Fetches: 0
15. 2.463 2.463 ↑ 1.0 1 821

Index Scan using idx_tg_user_id_v2org_id on tg_user employee (cost=0.43..0.65 rows=1 width=46) (actual time=0.002..0.003 rows=1 loops=821)

  • Index Cond: (id = sh.employee_id)
16. 2.463 2.463 ↑ 1.0 1 821

Index Scan using shift_time_template_pkey on shift_time_template stt (cost=0.42..0.44 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=821)

  • Index Cond: (st.shift_time_template_id = id)
17. 0.000 0.000 ↓ 0.0 0 821

Index Scan using shift_pkey on shift psh (cost=0.57..0.79 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=821)

  • Index Cond: (sh.parent_shift_id = id)
18. 0.000 0.000 ↓ 0.0 0 821

Index Only Scan using shift_time_id_stop_time_start_time_shift_time_template_id_idx on shift_time pst (cost=0.57..0.59 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=821)

  • Index Cond: (id = psh.shift_time_id)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0 821

Index Scan using shift_time_template_pkey on shift_time_template pstt (cost=0.42..0.44 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=821)

  • Index Cond: (pst.shift_time_template_id = id)
20.          

CTE assignments

21. 1.182 66.169 ↓ 730.0 730 1

HashAggregate (cost=0.03..0.04 rows=1 width=28) (actual time=65.801..66.169 rows=730 loops=1)

  • Group Key: ugly.locid, ugly.shtid, ugly.sttid, ugly.dt
22. 64.987 64.987 ↓ 821.0 821 1

CTE Scan on v1shifts ugly (cost=0.00..0.02 rows=1 width=28) (actual time=0.910..64.987 rows=821 loops=1)

23.          

CTE message_counts

24. 0.001 12.461 ↓ 0.0 0 1

GroupAggregate (cost=8.53..8.56 rows=1 width=28) (actual time=12.461..12.461 rows=0 loops=1)

  • Group Key: sr.location_id, sr.shift_type_id, st_1.shift_time_template_id, st_1.start_time
25. 0.007 12.460 ↓ 0.0 0 1

Sort (cost=8.53..8.54 rows=1 width=228) (actual time=12.460..12.460 rows=0 loops=1)

  • Sort Key: sr.location_id, sr.shift_type_id, st_1.shift_time_template_id, st_1.start_time
  • Sort Method: quicksort Memory: 25kB
26. 0.001 12.453 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.04..8.52 rows=1 width=228) (actual time=12.453..12.453 rows=0 loops=1)

  • Filter: ((parent_message.alert_state_id IS NULL) OR (parent_message.alert_state_id <> 5))
27. 0.001 12.452 ↓ 0.0 0 1

Nested Loop (cost=2.47..7.92 rows=1 width=232) (actual time=12.452..12.452 rows=0 loops=1)

28. 0.001 12.451 ↓ 0.0 0 1

Nested Loop (cost=2.34..7.58 rows=2 width=236) (actual time=12.451..12.451 rows=0 loops=1)

29. 0.837 12.450 ↓ 0.0 0 1

Nested Loop (cost=1.13..1.69 rows=4 width=24) (actual time=12.450..12.450 rows=0 loops=1)

30. 1.066 9.915 ↓ 849.0 849 1

Nested Loop (cost=0.57..0.82 rows=1 width=24) (actual time=0.007..9.915 rows=849 loops=1)

31. 6.302 6.302 ↓ 849.0 849 1

CTE Scan on requirements sr (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..6.302 rows=849 loops=1)

32. 2.547 2.547 ↑ 1.0 1 849

Index Only Scan using shift_time_id_stop_time_start_time_shift_time_template_id_idx on shift_time st_1 (cost=0.57..0.79 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=849)

  • Index Cond: (id = sr.shift_time_id)
  • Heap Fetches: 0
33. 1.698 1.698 ↓ 0.0 0 849

Index Only Scan using v2shift_message_shift_requirement_id_message_id_idx on v2shift_message v2m (cost=0.56..0.83 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=849)

  • Index Cond: (shift_requirement_id = sr.id)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on message m (cost=1.20..1.44 rows=3 width=220) (never executed)

  • Recheck Cond: (((id = v2m.message_id) AND (alert_state_id <> 5)) OR (linked_message_id = v2m.message_id))
  • Filter: (alert_state_id <> 5)
35. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=1.20..1.20 rows=8 width=0) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on message_id_idx (cost=0.00..0.57 rows=1 width=0) (never executed)

  • Index Cond: (id = v2m.message_id)
37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on linked_message_id_idx (cost=0.00..0.63 rows=8 width=0) (never executed)

  • Index Cond: (linked_message_id = v2m.message_id)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using message_type_pkey on message_type m_t (cost=0.14..0.16 rows=1 width=16) (never executed)

  • Index Cond: (id = m.message_type_id)
  • Filter: ((description = ANY ('{""Drop Shift Request"",""Shift Request"",""Open Shift"",Calloff}'::text[])) OR (m.linked_message_id IS NOT NULL))
39. 0.000 0.000 ↓ 0.0 0

Index Scan using message_pkey on message parent_message (cost=0.57..0.59 rows=1 width=8) (never executed)

  • Index Cond: (m.linked_message_id = id)
40.          

CTE v2shifts

41. 1.100 83.814 ↓ 849.0 849 1

Nested Loop (cost=0.33..0.65 rows=1 width=42) (actual time=79.802..83.814 rows=849 loops=1)

42. 0.015 0.015 ↑ 1.0 1 1

Index Scan using v2organization_pkey on v2organization (cost=0.29..0.50 rows=1 width=16) (actual time=0.011..0.015 rows=1 loops=1)

  • Index Cond: (id = 3738)
43. 1.136 82.699 ↓ 849.0 849 1

Hash Full Join (cost=0.04..0.12 rows=1 width=60) (actual time=79.781..82.699 rows=849 loops=1)

  • Hash Cond: ((requirements.location_id = assignments.locid) AND (requirements.shift_type_id = assignments.shtid) AND (requirements.shift_time_template_id = assignments.sttid) AND (requirements.start_time = assignments.dt))
44. 1.110 14.293 ↓ 849.0 849 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=32) (actual time=12.499..14.293 rows=849 loops=1)

  • Join Filter: ((requirements.location_id = message_counts.location_id) AND (requirements.shift_type_id = message_counts.shift_type_id) AND (requirements.shift_time_template_id = message_counts.shift_time_template_id) AND (requirements.start_time = message_counts.start_time))
45. 0.448 0.448 ↓ 849.0 849 1

CTE Scan on requirements (cost=0.00..0.02 rows=1 width=24) (actual time=0.034..0.448 rows=849 loops=1)

46. 12.735 12.735 ↓ 0.0 0 849

CTE Scan on message_counts (cost=0.00..0.02 rows=1 width=28) (actual time=0.015..0.015 rows=0 loops=849)

47. 0.421 67.270 ↓ 730.0 730 1

Hash (cost=0.02..0.02 rows=1 width=28) (actual time=67.270..67.270 rows=730 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
48. 66.849 66.849 ↓ 730.0 730 1

CTE Scan on assignments (cost=0.00..0.02 rows=1 width=28) (actual time=65.804..66.849 rows=730 loops=1)

49. 84.699 84.699 ↓ 849.0 849 1

CTE Scan on v2shifts (cost=0.00..0.02 rows=1 width=42) (actual time=79.805..84.699 rows=849 loops=1)

50. 280.170 280.170 ↓ 821.0 821 849

CTE Scan on v1shifts (cost=0.00..0.02 rows=1 width=322) (actual time=0.000..0.330 rows=821 loops=849)

Planning time : 141.936 ms
Execution time : 662.979 ms