explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HWzu

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 35.659 ↑ 126.3 3 1

Append (cost=0.28..194,216.65 rows=379 width=82) (actual time=1.31..35.659 rows=3 loops=1)

  • Buffers: shared hit=278
2. 0.001 11.312 ↑ 365.0 1 1

Nested Loop (cost=0.28..60,603.42 rows=365 width=82) (actual time=1.309..11.312 rows=1 loops=1)

  • Buffers: shared hit=90
3. 0.005 10.199 ↑ 76.0 1 1

Nested Loop (cost=0.28..56,197.93 rows=76 width=7) (actual time=0.424..10.199 rows=1 loops=1)

  • Buffers: shared hit=86
4. 0.283 0.283 ↑ 1.0 1 1

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

  • 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=54
5. 9.827 9.911 ↑ 71.0 4 1

Seq Scan on pyr_pay_group ppg (cost=0..55,905.2 rows=284 width=16,817) (actual time=0.134..9.911 rows=4 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Buffers: shared hit=32
6.          

SubPlan (for Seq Scan)

7. 0.068 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.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.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
9. 1.112 1.112 ↑ 5.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule p (cost=0..57.92 rows=5 width=50) (actual time=0.884..1.112 rows=1 loops=1)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg.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 (date_trunc('DAY'::text, x_sysdate(NULL::timestamp without time zone)) >= start_date) AND (date_trunc('DAY'::text, x_sysdate(NULL::timestamp without time zone)) <= end_date))
  • Buffers: shared hit=4
10. 0.005 12.215 ↑ 7.0 1 1

Nested Loop (cost=0.28..66,803.78 rows=7 width=82) (actual time=1.975..12.215 rows=1 loops=1)

  • Buffers: shared hit=94
11. 0.003 11.277 ↑ 367.0 1 1

Nested Loop (cost=0.28..60,603.42 rows=367 width=18) (actual time=1.236..11.277 rows=1 loops=1)

  • Buffers: shared hit=90
12. 0.004 10.148 ↑ 76.0 1 1

Nested Loop (cost=0.28..56,197.93 rows=76 width=7) (actual time=0.352..10.148 rows=1 loops=1)

  • Buffers: shared hit=86
13. 0.231 0.231 ↑ 1.0 1 1

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.141..0.231 rows=1 loops=1)

  • 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=54
14. 9.837 9.913 ↑ 71.0 4 1

Seq Scan on pyr_pay_group ppg_1 (cost=0..55,905.2 rows=284 width=16,817) (actual time=0.116..9.913 rows=4 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Buffers: shared hit=32
15.          

SubPlan (for Seq Scan)

16. 0.060 0.076 ↓ 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.019..0.019 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
17. 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_1.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
18. 1.126 1.126 ↑ 5.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule q (cost=0..57.92 rows=5 width=11) (actual time=0.881..1.126 rows=1 loops=1)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg_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 (date_trunc('DAY'::text, x_sysdate(NULL::timestamp without time zone)) >= start_date) AND (date_trunc('DAY'::text, x_sysdate(NULL::timestamp without time zone)) <= end_date))
  • Buffers: shared hit=4
19. 0.933 0.933 ↑ 1.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule p_1 (cost=0..16.88 rows=1 width=50) (actual time=0.735..0.933 rows=1 loops=1)

  • Index Cond: ((payroll_cycle_oid)::text = (q.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 (start_date = (q.end_date + 1)))
  • Buffers: shared hit=4
20. 0.003 12.129 ↑ 7.0 1 1

Nested Loop (cost=0.28..66,803.78 rows=7 width=82) (actual time=1.998..12.129 rows=1 loops=1)

  • Buffers: shared hit=94
21. 0.003 11.185 ↑ 367.0 1 1

Nested Loop (cost=0.28..60,603.42 rows=367 width=18) (actual time=1.225..11.185 rows=1 loops=1)

  • Buffers: shared hit=90
22. 0.005 10.078 ↑ 76.0 1 1

Nested Loop (cost=0.28..56,197.93 rows=76 width=7) (actual time=0.346..10.078 rows=1 loops=1)

  • Buffers: shared hit=86
23. 0.223 0.223 ↑ 1.0 1 1

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.134..0.223 rows=1 loops=1)

  • 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=54
24. 9.774 9.850 ↑ 71.0 4 1

Seq Scan on pyr_pay_group ppg_2 (cost=0..55,905.2 rows=284 width=16,817) (actual time=0.115..9.85 rows=4 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(vpd_key))
  • Buffers: shared hit=32
25.          

SubPlan (for Seq Scan)

26. 0.060 0.076 ↓ 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.019..0.019 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
27. 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
28. 1.104 1.104 ↑ 5.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule q_1 (cost=0..57.92 rows=5 width=11) (actual time=0.875..1.104 rows=1 loops=1)

  • Index Cond: ((payroll_cycle_oid)::text = (ppg_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 (date_trunc('DAY'::text, x_sysdate(NULL::timestamp without time zone)) >= start_date) AND (date_trunc('DAY'::text, x_sysdate(NULL::timestamp without time zone)) <= end_date))
  • Buffers: shared hit=4
29. 0.941 0.941 ↑ 1.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule p_2 (cost=0..16.88 rows=1 width=50) (actual time=0.771..0.941 rows=1 loops=1)

  • Index Cond: ((payroll_cycle_oid)::text = (q_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 (end_date = (q_1.start_date - 1)))
  • Buffers: shared hit=4
Planning time : 4.288 ms
Execution time : 36.051 ms