explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bAv3

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 36.343 ↑ 126.3 3 1

Append (cost=60,618.95..194,240.4 rows=379 width=82) (actual time=11.457..36.343 rows=3 loops=1)

  • Buffers: shared hit=278
2. 0.004 11.457 ↑ 365.0 1 1

Unique (cost=60,618.95..60,627.16 rows=365 width=82) (actual time=11.456..11.457 rows=1 loops=1)

  • Buffers: shared hit=90
3. 0.007 11.453 ↑ 365.0 1 1

Sort (cost=60,618.95..60,619.86 rows=365 width=82) (actual time=11.453..11.453 rows=1 loops=1)

  • Sort Key: p.oid, p.payroll_cycle_oid, p.period_id, p.period_number, p.start_date, p.end_date, p.pay_date, p.status
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=90
4. 0.002 11.446 ↑ 365.0 1 1

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

  • Buffers: shared hit=90
5. 0.004 10.304 ↑ 76.0 1 1

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

  • Buffers: shared hit=86
6. 0.285 0.285 ↑ 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.191..0.285 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
7. 9.931 10.015 ↑ 71.0 4 1

Seq Scan on pyr_pay_group ppg (cost=0..55,905.2 rows=284 width=16,817) (actual time=0.133..10.015 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
8.          

SubPlan (for Seq Scan)

9. 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
10. 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
11. 1.140 1.140 ↑ 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.888..1.14 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
12. 0.002 12.483 ↑ 7.0 1 1

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

  • Buffers: shared hit=94
13. 0.002 11.531 ↑ 367.0 1 1

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

  • Buffers: shared hit=90
14. 0.004 10.413 ↑ 76.0 1 1

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

  • Buffers: shared hit=86
15. 0.222 0.222 ↑ 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.131..0.222 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
16. 10.107 10.187 ↑ 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.129..10.187 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
17.          

SubPlan (for Seq Scan)

18. 0.064 0.080 ↓ 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.02..0.02 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.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
20. 1.116 1.116 ↑ 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.886..1.116 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
21. 0.950 0.950 ↑ 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.751..0.95 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
22. 0.004 12.401 ↑ 7.0 1 1

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

  • Buffers: shared hit=94
23. 0.003 11.430 ↑ 367.0 1 1

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

  • Buffers: shared hit=90
24. 0.005 10.319 ↑ 76.0 1 1

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

  • Buffers: shared hit=86
25. 0.227 0.227 ↑ 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.133..0.227 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
26. 10.007 10.087 ↑ 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.12..10.087 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
27.          

SubPlan (for Seq Scan)

28. 0.064 0.080 ↓ 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.02..0.02 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
29. 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
30. 1.108 1.108 ↑ 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.878..1.108 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
31. 0.967 0.967 ↑ 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.807..0.967 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.328 ms
Execution time : 36.75 ms