explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hof6

Settings
# exclusive inclusive rows x rows loops node
1. 0.794 22,623.250 ↓ 94.0 94 1

Sort (cost=47,457.59..47,457.60 rows=1 width=587) (actual time=22,623.246..22,623.250 rows=94 loops=1)

  • Sort Key: (("*SELECT* 1".user_id = 0)) DESC, "*SELECT* 1".start_time, uf.last_name
  • Sort Method: quicksort Memory: 73kB
2. 0.402 22,622.456 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,457.58 rows=1 width=587) (actual time=297.884..22,622.456 rows=94 loops=1)

  • Join Filter: (jif.group_id = jigf.id)
3. 0.423 22,622.054 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,428.23 rows=1 width=550) (actual time=297.878..22,622.054 rows=94 loops=1)

  • Join Filter: (jf.group_id = jgf.id)
  • Rows Removed by Join Filter: 94
4. 0.342 22,621.537 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,427.21 rows=1 width=543) (actual time=297.857..22,621.537 rows=94 loops=1)

  • Join Filter: (jf.department_id = jdf.id)
  • Rows Removed by Join Filter: 282
5. 0.291 22,621.101 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,425.71 rows=1 width=529) (actual time=297.851..22,621.101 rows=94 loops=1)

  • Join Filter: (jf.branch_id = jbf.id)
  • Rows Removed by Join Filter: 168
6. 0.468 22,620.716 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,424.64 rows=1 width=523) (actual time=297.842..22,620.716 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".job_item_id = jif.id)
  • Rows Removed by Join Filter: 1222
7. 2.311 22,620.154 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,423.35 rows=1 width=492) (actual time=297.835..22,620.154 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".job_id = jf.id)
  • Rows Removed by Join Filter: 25776
8. 0.476 22,615.587 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,366.23 rows=1 width=344) (actual time=297.700..22,615.587 rows=94 loops=1)

  • Join Filter: (uf.default_job_item_id = jifb.id)
  • Rows Removed by Join Filter: 1222
9. 2.737 22,614.923 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,364.94 rows=1 width=326) (actual time=297.692..22,614.923 rows=94 loops=1)

  • Join Filter: (uf.default_job_id = jfb.id)
  • Rows Removed by Join Filter: 23873
10. 0.907 22,609.272 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,307.81 rows=1 width=302) (actual time=297.480..22,609.272 rows=94 loops=1)

  • Join Filter: (uwf.id = (SubPlan 1))
  • Rows Removed by Join Filter: 1786
11. 0.783 22,599.247 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,280.78 rows=1 width=285) (actual time=297.331..22,599.247 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".pay_period_id = ppf.id)
  • Rows Removed by Join Filter: 4888
12. 0.518 22,597.242 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,278.26 rows=1 width=261) (actual time=297.311..22,597.242 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".absence_policy_id = apf.id)
  • Rows Removed by Join Filter: 1128
13. 0.378 22,596.536 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,276.99 rows=1 width=243) (actual time=297.301..22,596.536 rows=94 loops=1)

  • Join Filter: (uf.title_id = utf.id)
  • Rows Removed by Join Filter: 132
14. 0.373 22,596.064 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,275.91 rows=1 width=227) (actual time=297.294..22,596.064 rows=94 loops=1)

  • Join Filter: (uf.group_id = ugf.id)
  • Rows Removed by Join Filter: 282
15. 0.266 22,595.503 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,274.84 rows=1 width=188) (actual time=297.286..22,595.503 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".department_id = dfb.id)
16. 0.373 22,595.143 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,273.50 rows=1 width=174) (actual time=297.281..22,595.143 rows=94 loops=1)

  • Join Filter: (uf.default_department_id = df.id)
  • Rows Removed by Join Filter: 293
17. 0.301 22,594.488 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,271.95 rows=1 width=160) (actual time=297.270..22,594.488 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".branch_id = bfb.id)
  • Rows Removed by Join Filter: 190
18. 0.442 22,594.093 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,270.87 rows=1 width=154) (actual time=297.265..22,594.093 rows=94 loops=1)

  • Join Filter: (uf.default_branch_id = bf.id)
  • Rows Removed by Join Filter: 214
19. 11.777 22,593.369 ↓ 94.0 94 1

Nested Loop Left Join (cost=0.42..47,269.80 rows=1 width=148) (actual time=297.255..22,593.369 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".user_id = uf.id)
  • Rows Removed by Join Filter: 189634
  • Filter: ((uf.deleted IS NULL) OR (uf.deleted = 0))
20. 4,038.815 22,457.512 ↓ 94.0 94 1

Nested Loop Anti Join (cost=0.42..46,866.30 rows=1 width=98) (actual time=296.110..22,457.512 rows=94 loops=1)

  • Join Filter: ("*SELECT* 1".id = sf.replaced_id)
  • Rows Removed by Join Filter: 70401582
21. 0.055 43.953 ↓ 47.0 94 1

Append (cost=0.42..15,609.86 rows=2 width=94) (actual time=0.417..43.953 rows=94 loops=1)

22. 0.087 26.388 ↓ 94.0 94 1

Subquery Scan on *SELECT* 1 (cost=0.42..13,304.04 rows=1 width=79) (actual time=0.417..26.388 rows=94 loops=1)

23. 26.301 26.301 ↓ 94.0 94 1

Index Scan using schedule_department_id on schedule sf_1 (cost=0.42..13,304.03 rows=1 width=93) (actual time=0.416..26.301 rows=94 loops=1)

  • Index Cond: (department_id = 9)
  • Filter: ((start_time >= '2020-02-05 00:00:00+00'::timestamp with time zone) AND (start_time <= '2020-02-05 23:59:59+00'::timestamp with time zone) AND (company_id = 2) AND (deleted = 0))
  • Rows Removed by Filter: 56828
24. 0.000 17.510 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=395.60..2,305.81 rows=1 width=110) (actual time=17.510..17.510 rows=0 loops=1)

25. 0.001 17.510 ↓ 0.0 0 1

Nested Loop Left Join (cost=395.60..2,305.80 rows=1 width=124) (actual time=17.510..17.510 rows=0 loops=1)

  • Join Filter: ((rsf.date_stamp >= ppf_1.start_date) AND (rsf.date_stamp <= ppf_1.end_date))
26. 0.033 17.509 ↓ 0.0 0 1

Nested Loop Left Join (cost=395.46..2,305.00 rows=1 width=118) (actual time=17.509..17.509 rows=0 loops=1)

  • Filter: (sf_b.id IS NULL)
  • Rows Removed by Filter: 94
27. 0.075 16.912 ↓ 94.0 94 1

Nested Loop (cost=395.04..2,302.48 rows=1 width=118) (actual time=3.379..16.912 rows=94 loops=1)

28. 0.075 16.649 ↓ 94.0 94 1

Nested Loop Left Join (cost=394.76..2,302.14 rows=1 width=122) (actual time=3.361..16.649 rows=94 loops=1)

  • Filter: ((ppsf.deleted IS NULL) OR (ppsf.deleted = 0))
29. 0.445 16.104 ↓ 94.0 94 1

Hash Left Join (cost=394.35..2,301.62 rows=1 width=118) (actual time=3.318..16.104 rows=94 loops=1)

  • Hash Cond: (rsf.user_id = uf_b.id)
  • Filter: (((uf_b.deleted IS NULL) OR (uf_b.deleted = 0)) AND (CASE WHEN (rsf.department_id = '-1'::integer) THEN uf_b.default_department_id ELSE rsf.department_id END = 9) AND ((uf_b.hire_date IS NULL) OR (to_timestamp((uf_b.hire_date)::double precision) <= rsf.date_stamp)) AND ((uf_b.termination_date IS NULL) OR (to_timestamp((uf_b.termination_date)::double precision) >= rsf.date_stamp)))
  • Rows Removed by Filter: 1142
30. 12.532 12.532 ↓ 1.4 1,236 1

Seq Scan on recurring_schedule rsf (cost=0.00..1,904.98 rows=874 width=102) (actual time=0.040..12.532 rows=1,236 loops=1)

  • Filter: ((start_time >= '2020-02-05 00:00:00+00'::timestamp with time zone) AND (start_time <= '2020-02-05 23:59:59+00'::timestamp with time zone) AND (company_id = 2) AND (deleted = 0))
  • Rows Removed by Filter: 49169
31. 0.835 3.127 ↓ 1.0 3,680 1

Hash (cost=348.60..348.60 rows=3,660 width=30) (actual time=3.126..3.127 rows=3,680 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 247kB
32. 2.292 2.292 ↓ 1.0 3,680 1

Seq Scan on users uf_b (cost=0.00..348.60 rows=3,660 width=30) (actual time=0.005..2.292 rows=3,680 loops=1)

33. 0.094 0.470 ↑ 1.0 1 94

Nested Loop Left Join (cost=0.41..0.50 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=94)

34. 0.282 0.282 ↑ 1.0 1 94

Index Scan using pay_period_schedule_user_user_id on pay_period_schedule_user ppsuf (cost=0.28..0.35 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=94)

  • Index Cond: (rsf.user_id = user_id)
35. 0.094 0.094 ↑ 1.0 1 94

Index Scan using pay_period_schedule_id on pay_period_schedule ppsf (cost=0.13..0.15 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=94)

  • Index Cond: (ppsuf.pay_period_schedule_id = id)
36. 0.188 0.188 ↑ 1.0 1 94

Index Scan using recurring_schedule_control_id on recurring_schedule_control rscf (cost=0.28..0.34 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=94)

  • Index Cond: (id = rsf.recurring_schedule_control_id)
  • Filter: (deleted = 0)
37. 0.564 0.564 ↑ 1.0 1 94

Index Scan using schedule_user_id_user_date on schedule sf_b (cost=0.42..2.51 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=94)

  • Index Cond: ((user_id = rsf.user_id) AND (date_stamp >= '2020-02-04'::date) AND (date_stamp <= '2020-02-06'::date))
  • Filter: ((user_id <> 0) AND (deleted = 0) AND (((start_time >= rsf.start_time) AND (end_time <= rsf.end_time)) OR ((start_time >= rsf.start_time) AND (start_time < rsf.end_time)) OR ((end_time > rsf.start_time) AND (end_time <= rsf.end_time)) OR ((start_time <= rsf.start_time) AND (end_time >= rsf.end_time)) OR ((start_time = rsf.start_time) AND (end_time = rsf.end_time))))
  • Rows Removed by Filter: 1
38. 0.000 0.000 ↓ 0.0 0

Index Scan using pay_period_pay_period_schedule_id on pay_period ppf_1 (cost=0.14..0.49 rows=20 width=24) (never executed)

  • Index Cond: (pay_period_schedule_id = ppsuf.pay_period_schedule_id)
39. 18,374.744 18,374.744 ↓ 1.0 748,953 94

Seq Scan on schedule sf (cost=0.00..21,973.18 rows=742,654 width=8) (actual time=0.002..195.476 rows=748,953 loops=94)

  • Filter: (deleted = 0)
40. 124.080 124.080 ↑ 1.8 2,018 94

Seq Scan on users uf (cost=0.00..348.60 rows=3,660 width=56) (actual time=0.005..1.320 rows=2,018 loops=94)

41. 0.282 0.282 ↑ 1.0 3 94

Seq Scan on branch bf (cost=0.00..1.04 rows=3 width=10) (actual time=0.002..0.003 rows=3 loops=94)

  • Filter: (deleted = 0)
42. 0.094 0.094 ↑ 1.0 3 94

Seq Scan on branch bfb (cost=0.00..1.04 rows=3 width=10) (actual time=0.001..0.001 rows=3 loops=94)

  • Filter: (deleted = 0)
43. 0.282 0.282 ↑ 5.5 4 94

Seq Scan on department df (cost=0.00..1.27 rows=22 width=18) (actual time=0.002..0.003 rows=4 loops=94)

  • Filter: (deleted = 0)
44. 0.094 0.094 ↑ 1.0 1 94

Seq Scan on department dfb (cost=0.00..1.33 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=94)

  • Filter: ((id = 9) AND (deleted = 0))
  • Rows Removed by Filter: 3
45. 0.188 0.188 ↑ 1.0 3 94

Seq Scan on user_group ugf (cost=0.00..1.04 rows=3 width=43) (actual time=0.001..0.002 rows=3 loops=94)

  • Filter: (deleted = 0)
46. 0.094 0.094 ↑ 1.5 2 94

Seq Scan on user_title utf (cost=0.00..1.04 rows=3 width=20) (actual time=0.001..0.001 rows=2 loops=94)

  • Filter: (deleted = 0)
47. 0.188 0.188 ↑ 1.0 12 94

Seq Scan on absence_policy apf (cost=0.00..1.12 rows=12 width=22) (actual time=0.001..0.002 rows=12 loops=94)

48. 1.222 1.222 ↑ 1.2 53 94

Seq Scan on pay_period ppf (cost=0.00..1.76 rows=61 width=28) (actual time=0.005..0.013 rows=53 loops=94)

  • Filter: (deleted = 0)
49. 0.188 0.188 ↑ 1.0 19 94

Seq Scan on user_wage uwf (cost=0.00..1.19 rows=19 width=17) (actual time=0.001..0.002 rows=19 loops=94)

50.          

SubPlan (for Nested Loop Left Join)

51. 0.000 8.930 ↓ 0.0 0 1,786

Limit (cost=1.34..1.35 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1,786)

52. 3.572 8.930 ↓ 0.0 0 1,786

Sort (cost=1.34..1.35 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1,786)

  • Sort Key: z.effective_date DESC
  • Sort Method: quicksort Memory: 25kB
53. 5.358 5.358 ↓ 0.0 0 1,786

Seq Scan on user_wage z (cost=0.00..1.33 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,786)

  • Filter: ((effective_date <= "*SELECT* 1".date_stamp) AND (user_id = "*SELECT* 1".user_id) AND (deleted = 0))
  • Rows Removed by Filter: 19
54. 2.914 2.914 ↑ 4.6 255 94

Seq Scan on job jfb (cost=0.00..42.61 rows=1,161 width=32) (actual time=0.004..0.031 rows=255 loops=94)

55. 0.188 0.188 ↑ 1.0 13 94

Seq Scan on job_item jifb (cost=0.00..1.13 rows=13 width=26) (actual time=0.001..0.002 rows=13 loops=94)

56. 2.256 2.256 ↑ 4.2 275 94

Seq Scan on job jf (cost=0.00..42.61 rows=1,161 width=152) (actual time=0.001..0.024 rows=275 loops=94)

57. 0.094 0.094 ↑ 1.0 13 94

Seq Scan on job_item jif (cost=0.00..1.13 rows=13 width=35) (actual time=0.001..0.001 rows=13 loops=94)

58. 0.094 0.094 ↑ 1.0 3 94

Seq Scan on branch jbf (cost=0.00..1.03 rows=3 width=10) (actual time=0.000..0.001 rows=3 loops=94)

59. 0.094 0.094 ↑ 5.5 4 94

Seq Scan on department jdf (cost=0.00..1.22 rows=22 width=18) (actual time=0.001..0.001 rows=4 loops=94)

60. 0.094 0.094 ↑ 1.0 1 94

Seq Scan on job_group jgf (cost=0.00..1.01 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=94)

61. 0.000 0.000 ↓ 0.0 0 94

Seq Scan on job_item_group jigf (cost=0.00..18.60 rows=860 width=36) (actual time=0.000..0.000 rows=0 loops=94)

Planning time : 10.654 ms
Execution time : 22,623.998 ms