explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L947

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 173.766 ↑ 1.0 3 1

Append (cost=9.01..2,196.81 rows=3 width=82) (actual time=4.041..173.766 rows=3 loops=1)

  • Buffers: shared hit=24,373
2. 0.008 6.272 ↑ 1.0 1 1

Nested Loop (cost=9.01..721.41 rows=1 width=82) (actual time=4.04..6.272 rows=1 loops=1)

  • Buffers: shared hit=259
3. 0.007 5.516 ↓ 4.0 4 1

Nested Loop (cost=8.73..435.06 rows=1 width=58) (actual time=0.291..5.516 rows=4 loops=1)

  • Buffers: shared hit=43
4. 0.133 0.229 ↓ 4.0 4 1

Bitmap Heap Scan on pyr_pay_group ppg (cost=4.31..210.46 rows=1 width=16,817) (actual time=0.123..0.229 rows=4 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Heap Blocks: exact=1
  • Buffers: shared hit=23
5. 0.012 0.012 ↑ 1.0 4 1

Bitmap Index Scan on pyr_pay_group_idx1 (cost=0..4.31 rows=4 width=0) (actual time=0.012..0.012 rows=4 loops=1)

  • Index Cond: ((vpd_key)::text = 'WNVPD0000001505'::text)
  • Buffers: shared hit=2
6.          

SubPlan (for Bitmap Heap Scan)

7. 0.064 0.084 ↓ 0.0 0 4

Bitmap Heap Scan on pyr_payroll_schedule pps (cost=4.42..196.01 rows=1 width=7) (actual time=0.021..0.021 rows=0 loops=4)

  • Filter: ((f_type = 'C'::bpchar) AND (("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=15
  • Buffers: shared hit=20
8. 0.020 0.020 ↑ 1.0 55 4

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.42 rows=56 width=0) (actual time=0.005..0.005 rows=55 loops=4)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
9. 5.268 5.280 ↑ 2.0 1 4

Bitmap Heap Scan on pyr_payroll_schedule p (cost=4.42..224.57 rows=2 width=50) (actual time=0.378..1.32 rows=1 loops=4)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (f_unscheduled = '0'::numeric) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) >= start_date) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) <= end_date))
  • Heap Blocks: exact=15
  • Buffers: shared hit=20
10. 0.012 0.012 ↑ 1.0 55 4

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.42 rows=56 width=0) (actual time=0.003..0.003 rows=55 loops=4)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
11. 0.748 0.748 ↑ 1.0 1 4

Index Scan using pk_chr_emp_position on chr_emp_position person (cost=0.28..286.34 rows=1 width=8) (actual time=0.105..0.187 rows=1 loops=4)

  • Index Cond: ((pfid)::text = '96861'::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Buffers: shared hit=216
12. 0.026 83.385 ↑ 1.0 1 1

Nested Loop (cost=9.01..737.68 rows=1 width=82) (actual time=34.837..83.385 rows=1 loops=1)

  • Buffers: shared hit=12,057
13. 0.184 39.555 ↓ 47.0 47 1

Nested Loop (cost=9.01..693.02 rows=1 width=57) (actual time=22.897..39.555 rows=47 loops=1)

  • Buffers: shared hit=11,869
14. 0.059 4.769 ↓ 219.0 219 1

Nested Loop (cost=8.73..406.67 rows=1 width=65) (actual time=0.08..4.769 rows=219 loops=1)

  • Buffers: shared hit=43
15. 0.082 0.174 ↓ 4.0 4 1

Bitmap Heap Scan on pyr_pay_group ppg_1 (cost=4.31..210.46 rows=1 width=16,817) (actual time=0.048..0.174 rows=4 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Heap Blocks: exact=1
  • Buffers: shared hit=23
16. 0.008 0.008 ↑ 1.0 4 1

Bitmap Index Scan on pyr_pay_group_idx1 (cost=0..4.31 rows=4 width=0) (actual time=0.008..0.008 rows=4 loops=1)

  • Index Cond: ((vpd_key)::text = 'WNVPD0000001505'::text)
  • Buffers: shared hit=2
17.          

SubPlan (for Bitmap Heap Scan)

18. 0.064 0.084 ↓ 0.0 0 4

Bitmap Heap Scan on pyr_payroll_schedule pps_1 (cost=4.42..196.01 rows=1 width=7) (actual time=0.021..0.021 rows=0 loops=4)

  • Filter: ((f_type = 'C'::bpchar) AND (("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=15
  • Buffers: shared hit=20
19. 0.020 0.020 ↑ 1.0 55 4

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.42 rows=56 width=0) (actual time=0.005..0.005 rows=55 loops=4)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg_1.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
20. 4.524 4.536 ↓ 2.9 55 4

Bitmap Heap Scan on pyr_payroll_schedule p_1 (cost=4.42..196.01 rows=19 width=50) (actual time=0.028..1.134 rows=55 loops=4)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (f_unscheduled = '0'::numeric))
  • Heap Blocks: exact=15
  • Buffers: shared hit=20
21. 0.012 0.012 ↑ 1.0 55 4

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.42 rows=56 width=0) (actual time=0.003..0.003 rows=55 loops=4)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg_1.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
22. 34.602 34.602 ↑ 1.0 1 219

Index Scan using pk_chr_emp_position on chr_emp_position person_1 (cost=0.28..286.34 rows=1 width=8) (actual time=0.082..0.158 rows=1 loops=219)

  • Index Cond: ((pfid)::text = '96861'::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Buffers: shared hit=11,826
23. 43.804 43.804 ↓ 0.0 0 47

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule q (cost=0..44.65 rows=1 width=11) (actual time=0.928..0.932 rows=0 loops=47)

  • Index Cond: ((payroll_cycle_oid)::text = (p_1.payroll_cycle_oid)::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (f_unscheduled = '0'::numeric) AND (p_1.start_date = (end_date + 1)) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) >= start_date) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) <= end_date))
  • Buffers: shared hit=188
24. 0.004 84.105 ↑ 1.0 1 1

Nested Loop (cost=9.01..737.68 rows=1 width=82) (actual time=33.295..84.105 rows=1 loops=1)

  • Buffers: shared hit=12,057
25. 0.289 40.203 ↓ 47.0 47 1

Nested Loop (cost=9.01..693.02 rows=1 width=57) (actual time=23.48..40.203 rows=47 loops=1)

  • Buffers: shared hit=11,869
26. 0.064 4.874 ↓ 219.0 219 1

Nested Loop (cost=8.73..406.67 rows=1 width=65) (actual time=0.101..4.874 rows=219 loops=1)

  • Buffers: shared hit=43
27. 0.088 0.198 ↓ 4.0 4 1

Bitmap Heap Scan on pyr_pay_group ppg_2 (cost=4.31..210.46 rows=1 width=16,817) (actual time=0.067..0.198 rows=4 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Heap Blocks: exact=1
  • Buffers: shared hit=23
28. 0.018 0.018 ↑ 1.0 4 1

Bitmap Index Scan on pyr_pay_group_idx1 (cost=0..4.31 rows=4 width=0) (actual time=0.018..0.018 rows=4 loops=1)

  • Index Cond: ((vpd_key)::text = 'WNVPD0000001505'::text)
  • Buffers: shared hit=2
29.          

SubPlan (for Bitmap Heap Scan)

30. 0.068 0.092 ↓ 0.0 0 4

Bitmap Heap Scan on pyr_payroll_schedule pps_2 (cost=4.42..196.01 rows=1 width=7) (actual time=0.023..0.023 rows=0 loops=4)

  • Filter: ((f_type = 'C'::bpchar) AND (("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=15
  • Buffers: shared hit=20
31. 0.024 0.024 ↑ 1.0 55 4

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.42 rows=56 width=0) (actual time=0.006..0.006 rows=55 loops=4)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg_2.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
32. 4.596 4.612 ↓ 2.9 55 4

Bitmap Heap Scan on pyr_payroll_schedule p_2 (cost=4.42..196.01 rows=19 width=50) (actual time=0.029..1.153 rows=55 loops=4)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (f_unscheduled = '0'::numeric))
  • Heap Blocks: exact=15
  • Buffers: shared hit=20
33. 0.016 0.016 ↑ 1.0 55 4

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.42 rows=56 width=0) (actual time=0.004..0.004 rows=55 loops=4)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg_2.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
34. 35.040 35.040 ↑ 1.0 1 219

Index Scan using pk_chr_emp_position on chr_emp_position person_2 (cost=0.28..286.34 rows=1 width=8) (actual time=0.084..0.16 rows=1 loops=219)

  • Index Cond: ((pfid)::text = '96861'::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Buffers: shared hit=11,826
35. 43.898 43.898 ↓ 0.0 0 47

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule q_1 (cost=0..44.65 rows=1 width=11) (actual time=0.93..0.934 rows=0 loops=47)

  • Index Cond: ((payroll_cycle_oid)::text = (p_2.payroll_cycle_oid)::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (f_unscheduled = '0'::numeric) AND (p_2.end_date = (start_date - 1)) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) >= start_date) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) <= end_date))
  • Buffers: shared hit=188
Planning time : 4.3 ms
Execution time : 174.212 ms