explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OWSi : Optimization for: benefits_biliing_detail; plan #pjVk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 182,574.441 ↓ 0.0 0 1

Limit (cost=10,638.78..10,683.30 rows=1 width=462) (actual time=182,574.440..182,574.441 rows=0 loops=1)

2. 168.913 182,632.831 ↓ 0.0 0 1

Gather (cost=10,638.78..10,683.30 rows=1 width=462) (actual time=182,574.438..182,632.831 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 0.001 182,463.918 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,638.78..9,641.36 rows=1 width=334) (actual time=182,463.918..182,463.918 rows=0 loops=3)

4. 0.001 182,463.917 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,638.37..9,640.10 rows=1 width=320) (actual time=182,463.917..182,463.917 rows=0 loops=3)

5. 0.000 182,463.916 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,638.08..9,639.69 rows=1 width=313) (actual time=182,463.916..182,463.916 rows=0 loops=3)

6. 0.001 182,463.916 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,637.80..9,639.34 rows=1 width=314) (actual time=182,463.916..182,463.916 rows=0 loops=3)

7. 0.005 182,463.915 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,637.38..9,638.62 rows=1 width=319) (actual time=182,463.915..182,463.915 rows=0 loops=3)

8. 0.000 182,463.910 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,637.11..9,638.27 rows=1 width=304) (actual time=182,463.910..182,463.910 rows=0 loops=3)

9. 0.001 182,463.910 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,636.69..9,637.50 rows=1 width=299) (actual time=182,463.910..182,463.910 rows=0 loops=3)

10. 0.000 182,463.909 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,636.28..9,636.89 rows=1 width=292) (actual time=182,463.909..182,463.909 rows=0 loops=3)

11. 0.001 182,463.909 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,635.99..9,636.51 rows=1 width=278) (actual time=182,463.909..182,463.909 rows=0 loops=3)

12. 0.001 182,463.908 ↓ 0.0 0 3

Nested Loop Left Join (cost=9,635.71..9,636.12 rows=1 width=266) (actual time=182,463.908..182,463.908 rows=0 loops=3)

13. 0.002 182,463.907 ↓ 0.0 0 3

Merge Left Join (cost=9,635.56..9,635.91 rows=1 width=252) (actual time=182,463.907..182,463.907 rows=0 loops=3)

  • Merge Cond: ((t7_0.peo_id = t9_0.peo_id) AND (t7_0.peo_id = t9_0.peo_id) AND (t7_0.peo_id = t9_0.peo_id) AND (t7_0.peo_id = t9_0.peo_id) AND (t7_0.peo_id = t9_0.peo_id) AND (t7_0.peo_id = t9_0.peo_id) AND (t7_0.
14. 0.059 182,463.905 ↓ 0.0 0 3

Sort (cost=9,632.38..9,632.38 rows=1 width=239) (actual time=182,463.905..182,463.905 rows=0 loops=3)

  • Sort Key: t7_0.peo_id, t7_0.type_code
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
15. 0.001 182,463.846 ↓ 0.0 0 3

Nested Loop Left Join (cost=693.48..9,632.37 rows=1 width=239) (actual time=182,463.846..182,463.846 rows=0 loops=3)

16. 0.000 182,463.845 ↓ 0.0 0 3

Nested Loop Left Join (cost=693.06..9,631.74 rows=1 width=190) (actual time=182,463.845..182,463.845 rows=0 loops=3)

17. 0.001 182,463.845 ↓ 0.0 0 3

Nested Loop (cost=692.78..9,631.35 rows=1 width=164) (actual time=182,463.844..182,463.845 rows=0 loops=3)

18. 178,827.588 182,463.844 ↓ 0.0 0 3

Nested Loop (cost=692.36..9,630.80 rows=1 width=138) (actual time=182,463.844..182,463.844 rows=0 loops=3)

19. 3,048.987 3,627.508 ↓ 20,431.0 20,431 3

Nested Loop (cost=682.10..9,616.51 rows=1 width=104) (actual time=9.928..3,627.508 rows=20,431 loops=3)

  • Join Filter: ((t5_0.client_id = t1_0.client_id) AND (t2_0.plan_id = t1_0.plan_id))
20. 234.933 578.382 ↓ 13.9 20,494 3

Hash Join (cost=681.68..7,918.06 rows=1,477 width=102) (actual time=9.847..578.382 rows=20,494 loops=3)

  • Hash Cond: (t6_0.client_id = t5_0.client_id)
21. 164.033 334.024 ↓ 6.7 20,494 3

Hash Join (cost=163.40..7,391.68 rows=3,080 width=64) (actual time=0.404..334.024 rows=20,494 loops=3)

  • Hash Cond: (t6_0.plan_id = t2_0.plan_id)
22. 169.790 169.790 ↑ 1.3 20,494 3

Parallel Seq Scan on employee_ben_detail t6_0 (cost=0.00..7,159.65 rows=26,073 width=27) (actual time=0.078..169.790 rows=20,494 loops=3)

  • Filter: (peo_id = 'lan'::text)
  • Rows Removed by Filter: 69713
23. 0.054 0.201 ↑ 1.0 196 3

Hash (cost=160.95..160.95 rows=196 width=37) (actual time=0.201..0.201 rows=196 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
24. 0.095 0.147 ↑ 1.0 196 3

Bitmap Heap Scan on benefit_plans t2_0 (cost=5.80..160.95 rows=196 width=37) (actual time=0.061..0.147 rows=196 loops=3)

  • Recheck Cond: (peo_id = 'lan'::text)
  • Heap Blocks: exact=12
25. 0.052 0.052 ↑ 1.0 196 3

Bitmap Index Scan on pk_benefit_plans (cost=0.00..5.75 rows=196 width=0) (actual time=0.052..0.052 rows=196 loops=3)

  • Index Cond: (peo_id = 'lan'::text)
26. 0.220 9.425 ↑ 1.0 897 3

Hash (cost=507.06..507.06 rows=897 width=38) (actual time=9.424..9.425 rows=897 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
27. 9.205 9.205 ↑ 1.0 897 3

Seq Scan on client_master t5_0 (cost=0.00..507.06 rows=897 width=38) (actual time=0.102..9.205 rows=897 loops=3)

  • Filter: (peo_id = 'lan'::text)
  • Rows Removed by Filter: 2309
28. 0.139 0.139 ↑ 1.0 1 61,483

Index Scan using pk_employee_ben on employee_ben t1_0 (cost=0.42..1.13 rows=1 width=28) (actual time=0.139..0.139 rows=1 loops=61,483)

  • Index Cond: ((peo_id = 'lan'::text) AND (client_id = t6_0.client_id) AND (employee_id = t6_0.employee_id) AND (plan_id = t6_0.plan_id))
29. 2.544 8.748 ↓ 0.0 0 61,293

Bitmap Heap Scan on epv t0_0 (cost=10.26..14.28 rows=1 width=48) (actual time=8.748..8.748 rows=0 loops=61,293)

  • Recheck Cond: ((employee_id = t1_0.employee_id) AND (peo_id = 'lan'::text) AND (client_id = t1_0.client_id))
  • Filter: (jsonb_array_length((record -> 'eeben'::text)) > 0)
  • Rows Removed by Filter: 56
  • Heap Blocks: exact=1151068
30. 0.048 6.204 ↓ 0.0 0 61,293

BitmapAnd (cost=10.26..10.26 rows=1 width=0) (actual time=6.204..6.204 rows=0 loops=61,293)

31. 0.179 0.179 ↓ 1.2 157 61,293

Bitmap Index Scan on idx_epv_employee_id (cost=0.00..1.93 rows=132 width=0) (actual time=0.179..0.179 rows=157 loops=61,293)

  • Index Cond: (employee_id = t1_0.employee_id)
32. 5.977 5.977 ↓ 15.6 10,832 61,196

Bitmap Index Scan on pk_epv (cost=0.00..8.08 rows=694 width=0) (actual time=5.977..5.977 rows=10,832 loops=61,196)

  • Index Cond: ((peo_id = 'lan'::text) AND (client_id = t1_0.client_id))
33. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_employee_per on employee_per t3_0 (cost=0.42..0.55 rows=1 width=33) (never executed)

  • Index Cond: ((peo_id = 'lan'::text) AND (employee_id = t0_0.employee_id))
34. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_employer_master on employer_master t4_0 (cost=0.28..0.33 rows=1 width=36) (never executed)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.peo_id = peo_id) AND (t0_0.peo_id = peo_id) AND (t0_0.peo_id = peo_id) AND (t0_0.peo_id = peo_id) AND (t0_0.peo_id = peo_id) AND (t0_0.peo_id
35. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_employee_com on employee_com t7_0 (cost=0.42..0.63 rows=1 width=56) (never executed)

  • Index Cond: ((t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo
36. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.18..3.20 rows=8 width=21) (never executed)

  • Sort Key: t9_0.peo_id, t9_0.type_code
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on employee_type_codes t9_0 (cost=0.00..3.06 rows=8 width=21) (never executed)

  • Filter: (peo_id = 'lan'::text)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_employee_status_codes on employee_status_codes t8_0 (cost=0.14..0.20 rows=1 width=24) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id =
39. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_location_codes on location_codes t10_0 (cost=0.29..0.39 rows=1 width=26) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id = 'lan'
40. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dept_codes on dept_codes t11_0 (cost=0.29..0.38 rows=1 width=29) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id = 'lan'::text
41. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_division_codes on division_codes t12_0 (cost=0.41..0.61 rows=1 width=24) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id = 'lan'::text) AND
42. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_proj_work_codes on proj_work_codes t13_0 (cost=0.41..0.59 rows=1 width=27) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id = 'lan'::text) AND (t7_0.
43. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_work_groups on work_groups t14_0 (cost=0.28..0.35 rows=1 width=39) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id = 'lan'::text) AND (t7_0.client
44. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_employee_pay on employee_pay t15_0 (cost=0.42..0.72 rows=1 width=18) (never executed)

  • Index Cond: ((t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (t1_0.peo_id = peo_id) AND (peo_id = 'lan'::text) AND (t1_0.client_id =
45. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_shift_codes on shift_codes t16_0 (cost=0.28..0.35 rows=1 width=22) (never executed)

  • Index Cond: ((t15_0.peo_id = peo_id) AND (t15_0.peo_id = peo_id) AND (t15_0.peo_id = peo_id) AND (t15_0.peo_id = peo_id) AND (t15_0.peo_id = peo_id) AND (t15_0.peo_id = peo_id) AND (t15_0.peo_id = peo_id) AND (peo_id = 'lan'::text) AND (t15_0.client_id
46. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_job_codes on job_codes t17_0 (cost=0.29..0.41 rows=1 width=35) (never executed)

  • Index Cond: ((t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (t7_0.peo_id = peo_id) AND (peo_id = 'lan'::text) AND (t7_0.client_id = client_id) A
47. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_hrp_users_user_type_employee_id on hrp_users t18_0 (cost=0.42..1.25 rows=1 width=38) (never executed)

  • Index Cond: ((user_type = 'E'::text) AND (t3_0.employee_id = employee_id) AND (t3_0.employee_id = employee_id) AND (t3_0.employee_id = employee_id) AND (t3_0.employee_id = employee_id) AND (t3_0.employee_id = employee_id) AND (t3_0.employee_id = employee_id) AND (t
  • Filter: ((peo_id = 'lan'::text) AND (t3_0.peo_id = peo_id) AND (t3_0.peo_id = peo_id) AND (t3_0.peo_id = peo_id) AND (t3_0.peo_id = peo_id) AND (t3_0.peo_id = peo_id) AND (t3_0.peo_id = peo_id) AND (t3_0.peo_id = peo_id))
48.          

SubPlan (for Gather)

49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..10.46 rows=100 width=32) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_epv on epv t (cost=0.43..8.45 rows=1 width=496) (never executed)

  • Index Cond: ((peo_id = t0_0.peo_id) AND (client_id = t0_0.client_id) AND (voucher_id = t0_0.voucher_id))
  • Filter: (employee_id = t0_0.employee_id)
51. 0.000 0.000 ↓ 0.0 0

Function Scan on jsonb_to_recordset mv (cost=0.01..1.00 rows=100 width=32) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..10.46 rows=100 width=32) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_epv on epv t_1 (cost=0.43..8.45 rows=1 width=496) (never executed)

  • Index Cond: ((peo_id = t0_0.peo_id) AND (client_id = t0_0.client_id) AND (voucher_id = t0_0.voucher_id))
  • Filter: (employee_id = t0_0.employee_id)
54. 0.000 0.000 ↓ 0.0 0

Function Scan on jsonb_to_recordset mv_1 (cost=0.01..1.00 rows=100 width=32) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..10.46 rows=100 width=32) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_epv on epv t_2 (cost=0.43..8.45 rows=1 width=496) (never executed)

  • Index Cond: ((peo_id = t0_0.peo_id) AND (client_id = t0_0.client_id) AND (voucher_id = t0_0.voucher_id))
  • Filter: (employee_id = t0_0.employee_id)
57. 0.000 0.000 ↓ 0.0 0

Function Scan on jsonb_to_recordset mv_2 (cost=0.01..1.00 rows=100 width=32) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..10.46 rows=100 width=32) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_epv on epv t_3 (cost=0.43..8.45 rows=1 width=496) (never executed)

  • Index Cond: ((peo_id = t0_0.peo_id) AND (client_id = t0_0.client_id) AND (voucher_id = t0_0.voucher_id))
  • Filter: (employee_id = t0_0.employee_id)
60. 0.000 0.000 ↓ 0.0 0

Function Scan on jsonb_to_recordset mv_3 (cost=0.01..1.00 rows=100 width=32) (never executed)

Planning time : 565.469 ms
Execution time : 182,633.824 ms