explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ulhf : Optimization for: plan #4LxmF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,486.647 4,486.647 ↓ 4,986.0 4,986 1

CTE Scan on timeblocks (cost=1,109.08..1,109.10 rows=1 width=728) (actual time=69.149..4,486.647 rows=4,986 loops=1)

2.          

CTE timeblocks

3. 36.191 4,477.164 ↓ 4,986.0 4,986 1

Nested Loop (cost=1,016.52..1,109.08 rows=1 width=154) (actual time=69.143..4,477.164 rows=4,986 loops=1)

  • Join Filter: (tb.location_id = ul.location_id)
  • Rows Removed by Join Filter: 295
4. 8.782 4,420.277 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,016.09..1,085.46 rows=1 width=177) (actual time=68.517..4,420.277 rows=5,174 loops=1)

5. 4.551 4,395.973 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,015.67..1,083.02 rows=1 width=154) (actual time=68.483..4,395.973 rows=5,174 loops=1)

6. 2.458 4,375.900 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,015.24..1,080.58 rows=1 width=162) (actual time=68.446..4,375.900 rows=5,174 loops=1)

7. 6.119 4,357.920 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,014.95..1,078.28 rows=1 width=170) (actual time=68.422..4,357.920 rows=5,174 loops=1)

8. 257.663 4,336.279 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,014.67..1,076.01 rows=1 width=186) (actual time=68.297..4,336.279 rows=5,174 loops=1)

  • Join Filter: (tb.location_id = de_lo.location_id)
  • Rows Removed by Join Filter: 3266714
9. 6.978 260.204 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,014.25..1,037.45 rows=1 width=170) (actual time=68.246..260.204 rows=5,174 loops=1)

  • Join Filter: (tb.location_id = lo.id)
10. 5.083 242.878 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,013.96..1,035.15 rows=1 width=149) (actual time=68.218..242.878 rows=5,174 loops=1)

11. 8.283 222.273 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,013.54..1,032.70 rows=1 width=133) (actual time=68.179..222.273 rows=5,174 loops=1)

12. 8.156 198.468 ↓ 5,174.0 5,174 1

Nested Loop (cost=1,013.11..1,030.25 rows=1 width=122) (actual time=68.137..198.468 rows=5,174 loops=1)

13. 121.870 169.616 ↓ 5,174.0 5,174 1

Bitmap Heap Scan on timeblock tb (cost=1,012.68..1,027.80 rows=1 width=114) (actual time=68.071..169.616 rows=5,174 loops=1)

  • Recheck Cond: ((location_id = ANY ('{114106}'::bigint[])) AND (employee_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,91849701,92848760,92426540,91401606,92146308,95084837,94289481,88468242,19041160,50540711,20739810,47686729,89828632,94224219,19041161,94460520,19041136,19041153,19042027,72165658,88123553,19041138,19041146,93185582,49231582,19041148,94213967,19041169,93994593,63331863,95074069,85832697,86248146,87105440,90921896,19041150,19041145,91556927,91024699,94991158,92588221,19045651,21175130,95087352,19041195,19041894,93698765,19041816,95089691,88123552,95019242,92146307,87553971,95083436,90683166,19045627,95074677,19177944,19041193,94877063,93240899,47686730,88498584,93163779,19460265,95084836,19829476,19045632,95034204,94948427,19041173,93071175,95083987,94264083,95083520,92027616,32068517,94811810,88391754,93163748,93846824,94761944,88106509,94214052,93473045}'::bigint[])))
  • Filter: ((start_date >= '2019-12-01'::date) AND (start_date <= '2020-03-01'::date))
  • Rows Removed by Filter: 48348
  • Heap Blocks: exact=24919
14. 5.798 47.746 ↓ 0.0 0 1

BitmapAnd (cost=1,012.68..1,012.68 rows=13 width=0) (actual time=47.746..47.746 rows=0 loops=1)

15. 28.408 28.408 ↓ 7.4 148,268 1

Bitmap Index Scan on i_timeblock_loc (cost=0.00..205.72 rows=20,020 width=0) (actual time=28.408..28.408 rows=148,268 loops=1)

  • Index Cond: (location_id = ANY ('{114106}'::bigint[]))
16. 13.540 13.540 ↓ 1.1 72,910 1

Bitmap Index Scan on i_timeblock_emp (cost=0.00..806.72 rows=67,551 width=0) (actual time=13.540..13.540 rows=72,910 loops=1)

  • Index Cond: (employee_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,91849701,92848760,92426540,91401606,92146308,95084837,94289481,88468242,19041160,50540711,20739810,47686729,89828632,94224219,19041161,94460520,19041136,19041153,19042027,72165658,88123553,19041138,19041146,93185582,49231582,19041148,94213967,19041169,93994593,63331863,95074069,85832697,86248146,87105440,90921896,19041150,19041145,91556927,91024699,94991158,92588221,19045651,21175130,95087352,19041195,19041894,93698765,19041816,95089691,88123552,95019242,92146307,87553971,95083436,90683166,19045627,95074677,19177944,19041193,94877063,93240899,47686730,88498584,93163779,19460265,95084836,19829476,19045632,95034204,94948427,19041173,93071175,95083987,94264083,95083520,92027616,32068517,94811810,88391754,93163748,93846824,94761944,88106509,94214052,93473045}'::bigint[]))
17. 20.696 20.696 ↑ 1.0 1 5,174

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

  • Index Cond: (id = tb.schedule_id)
  • Filter: (department_id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::bigint[]))
18. 15.522 15.522 ↑ 1.0 1 5,174

Index Scan using idx_employee_id_del on employee em (cost=0.42..2.44 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=5,174)

  • Index Cond: (id = tb.employee_id)
19. 15.522 15.522 ↑ 1.0 1 5,174

Index Scan using i_position_id on "position" po (cost=0.42..2.44 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=5,174)

  • Index Cond: (id = tb.position_id)
  • Filter: (tb.location_id = location_id)
20. 10.348 10.348 ↑ 1.0 1 5,174

Index Scan using idx_location_id_del on location lo (cost=0.29..2.29 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=5,174)

  • Index Cond: (id = po.location_id)
21. 3,818.412 3,818.412 ↓ 23.4 632 5,174

Index Scan using department_location_dep_idx on department_location de_lo (cost=0.42..38.22 rows=27 width=16) (actual time=0.010..0.738 rows=632 loops=5,174)

  • Index Cond: (department_id = sc.department_id)
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 1089
22. 15.522 15.522 ↑ 1.0 1 5,174

Index Only Scan using i_department_id on department dep (cost=0.29..2.27 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5,174)

  • Index Cond: (id = sc.department_id)
  • Heap Fetches: 5174
23. 15.522 15.522 ↑ 1.0 1 5,174

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

  • Index Cond: (id = lo.corporation_id)
  • Filter: (deleted = 'N'::bpchar)
24. 15.522 15.522 ↑ 1.0 1 5,174

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=5,174)

  • Index Cond: (id = po.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
25. 15.522 15.522 ↑ 1.0 1 5,174

Index Scan using i_au_id_del on app_user ap (cost=0.42..2.44 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=5,174)

  • Index Cond: (id = em.user_id)
26. 20.696 20.696 ↑ 22.0 1 5,174

Index Scan using user_loc_user_id on user_location ul (cost=0.42..23.09 rows=22 width=16) (actual time=0.004..0.004 rows=1 loops=5,174)

  • Index Cond: (user_id = em.user_id)
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-03-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 0
Planning time : 56.510 ms
Execution time : 4,490.150 ms