explain.depesz.com

PostgreSQL's explain analyze made readable

Result: It1z

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,574.244 ↑ 547.0 3 1

Append (cost=433,232.24..1,373,724.81 rows=1,641 width=82) (actual time=35.035..4,574.244 rows=3 loops=1)

  • Buffers: shared hit=4,383
2. 0.001 35.034 ↑ 1,469.0 1 1

Unique (cost=433,232.24..433,265.29 rows=1,469 width=82) (actual time=35.034..35.034 rows=1 loops=1)

  • Buffers: shared hit=287
3. 0.040 35.033 ↑ 1,469.0 1 1

Sort (cost=433,232.24..433,235.91 rows=1,469 width=82) (actual time=35.033..35.033 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=287
4. 0.003 34.993 ↑ 1,469.0 1 1

Nested Loop (cost=0.41..433,154.96 rows=1,469 width=82) (actual time=3.319..34.993 rows=1 loops=1)

  • Buffers: shared hit=281
5. 0.005 31.455 ↑ 324.0 1 1

Nested Loop (cost=0.41..398,555.3 rows=324 width=7) (actual time=0.859..31.455 rows=1 loops=1)

  • Buffers: shared hit=256
6. 1.124 1.124 ↑ 1.0 1 1

Index Scan using pk_chr_emp_position on chr_emp_position person (cost=0.41..959.34 rows=1 width=11) (actual time=0.299..1.124 rows=1 loops=1)

  • Index Cond: ((person.pfid)::text = '29449'::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(person.vpd_key))
  • Buffers: shared hit=198
7. 30.226 30.326 ↑ 470.0 2 1

Seq Scan on pyr_pay_group ppg (cost=0..397,574.81 rows=940 width=16,818) (actual time=0.558..30.326 rows=2 loops=1)

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

SubPlan (for Seq Scan)

9. 0.070 0.100 ↓ 0.0 0 2

Bitmap Heap Scan on pyr_payroll_schedule pps (cost=4.92..422.11 rows=1 width=7) (actual time=0.05..0.05 rows=0 loops=2)

  • Filter: ((pps.f_type = 'C'::bpchar) AND (("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(pps.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=14
  • Buffers: shared hit=19
10. 0.030 0.030 ↑ 1.1 108 2

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.92 rows=123 width=0) (actual time=0.015..0.015 rows=108 loops=2)

  • Index Cond: ((pps.payroll_cycle_oid)::text = (ppg.payroll_cycle_oid)::text)
  • Buffers: shared hit=5
11. 3.535 3.535 ↑ 5.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule p (cost=0..106.74 rows=5 width=50) (actual time=2.458..3.535 rows=1 loops=1)

  • Index Cond: ((p.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(p.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (p.f_unscheduled = '0'::numeric) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) >= p.start_date) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) <= p.end_date))
  • Buffers: shared hit=25
12. 0.240 2,274.126 ↑ 86.0 1 1

Hash Join (cost=34,449.4..470,217.46 rows=86 width=82) (actual time=2,239.024..2,274.126 rows=1 loops=1)

  • Buffers: shared hit=2,048
13. 0.004 37.897 ↑ 1,472.0 1 1

Nested Loop (cost=0.41..433,154.96 rows=1,472 width=18) (actual time=2.797..37.897 rows=1 loops=1)

  • Buffers: shared hit=269
14. 0.007 34.503 ↑ 324.0 1 1

Nested Loop (cost=0.41..398,555.3 rows=324 width=7) (actual time=0.766..34.503 rows=1 loops=1)

  • Buffers: shared hit=255
15. 1.092 1.092 ↑ 1.0 1 1

Index Scan using pk_chr_emp_position on chr_emp_position person_1 (cost=0.41..959.34 rows=1 width=11) (actual time=0.232..1.092 rows=1 loops=1)

  • Index Cond: ((person_1.pfid)::text = '29449'::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(person_1.vpd_key))
  • Buffers: shared hit=198
16. 33.298 33.404 ↑ 470.0 2 1

Seq Scan on pyr_pay_group ppg_1 (cost=0..397,574.81 rows=940 width=16,818) (actual time=0.531..33.404 rows=2 loops=1)

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

SubPlan (for Seq Scan)

18. 0.084 0.106 ↓ 0.0 0 2

Bitmap Heap Scan on pyr_payroll_schedule pps_1 (cost=4.92..422.11 rows=1 width=7) (actual time=0.053..0.053 rows=0 loops=2)

  • Filter: ((pps_1.f_type = 'C'::bpchar) AND (("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(pps_1.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=14
  • Buffers: shared hit=18
19. 0.022 0.022 ↑ 1.1 108 2

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.92 rows=123 width=0) (actual time=0.011..0.011 rows=108 loops=2)

  • Index Cond: ((pps_1.payroll_cycle_oid)::text = (ppg_1.payroll_cycle_oid)::text)
  • Buffers: shared hit=4
20. 3.390 3.390 ↑ 5.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule q (cost=0..106.74 rows=5 width=11) (actual time=2.028..3.39 rows=1 loops=1)

  • Index Cond: ((q.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(q.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (q.f_unscheduled = '0'::numeric) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) >= q.start_date) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) <= q.end_date))
  • Buffers: shared hit=14
21. 0.061 2,235.989 ↑ 550.0 72 1

Hash (cost=33,855..33,855 rows=39,599 width=50) (actual time=2,235.989..2,235.989 rows=72 loops=1)

  • Buffers: shared hit=1,779
22. 2,235.928 2,235.928 ↑ 550.0 72 1

Seq Scan on pyr_payroll_schedule p_1 (cost=0..33,855 rows=39,599 width=50) (actual time=38.941..2,235.928 rows=72 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(p_1.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (p_1.f_unscheduled = '0'::numeric))
  • Buffers: shared hit=1,779
23. 0.237 2,265.078 ↑ 86.0 1 1

Hash Join (cost=34,449.4..470,217.45 rows=86 width=82) (actual time=2,234.923..2,265.078 rows=1 loops=1)

  • Buffers: shared hit=2,048
24. 0.004 32.931 ↑ 1,472.0 1 1

Nested Loop (cost=0.41..433,154.96 rows=1,472 width=18) (actual time=2.778..32.931 rows=1 loops=1)

  • Buffers: shared hit=269
25. 0.006 29.926 ↑ 324.0 1 1

Nested Loop (cost=0.41..398,555.3 rows=324 width=7) (actual time=0.782..29.926 rows=1 loops=1)

  • Buffers: shared hit=255
26. 1.056 1.056 ↑ 1.0 1 1

Index Scan using pk_chr_emp_position on chr_emp_position person_2 (cost=0.41..959.34 rows=1 width=11) (actual time=0.234..1.056 rows=1 loops=1)

  • Index Cond: ((person_2.pfid)::text = '29449'::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(person_2.vpd_key))
  • Buffers: shared hit=198
27. 28.770 28.864 ↑ 470.0 2 1

Seq Scan on pyr_pay_group ppg_2 (cost=0..397,574.81 rows=940 width=16,818) (actual time=0.544..28.864 rows=2 loops=1)

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

SubPlan (for Seq Scan)

29. 0.070 0.094 ↓ 0.0 0 2

Bitmap Heap Scan on pyr_payroll_schedule pps_2 (cost=4.92..422.11 rows=1 width=7) (actual time=0.047..0.047 rows=0 loops=2)

  • Filter: ((pps_2.f_type = 'C'::bpchar) AND (("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(pps_2.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=14
  • Buffers: shared hit=18
30. 0.024 0.024 ↑ 1.1 108 2

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.92 rows=123 width=0) (actual time=0.012..0.012 rows=108 loops=2)

  • Index Cond: ((pps_2.payroll_cycle_oid)::text = (ppg_2.payroll_cycle_oid)::text)
  • Buffers: shared hit=4
31. 3.001 3.001 ↑ 5.0 1 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule q_1 (cost=0..106.74 rows=5 width=11) (actual time=1.993..3.001 rows=1 loops=1)

  • Index Cond: ((q_1.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(q_1.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (q_1.f_unscheduled = '0'::numeric) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) >= q_1.start_date) AND (date_trunc('DAY'::text, sysdate(NULL::timestamp without time zone)) <= q_1.end_date))
  • Buffers: shared hit=14
32. 0.064 2,231.910 ↑ 550.0 72 1

Hash (cost=33,855..33,855 rows=39,599 width=50) (actual time=2,231.91..2,231.91 rows=72 loops=1)

  • Buffers: shared hit=1,779
33. 2,231.846 2,231.846 ↑ 550.0 72 1

Seq Scan on pyr_payroll_schedule p_2 (cost=0..33,855 rows=39,599 width=50) (actual time=38.843..2,231.846 rows=72 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(p_2.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (p_2.f_unscheduled = '0'::numeric))
  • Buffers: shared hit=1,779
Planning time : 5.907 ms
Execution time : 4,574.71 ms