explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kNjG

Settings
# exclusive inclusive rows x rows loops node
1. 0.373 24,384.821 ↓ 221.0 221 1

Sort (cost=1,991,068.43..1,991,068.44 rows=1 width=896) (actual time=24,384.807..24,384.821 rows=221 loops=1)

  • Sort Key: ns.order_column, ns.employee_name
  • Sort Method: quicksort Memory: 62kB
2.          

CTE prior_schedule

3. 6.108 23,006.474 ↓ 505.0 505 1

GroupAggregate (cost=1,990,759.87..1,990,760.22 rows=1 width=128) (actual time=23,002.266..23,006.474 rows=505 loops=1)

  • Group Key: ti.start_date, ap.first_name, ap.last_name, ap.middle_name, ti.end_date, ti.end_time, ti.start_time, em.id, lo.id, ti.shift_type, ti.extra
4. 0.942 23,000.366 ↓ 505.0 505 1

Sort (cost=1,990,759.87..1,990,759.87 rows=1 width=68) (actual time=23,000.272..23,000.366 rows=505 loops=1)

  • Sort Key: ti.start_date, ap.first_name, ap.last_name, ap.middle_name, ti.end_date, ti.end_time, ti.start_time, em.id, ti.shift_type, ti.extra
  • Sort Method: quicksort Memory: 96kB
5. 4,746.571 22,999.424 ↓ 505.0 505 1

Hash Right Join (cost=12,262.07..1,990,759.86 rows=1 width=68) (actual time=22,552.989..22,999.424 rows=505 loops=1)

  • Hash Cond: (bt.timeblock_id = ti.id)
  • Join Filter: CASE WHEN ((date_part('timezone'::text, ('now'::cstring)::time with time zone) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'Y'::bpchar)) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-8'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END WHEN ((date_part('timezone'::text, ('now'::cstring)::time with time zone) / '3600'::double precision) = '-5'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-6'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-7'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-8'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END ELSE NULL::boolean END
  • Rows Removed by Join Filter: 280
6. 16,626.965 16,626.965 ↓ 1.0 48,033,588 1

Seq Scan on break_time bt (cost=0.00..1,798,779.40 rows=47,924,851 width=28) (actual time=0.070..16,626.965 rows=48,033,588 loops=1)

  • Filter: (paid = 'N'::bpchar)
  • Rows Removed by Filter: 14040159
7. 0.615 1,625.888 ↓ 505.0 505 1

Hash (cost=12,262.06..12,262.06 rows=1 width=82) (actual time=1,625.888..1,625.888 rows=505 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 78kB
8. 7.305 1,625.273 ↓ 505.0 505 1

Nested Loop Left Join (cost=11,999.16..12,262.06 rows=1 width=82) (actual time=216.994..1,625.273 rows=505 loops=1)

  • Filter: CASE WHEN ((date_part('timezone'::text, ('now'::cstring)::time with time zone) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'Y'::bpchar)) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-8'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END WHEN ((date_part('timezone'::text, ('now'::cstring)::time with time zone) / '3600'::double precision) = '-5'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-6'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-7'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-8'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END ELSE NULL::boolean END
  • Rows Removed by Filter: 656
9. 0.595 1,609.841 ↓ 1,161.0 1,161 1

Nested Loop (cost=11,998.73..12,259.41 rows=1 width=93) (actual time=216.844..1,609.841 rows=1,161 loops=1)

  • Join Filter: (em.user_id = ul.user_id)
10. 0.842 1,602.106 ↓ 1,190.0 1,190 1

Nested Loop (cost=11,998.30..12,257.28 rows=1 width=109) (actual time=216.811..1,602.106 rows=1,190 loops=1)

11. 18.417 1,595.314 ↓ 1,190.0 1,190 1

Nested Loop (cost=11,997.88..12,254.83 rows=1 width=86) (actual time=216.780..1,595.314 rows=1,190 loops=1)

12. 1.848 1,565.256 ↓ 1,663.0 1,663 1

Nested Loop (cost=11,997.45..12,194.26 rows=1 width=75) (actual time=209.279..1,565.256 rows=1,663 loops=1)

13. 0.983 1,558.419 ↓ 1,663.0 1,663 1

Nested Loop (cost=11,997.03..12,191.80 rows=1 width=83) (actual time=209.254..1,558.419 rows=1,663 loops=1)

14. 4.645 1,552.447 ↓ 1,663.0 1,663 1

Nested Loop (cost=11,996.74..12,189.48 rows=1 width=91) (actual time=209.230..1,552.447 rows=1,663 loops=1)

15. 0.931 1,539.487 ↓ 1,663.0 1,663 1

Nested Loop (cost=11,996.46..12,186.51 rows=1 width=107) (actual time=209.163..1,539.487 rows=1,663 loops=1)

16. 0.314 231.438 ↓ 1,663.0 1,663 1

Nested Loop (cost=11,996.03..12,184.93 rows=1 width=107) (actual time=208.526..231.438 rows=1,663 loops=1)

17. 1.302 224.472 ↓ 1,663.0 1,663 1

Nested Loop (cost=11,995.75..12,182.61 rows=1 width=94) (actual time=208.463..224.472 rows=1,663 loops=1)

18. 2.032 216.518 ↓ 1,663.0 1,663 1

Hash Join (cost=11,995.32..12,180.17 rows=1 width=94) (actual time=208.424..216.518 rows=1,663 loops=1)

  • Hash Cond: (ti.position_id = po.id)
19. 6.259 214.034 ↓ 9.2 1,663 1

Bitmap Heap Scan on timeblock ti (cost=11,980.86..12,165.03 rows=181 width=86) (actual time=207.953..214.034 rows=1,663 loops=1)

  • Recheck Cond: ((location_id = 114106) AND (start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
  • Heap Blocks: exact=597
20. 12.853 207.775 ↓ 0.0 0 1

BitmapAnd (cost=11,980.86..11,980.86 rows=181 width=0) (actual time=207.775..207.775 rows=0 loops=1)

21. 22.950 22.950 ↓ 7.3 148,558 1

Bitmap Index Scan on i_timeblock_loc (cost=0.00..209.26 rows=20,226 width=0) (actual time=22.950..22.950 rows=148,558 loops=1)

  • Index Cond: (location_id = 114106)
22. 171.972 171.972 ↑ 1.0 883,266 1

Bitmap Index Scan on i_timeblock_start_nodel (cost=0.00..11,771.26 rows=921,269 width=0) (actual time=171.972..171.972 rows=883,266 loops=1)

  • Index Cond: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
23. 0.011 0.452 ↓ 2.9 64 1

Hash (cost=14.18..14.18 rows=22 width=24) (actual time=0.452..0.452 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.441 0.441 ↓ 2.9 64 1

Index Scan using i_position_loc_id on "position" po (cost=0.42..14.18 rows=22 width=24) (actual time=0.063..0.441 rows=64 loops=1)

  • Index Cond: (location_id = 114106)
25. 6.652 6.652 ↑ 1.0 1 1,663

Index Scan using schedule_pkey on schedule sc (cost=0.43..2.43 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,663)

  • Index Cond: (id = ti.schedule_id)
26. 6.652 6.652 ↑ 1.0 1 1,663

Index Scan using idx_location_id_del on location lo (cost=0.29..2.31 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=1,663)

  • Index Cond: (id = 114106)
27. 1,307.118 1,307.118 ↑ 1.0 1 1,663

Index Scan using department_location_dep_idx on department_location de_lo (cost=0.42..1.57 rows=1 width=16) (actual time=0.289..0.786 rows=1 loops=1,663)

  • Index Cond: (department_id = sc.department_id)
  • Filter: ((location_id = 114106) AND (deleted = 'N'::bpchar))
  • Rows Removed by Filter: 1790
28. 8.315 8.315 ↑ 1.0 1 1,663

Index Only Scan using i_department_id on department dep (cost=0.29..2.96 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=1,663)

  • Index Cond: ((id = sc.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 0
29. 4.989 4.989 ↑ 1.0 1 1,663

Index Scan using corporation_pkey on corporation co (cost=0.29..2.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,663)

  • Index Cond: (id = lo.corporation_id)
  • Filter: (deleted = 'N'::bpchar)
30. 4.989 4.989 ↑ 1.0 1 1,663

Index Scan using position_code_pkey on position_code po_code (cost=0.42..2.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,663)

  • Index Cond: (id = po.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
31. 11.641 11.641 ↑ 1.0 1 1,663

Index Scan using idx_employee_id_del on employee em (cost=0.42..60.56 rows=1 width=19) (actual time=0.005..0.007 rows=1 loops=1,663)

  • Index Cond: ((id = ti.employee_id) AND (id = ANY ('{91672447,24137827,94214053,14294648,95083521,19048609,92146306,88003096,94514269,87697352,17971146,92500768,92801207,87906652,92170354,94185669,19043187,20739895,19043387,94224220,93795134,19043185,17971287,92097293,92254685,93417831,94987951,44372285,19041151,85628241,47686780,86173098,95084812,91849701,92848760,92426540,91401606,92146308,94289481,88468242,19041160,50540711,20739810,47686729,89828632,94224219,19041161,94460520,19041136,19041153,19042027,72165658,88123553,19041138,19041146,93185582,49231582,19041148,19041169,95084821,93994593,63331863,95074069,85832697,86248146,87105440,90921896,19041150,19041145,91556927,91024699,94991158,92588221,19045651,21175130,19041195,19041894,93698765,19041816,88123552,95019242,92146307,87553971,95083436,90683166,19045627,95074677,19177944,19041193,94877063,93240899,47686730,88106509,88498584,93163779,19460265,19829476,19045632,95034204,94948427,19041173,93071175,93473045,95083987,94264083,95083520,92027616}'::bigint[])))
32. 5.950 5.950 ↑ 1.0 1 1,190

Index Scan using i_au_id_del on app_user ap (cost=0.42..2.44 rows=1 width=23) (actual time=0.004..0.005 rows=1 loops=1,190)

  • Index Cond: (id = em.user_id)
33. 7.140 7.140 ↑ 1.0 1 1,190

Index Scan using user_loc_user_id on user_location ul (cost=0.42..2.12 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=1,190)

  • Index Cond: (user_id = ap.id)
  • Filter: (((termination_date IS NULL) OR (termination_date >= '2020-02-08 00:00:00'::timestamp without time zone)) AND (location_id = 114106))
  • Rows Removed by Filter: 0
34. 8.127 8.127 ↑ 1.0 1 1,161

Index Scan using timezone_postal_idx on timezone tz (cost=0.43..2.45 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=1,161)

  • Index Cond: ((postal_code)::text = split_part((lo.zip)::text, '-'::text, 1))
35.          

CTE final

36. 9.499 24,383.031 ↓ 666.0 666 1

GroupAggregate (cost=307.68..308.17 rows=1 width=1,518) (actual time=24,373.763..24,383.031 rows=666 loops=1)

  • Group Key: em_1.payroll_id, ti_1.start_date, ap_1.first_name, ap_1.last_name, ap_1.middle_name, ps.schedule_duration, ps.start_date, ps.start_time, ps.end_time, ti_1.end_date, ti_1.end_time, ti_1.start_time, ti_1.shift_type, ti_3.shift_type, ss.pickedup, ps.payroll_id, ps.ename, ti_1.extra, ps.extra
37. 1.553 24,373.532 ↓ 666.0 666 1

Sort (cost=307.68..307.68 rows=1 width=666) (actual time=24,373.434..24,373.532 rows=666 loops=1)

  • Sort Key: em_1.payroll_id, ti_1.start_date, ap_1.first_name, ap_1.last_name, ap_1.middle_name, ps.schedule_duration, ps.start_date, ps.start_time, ps.end_time, ti_1.end_date, ti_1.end_time, ti_1.start_time, ti_1.shift_type, ti_3.shift_type, ss.pickedup, ps.payroll_id, ps.ename, ti_1.extra, ps.extra
  • Sort Method: quicksort Memory: 180kB
38. 1.168 24,371.979 ↓ 666.0 666 1

Hash Full Join (cost=124.14..307.67 rows=1 width=666) (actual time=23,025.672..24,371.979 rows=666 loops=1)

  • Hash Cond: ((ti_1.start_date = ps.start_date) AND (lo_1.id = ps.location_id) AND (ti_1.employee_id = ps.employee_id))
  • Filter: (COALESCE(sc_1.deleted, 'N'::bpchar) = 'N'::bpchar)
39. 0.587 1,363.729 ↓ 657.0 657 1

Nested Loop Left Join (cost=124.10..307.61 rows=1 width=76) (actual time=18.552..1,363.729 rows=657 loops=1)

40. 0.610 1,350.002 ↓ 657.0 657 1

Nested Loop Left Join (cost=122.82..300.24 rows=1 width=80) (actual time=18.489..1,350.002 rows=657 loops=1)

41. 0.795 1,343.479 ↓ 657.0 657 1

Nested Loop (cost=122.25..296.93 rows=1 width=84) (actual time=18.265..1,343.479 rows=657 loops=1)

  • Join Filter: (em_1.user_id = ul_1.user_id)
42. 1.117 1,340.028 ↓ 664.0 664 1

Nested Loop (cost=121.83..294.79 rows=1 width=100) (actual time=18.197..1,340.028 rows=664 loops=1)

43. 0.624 1,336.919 ↓ 664.0 664 1

Nested Loop Left Join (cost=121.40..292.34 rows=1 width=77) (actual time=18.146..1,336.919 rows=664 loops=1)

44. 0.302 1,333.639 ↓ 664.0 664 1

Nested Loop (cost=120.12..284.94 rows=1 width=75) (actual time=18.046..1,333.639 rows=664 loops=1)

45. 0.566 1,330.681 ↓ 664.0 664 1

Nested Loop (cost=119.70..282.48 rows=1 width=83) (actual time=17.995..1,330.681 rows=664 loops=1)

46. 2.151 1,327.459 ↓ 664.0 664 1

Nested Loop (cost=119.28..280.07 rows=1 width=83) (actual time=17.914..1,327.459 rows=664 loops=1)

47. 0.797 1,321.988 ↓ 664.0 664 1

Nested Loop (cost=118.99..277.10 rows=1 width=99) (actual time=17.855..1,321.988 rows=664 loops=1)

48. 0.393 785.343 ↓ 664.0 664 1

Nested Loop (cost=118.57..275.52 rows=1 width=99) (actual time=17.002..785.343 rows=664 loops=1)

49. 0.648 782.294 ↓ 664.0 664 1

Nested Loop (cost=118.28..273.20 rows=1 width=91) (actual time=16.933..782.294 rows=664 loops=1)

50. 0.948 778.326 ↓ 664.0 664 1

Nested Loop (cost=117.85..270.75 rows=1 width=89) (actual time=16.882..778.326 rows=664 loops=1)

51. 1.200 1.200 ↓ 107.0 107 1

Index Scan using idx_employee_id_del on employee em_1 (cost=0.42..152.29 rows=1 width=19) (actual time=0.041..1.200 rows=107 loops=1)

  • Index Cond: (id = ANY ('{91672447,24137827,94214053,14294648,95083521,19048609,92146306,88003096,94514269,87697352,17971146,92500768,92801207,87906652,92170354,94185669,19043187,20739895,19043387,94224220,93795134,19043185,17971287,92097293,92254685,93417831,94987951,44372285,19041151,85628241,47686780,86173098,95084812,91849701,92848760,92426540,91401606,92146308,94289481,88468242,19041160,50540711,20739810,47686729,89828632,94224219,19041161,94460520,19041136,19041153,19042027,72165658,88123553,19041138,19041146,93185582,49231582,19041148,19041169,95084821,93994593,63331863,95074069,85832697,86248146,87105440,90921896,19041150,19041145,91556927,91024699,94991158,92588221,19045651,21175130,19041195,19041894,93698765,19041816,88123552,95019242,92146307,87553971,95083436,90683166,19045627,95074677,19177944,19041193,94877063,93240899,47686730,88106509,88498584,93163779,19460265,19829476,19045632,95034204,94948427,19041173,93071175,93473045,95083987,94264083,95083520,92027616}'::bigint[]))
52. 69.764 776.178 ↓ 6.0 6 107

Bitmap Heap Scan on timeblock ti_1 (cost=117.43..118.45 rows=1 width=78) (actual time=7.235..7.254 rows=6 loops=107)

  • Recheck Cond: ((employee_id = em_1.id) AND (location_id = 114106) AND (deleted = 'N'::bpchar))
  • Filter: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
  • Rows Removed by Filter: 282
  • Heap Blocks: exact=19660
53. 5.243 706.414 ↓ 0.0 0 107

BitmapAnd (cost=117.43..117.43 rows=1 width=0) (actual time=6.602..6.602 rows=0 loops=107)

54. 26.322 26.322 ↓ 1.1 663 107

Bitmap Index Scan on i_timeblock_emp (cost=0.00..6.96 rows=585 width=0) (actual time=0.246..0.246 rows=663 loops=107)

  • Index Cond: (employee_id = em_1.id)
55. 674.849 674.849 ↓ 6.1 64,356 107

Bitmap Index Scan on i_timeblock_location (cost=0.00..110.20 rows=10,618 width=0) (actual time=6.307..6.307 rows=64,356 loops=107)

  • Index Cond: (location_id = 114106)
56. 3.320 3.320 ↑ 1.0 1 664

Index Scan using schedule_pkey on schedule sc_1 (cost=0.43..2.45 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=664)

  • Index Cond: (id = ti_1.schedule_id)
57. 2.656 2.656 ↑ 1.0 1 664

Index Only Scan using idx_location_id_del on location lo_1 (cost=0.29..2.31 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=664)

  • Index Cond: (id = 114106)
  • Heap Fetches: 664
58. 535.848 535.848 ↑ 1.0 1 664

Index Scan using department_location_dep_idx on department_location de_lo_1 (cost=0.42..1.57 rows=1 width=16) (actual time=0.328..0.807 rows=1 loops=664)

  • Index Cond: (department_id = sc_1.department_id)
  • Filter: ((location_id = 114106) AND (deleted = 'N'::bpchar))
  • Rows Removed by Filter: 1772
59. 3.320 3.320 ↑ 1.0 1 664

Index Only Scan using i_department_id on department dep_1 (cost=0.29..2.96 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=664)

  • Index Cond: ((id = sc_1.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 0
60. 2.656 2.656 ↑ 1.0 1 664

Index Scan using i_position_id on "position" po_1 (cost=0.42..2.40 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=664)

  • Index Cond: (id = ti_1.position_id)
  • Filter: (location_id = 114106)
61. 2.656 2.656 ↑ 1.0 1 664

Index Scan using position_code_pkey on position_code po_code_1 (cost=0.42..2.44 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=664)

  • Index Cond: (id = po_1.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
62. 0.000 2.656 ↓ 0.0 0 664

Nested Loop (cost=1.28..7.40 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=664)

63. 0.664 2.656 ↓ 0.0 0 664

Nested Loop (cost=0.99..5.08 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=664)

64. 1.992 1.992 ↓ 0.0 0 664

Index Scan using idx_shift_swap on shift_swap ss (cost=0.42..2.48 rows=1 width=18) (actual time=0.003..0.003 rows=0 loops=664)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: ((location_id = 114106) AND (pickedup = 'Y'::bpchar))
  • Rows Removed by Filter: 0
65. 0.000 0.000 ↓ 0.0 0

Index Scan using timeblock_pkey on timeblock ti_2 (cost=0.57..2.59 rows=1 width=8) (never executed)

  • Index Cond: (id = ss.timeblock_id)
  • Filter: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_location_id_del on location lo_2 (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: (id = 114106)
  • Heap Fetches: 0
67. 1.992 1.992 ↑ 1.0 1 664

Index Scan using i_au_id_del on app_user ap_1 (cost=0.42..2.44 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=664)

  • Index Cond: (id = em_1.user_id)
68. 2.656 2.656 ↑ 1.0 1 664

Index Scan using user_loc_user_id on user_location ul_1 (cost=0.42..2.12 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=664)

  • Index Cond: (user_id = ap_1.id)
  • Filter: (((termination_date IS NULL) OR (termination_date >= '2020-02-08 00:00:00'::timestamp without time zone)) AND (location_id = 114106))
  • Rows Removed by Filter: 0
69. 5.913 5.913 ↑ 1.0 1 657

Index Scan using i_breaktime_time_id on break_time bt_1 (cost=0.56..3.31 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=657)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: (COALESCE(paid, 'N'::bpchar) = 'N'::bpchar)
70. 0.327 13.140 ↑ 1.0 1 657

Nested Loop (cost=1.28..7.35 rows=1 width=12) (actual time=0.020..0.020 rows=1 loops=657)

71. 0.326 11.826 ↑ 1.0 1 657

Nested Loop (cost=0.99..5.04 rows=1 width=20) (actual time=0.018..0.018 rows=1 loops=657)

72. 9.855 9.855 ↑ 1.0 1 657

Index Scan using timeblock_pkey on timeblock ti_3 (cost=0.57..2.59 rows=1 width=28) (actual time=0.015..0.015 rows=1 loops=657)

  • Index Cond: (id = ti_1.parent_id)
  • Filter: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date) AND (location_id = 114106))
73. 1.645 1.645 ↑ 1.0 1 329

Index Scan using i_schedule_id on schedule sc_2 (cost=0.43..2.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=329)

  • Index Cond: (id = ti_3.schedule_id)
  • Filter: (department_id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::bigint[]))
74. 0.987 0.987 ↑ 1.0 1 329

Index Only Scan using idx_location_id_del on location lo_3 (cost=0.29..2.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=329)

  • Index Cond: (id = 114106)
  • Heap Fetches: 329
75. 0.223 23,007.082 ↓ 505.0 505 1

Hash (cost=0.02..0.02 rows=1 width=624) (actual time=23,007.082..23,007.082 rows=505 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
76. 23,006.859 23,006.859 ↓ 505.0 505 1

CTE Scan on prior_schedule ps (cost=0.00..0.02 rows=1 width=624) (actual time=23,002.281..23,006.859 rows=505 loops=1)

77. 24,384.448 24,384.448 ↓ 221.0 221 1

CTE Scan on final ns (cost=0.00..0.03 rows=1 width=896) (actual time=24,373.805..24,384.448 rows=221 loops=1)

  • Filter: ((COALESCE(schedule_duration_14_days_prior, '0'::numeric) <> COALESCE(schedule_duration, '0'::numeric)) OR (COALESCE(schedule_start_time_14_days_prior, ' '::text) <> COALESCE(schedule_start_time, ' '::text)) OR (COALESCE(schedule_end_time_14_days_prior, ' '::text) <> COALESCE(schedule_end_time, ' '::text)) OR (COALESCE(schdeule_reg_or_on_call_14_days_prior, ' '::text) <> COALESCE(schdeule_reg_or_on_call, ' '::text)))
  • Rows Removed by Filter: 445
Planning time : 75.528 ms
Execution time : 24,387.166 ms