explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KTWb

Settings
# exclusive inclusive rows x rows loops node
1. 0.192 2,918.535 ↑ 1.0 2,400 1

Limit (cost=2,417,496.03..2,417,610.03 rows=2,400 width=112) (actual time=2,916.844..2,918.535 rows=2,400 loops=1)

2. 1.145 2,918.343 ↑ 31.1 2,400 1

Unique (cost=2,417,496.03..2,421,045.52 rows=74,726 width=112) (actual time=2,916.843..2,918.343 rows=2,400 loops=1)

3. 114.796 2,917.198 ↑ 31.1 2,400 1

Sort (cost=2,417,496.03..2,417,682.85 rows=74,726 width=112) (actual time=2,916.842..2,917.198 rows=2,400 loops=1)

  • Sort Key: core_workreport.id DESC, core_workreport.start_time, core_workreport.end_time, core_employee.name, core_workcategory.hue, core_workreport.amount, core_workreport.start_date, core_worktype.name, core_case.name, core_workreport.start_date_week_number, core_workreport.approved, core_case.number, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), (CASE WHEN (core_workreport.work_type_id IS NOT NULL) THEN core_worktype.input_type ELSE core_product.unit END)
  • Sort Method: external merge Disk: 11,280kB
4. 130.799 2,802.402 ↓ 1.3 93,572 1

Hash Left Join (cost=266,851.14..2,411,447.22 rows=74,726 width=112) (actual time=1,054.698..2,802.402 rows=93,572 loops=1)

  • Hash Cond: (core_worktype.work_category_id = core_workcategory.id)
5. 47.231 1,620.855 ↓ 1.3 93,572 1

Nested Loop Left Join (cost=264,762.11..534,529.79 rows=74,726 width=112) (actual time=1,032.765..1,620.855 rows=93,572 loops=1)

6. 0.000 1,573.624 ↓ 1.3 93,572 1

Hash Join (cost=264,761.68..389,725.73 rows=74,726 width=113) (actual time=1,032.759..1,573.624 rows=93,572 loops=1)

  • Hash Cond: (core_workreport.employee_id = core_employee.id)
  • Join Filter: (((core_employee.company_id = 7,701) AND (core_workreport.company_id = 7,701) AND (core_workreport.employee_id = 38,365)) OR (hashed SubPlan 6) OR (hashed SubPlan 7))
7. 277.989 1,380.765 ↓ 1.3 93,572 1

Hash Left Join (cost=176,066.28..299,068.77 rows=74,726 width=109) (actual time=913.435..1,380.765 rows=93,572 loops=1)

  • Hash Cond: (core_workreport.case_id = core_case.id)
8. 54.370 410.306 ↓ 1.3 93,572 1

Hash Left Join (cost=98,783.76..206,357.59 rows=74,726 width=79) (actual time=219.349..410.306 rows=93,572 loops=1)

  • Hash Cond: (core_workreport.work_type_id = core_worktype.id)
9. 53.412 224.418 ↓ 1.3 93,572 1

Index Scan using core_workreport_company_id on core_workreport (cost=85,405.64..188,333.63 rows=74,726 width=54) (actual time=86.490..224.418 rows=93,572 loops=1)

  • Index Cond: (company_id = 7,701)
  • Filter: (((company_id = 7,701) AND (employee_id = 38,365)) OR (hashed SubPlan 6) OR (hashed SubPlan 7))
10.          

SubPlan (for Index Scan)

11. 47.245 171.006 ↑ 1.1 1,675 2

Hash Left Join (cost=42.95..4,102.88 rows=1,900 width=4) (actual time=26.316..85.503 rows=1,675 loops=2)

  • Hash Cond: (u0_4.department_id = u3_2.id)
  • Filter: ((u1_1.responsible_id = 4,424) OR (u4.responsible_id = 4,424))
  • Rows Removed by Filter: 172,522
12. 52.862 123.202 ↓ 1.0 90,383 2

Merge Left Join (cost=0.58..3,507.78 rows=86,720 width=12) (actual time=0.019..61.601 rows=90,383 loops=2)

  • Merge Cond: (u0_4.id = u1_1.employee_id)
13. 64.990 64.990 ↓ 1.0 86,722 2

Index Scan using core_employee_pkey on core_employee u0_4 (cost=0.29..2,980.49 rows=86,720 width=8) (actual time=0.009..32.495 rows=86,722 loops=2)

14. 5.350 5.350 ↑ 1.0 8,500 2

Index Scan using core_employee_new_responsible_dcc97e32 on core_employee_new_responsible u1_1 (cost=0.29..204.24 rows=8,500 width=8) (actual time=0.008..2.675 rows=8,500 loops=2)

15. 0.167 0.559 ↓ 1.8 1,083 1

Hash (cost=34.88..34.88 rows=599 width=8) (actual time=0.559..0.559 rows=1,083 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 57kB
16. 0.205 0.392 ↓ 1.8 1,083 1

Hash Right Join (cost=16.66..34.88 rows=599 width=8) (actual time=0.169..0.392 rows=1,083 loops=1)

  • Hash Cond: (u4.department_id = u3_2.id)
17. 0.050 0.050 ↑ 1.0 599 1

Seq Scan on core_department_new_responsible u4 (cost=0.00..9.99 rows=599 width=8) (actual time=0.005..0.050 rows=599 loops=1)

18. 0.062 0.137 ↑ 1.0 518 1

Hash (cost=10.18..10.18 rows=518 width=4) (actual time=0.137..0.137 rows=518 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
19. 0.075 0.075 ↑ 1.0 518 1

Seq Scan on core_department u3_2 (cost=0.00..10.18 rows=518 width=4) (actual time=0.009..0.075 rows=518 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=43.22..81,212.06 rows=34,202 width=4) (never executed)

  • Hash Cond: (u0_5.department_id = u3_3.id)
  • Filter: ((u1_2.responsible_id = 4,424) OR (u4_1.responsible_id = 4,424))
21. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=0.85..71,447.88 rows=1,598,290 width=12) (never executed)

  • Merge Cond: (u0_5.id = u1_2.case_id)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using core_case_pkey on core_case u0_5 (cost=0.43..58,225.20 rows=1,598,290 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using core_case_new_responsible_case_id_3ac7290e on core_case_new_responsible u1_2 (cost=0.42..6,037.74 rows=255,137 width=8) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=34.88..34.88 rows=599 width=8) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=16.66..34.88 rows=599 width=8) (never executed)

  • Hash Cond: (u4_1.department_id = u3_3.id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on core_department_new_responsible u4_1 (cost=0.00..9.99 rows=599 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.18..10.18 rows=518 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on core_department u3_3 (cost=0.00..10.18 rows=518 width=4) (never executed)

29. 76.053 131.518 ↑ 1.0 317,406 1

Hash (cost=7,223.72..7,223.72 rows=318,272 width=29) (actual time=131.518..131.518 rows=317,406 loops=1)

  • Buckets: 262,144 Batches: 2 Memory Usage: 12,060kB
30. 55.465 55.465 ↑ 1.0 317,406 1

Seq Scan on core_worktype (cost=0.00..7,223.72 rows=318,272 width=29) (actual time=0.024..55.465 rows=317,406 loops=1)

31. 377.202 692.470 ↓ 1.0 1,598,319 1

Hash (cost=44,816.90..44,816.90 rows=1,598,290 width=34) (actual time=692.470..692.470 rows=1,598,319 loops=1)

  • Buckets: 262,144 Batches: 16 Memory Usage: 8,786kB
32. 315.268 315.268 ↓ 1.0 1,598,319 1

Seq Scan on core_case (cost=0.00..44,816.90 rows=1,598,290 width=34) (actual time=0.011..315.268 rows=1,598,319 loops=1)

33. 18.377 32.995 ↓ 1.0 86,722 1

Hash (cost=2,206.20..2,206.20 rows=86,720 width=24) (actual time=32.995..32.995 rows=86,722 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,957kB
34. 14.618 14.618 ↓ 1.0 86,722 1

Seq Scan on core_employee (cost=0.00..2,206.20 rows=86,720 width=24) (actual time=0.031..14.618 rows=86,722 loops=1)

35.          

SubPlan (for Hash Join)

36. 47.245 171.006 ↑ 1.1 1,675 2

Hash Left Join (cost=42.95..4,102.88 rows=1,900 width=4) (actual time=26.316..85.503 rows=1,675 loops=2)

  • Hash Cond: (u0_4.department_id = u3_2.id)
  • Filter: ((u1_1.responsible_id = 4,424) OR (u4.responsible_id = 4,424))
  • Rows Removed by Filter: 172,522
37. 52.862 123.202 ↓ 1.0 90,383 2

Merge Left Join (cost=0.58..3,507.78 rows=86,720 width=12) (actual time=0.019..61.601 rows=90,383 loops=2)

  • Merge Cond: (u0_4.id = u1_1.employee_id)
38. 64.990 64.990 ↓ 1.0 86,722 2

Index Scan using core_employee_pkey on core_employee u0_4 (cost=0.29..2,980.49 rows=86,720 width=8) (actual time=0.009..32.495 rows=86,722 loops=2)

39. 5.350 5.350 ↑ 1.0 8,500 2

Index Scan using core_employee_new_responsible_dcc97e32 on core_employee_new_responsible u1_1 (cost=0.29..204.24 rows=8,500 width=8) (actual time=0.008..2.675 rows=8,500 loops=2)

40. 0.167 0.559 ↓ 1.8 1,083 1

Hash (cost=34.88..34.88 rows=599 width=8) (actual time=0.559..0.559 rows=1,083 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 57kB
41. 0.205 0.392 ↓ 1.8 1,083 1

Hash Right Join (cost=16.66..34.88 rows=599 width=8) (actual time=0.169..0.392 rows=1,083 loops=1)

  • Hash Cond: (u4.department_id = u3_2.id)
42. 0.050 0.050 ↑ 1.0 599 1

Seq Scan on core_department_new_responsible u4 (cost=0.00..9.99 rows=599 width=8) (actual time=0.005..0.050 rows=599 loops=1)

43. 0.062 0.137 ↑ 1.0 518 1

Hash (cost=10.18..10.18 rows=518 width=4) (actual time=0.137..0.137 rows=518 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
44. 0.075 0.075 ↑ 1.0 518 1

Seq Scan on core_department u3_2 (cost=0.00..10.18 rows=518 width=4) (actual time=0.009..0.075 rows=518 loops=1)

45. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=43.22..81,212.06 rows=34,202 width=4) (never executed)

  • Hash Cond: (u0_5.department_id = u3_3.id)
  • Filter: ((u1_2.responsible_id = 4,424) OR (u4_1.responsible_id = 4,424))
46. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=0.85..71,447.88 rows=1,598,290 width=12) (never executed)

  • Merge Cond: (u0_5.id = u1_2.case_id)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using core_case_pkey on core_case u0_5 (cost=0.43..58,225.20 rows=1,598,290 width=8) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using core_case_new_responsible_case_id_3ac7290e on core_case_new_responsible u1_2 (cost=0.42..6,037.74 rows=255,137 width=8) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Hash (cost=34.88..34.88 rows=599 width=8) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=16.66..34.88 rows=599 width=8) (never executed)

  • Hash Cond: (u4_1.department_id = u3_3.id)
51. 0.000 0.000 ↓ 0.0 0

Seq Scan on core_department_new_responsible u4_1 (cost=0.00..9.99 rows=599 width=8) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.18..10.18 rows=518 width=4) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on core_department u3_3 (cost=0.00..10.18 rows=518 width=4) (never executed)

54. 0.000 0.000 ↓ 0.0 0 93,572

Index Scan using core_product_pkey on core_product (cost=0.43..1.93 rows=1 width=7) (actual time=0.000..0.000 rows=0 loops=93,572)

  • Index Cond: (core_workreport.product_id = id)
55. 10.021 21.456 ↑ 1.0 62,668 1

Hash (cost=1,305.68..1,305.68 rows=62,668 width=8) (actual time=21.456..21.456 rows=62,668 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,960kB
56. 11.435 11.435 ↑ 1.0 62,668 1

Seq Scan on core_workcategory (cost=0.00..1,305.68 rows=62,668 width=8) (actual time=0.006..11.435 rows=62,668 loops=1)

57.          

SubPlan (for Hash Left Join)

58. 93.572 280.716 ↓ 0.0 0 93,572

Limit (cost=1.01..5.72 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=93,572)

59. 0.000 187.144 ↓ 0.0 0 93,572

Nested Loop Left Join (cost=1.01..5.72 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=93,572)

60. 0.000 187.144 ↓ 0.0 0 93,572

Nested Loop (cost=0.72..5.38 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=93,572)

61. 187.144 187.144 ↓ 0.0 0 93,572

Index Scan using core_approvedby_work_report_id_2c40e461 on core_approvedby u0 (cost=0.43..2.86 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=93,572)

  • Index Cond: (work_report_id = core_workreport.id)
  • Filter: ((responsible_level)::text = 'first_approver'::text)
  • Rows Removed by Filter: 0
62. 0.000 0.000 ↓ 0.0 0

Index Scan using core_userprofile_pkey on core_userprofile u2 (cost=0.29..2.51 rows=1 width=8) (never executed)

  • Index Cond: (id = u0.user_profile_id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using core_employee_pkey on core_employee u3 (cost=0.29..0.33 rows=1 width=20) (never executed)

  • Index Cond: (u2.employee_id = id)
64. 0.000 93.572 ↓ 0.0 0 93,572

GroupAggregate (cost=0.58..8.12 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=93,572)

  • Group Key: u0_1.workreport_id
65. 0.000 93.572 ↓ 0.0 0 93,572

Nested Loop (cost=0.58..8.10 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=93,572)

66. 93.572 93.572 ↓ 0.0 0 93,572

Index Scan using file_upload_workreportupload_31c345b9 on file_upload_workreportupload u0_1 (cost=0.29..3.06 rows=2 width=12) (actual time=0.001..0.001 rows=0 loops=93,572)

  • Index Cond: (workreport_id = core_workreport.id)
67. 0.060 0.060 ↑ 1.0 1 12

Index Scan using file_upload_fileupload_pkey on file_upload_fileupload u1 (cost=0.29..2.51 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12)

  • Index Cond: (id = u0_1.fileupload_id)
  • Filter: (NOT is_directory)
68. 93.572 280.716 ↓ 0.0 0 93,572

Limit (cost=1.01..5.72 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=93,572)

69. 0.000 187.144 ↓ 0.0 0 93,572

Nested Loop Left Join (cost=1.01..5.72 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=93,572)

70. 71.358 187.144 ↓ 0.0 0 93,572

Nested Loop (cost=0.72..5.38 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=93,572)

71. 93.572 93.572 ↓ 0.0 0 93,572

Index Scan using core_approvedby_work_report_id_2c40e461 on core_approvedby u0_2 (cost=0.43..2.86 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=93,572)

  • Index Cond: (work_report_id = core_workreport.id)
  • Filter: ((responsible_level)::text = ANY ('{admin,final_approver}'::text[]))
72. 22.214 22.214 ↑ 1.0 1 22,214

Index Scan using core_userprofile_pkey on core_userprofile u2_1 (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=22,214)

  • Index Cond: (id = u0_2.user_profile_id)
73. 22.214 22.214 ↑ 1.0 1 22,214

Index Scan using core_employee_pkey on core_employee u3_1 (cost=0.29..0.33 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=22,214)

  • Index Cond: (u2_1.employee_id = id)
74. 280.716 280.716 ↑ 1.0 1 93,572

Index Only Scan using core_workreport_pkey on core_workreport v0 (cost=0.44..2.66 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=93,572)

  • Index Cond: (id = core_workreport.id)
  • Heap Fetches: 93,572
75. 93.572 93.572 ↓ 0.0 0 93,572

Index Scan using core_approvedby_work_report_id_2c40e461 on core_approvedby u0_3 (cost=0.43..2.86 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=93,572)

  • Index Cond: (work_report_id = core_workreport.id)
  • Filter: ((responsible_level)::text = 'first_approver'::text)
  • Rows Removed by Filter: 0
Planning time : 9.274 ms
Execution time : 2,922.585 ms