explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ctvW

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,720.696 ↑ 546.0 3 1

Append (cost=432,289.44..1,370,755.55 rows=1,638 width=82) (actual time=34.198..4,720.696 rows=3 loops=1)

  • Buffers: shared hit=3,786
2. 0.001 34.197 ↑ 1,467.0 1 1

Unique (cost=432,289.44..432,322.45 rows=1,467 width=82) (actual time=34.197..34.197 rows=1 loops=1)

  • Buffers: shared hit=76
3. 0.009 34.196 ↑ 1,467.0 1 1

Sort (cost=432,289.44..432,293.11 rows=1,467 width=82) (actual time=34.196..34.196 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=76
4. 0.002 34.187 ↑ 1,467.0 1 1

Nested Loop (cost=4.31..432,212.29 rows=1,467 width=82) (actual time=2.965..34.187 rows=1 loops=1)

  • Buffers: shared hit=76
5. 0.006 31.090 ↑ 324.0 1 1

Nested Loop (cost=4.31..397,612.62 rows=324 width=7) (actual time=0.909..31.09 rows=1 loops=1)

  • Buffers: shared hit=62
6. 0.126 0.142 ↑ 1.0 1 1

Bitmap Heap Scan on chr_emp_position person (cost=4.31..16.66 rows=1 width=11) (actual time=0.115..0.142 rows=1 loops=1)

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

Bitmap Index Scan on chr_emp_position_idx15 (cost=0..4.31 rows=3 width=0) (actual time=0.016..0.016 rows=3 loops=1)

  • Index Cond: ((person.pfid)::text = '29449'::text)
  • Buffers: shared hit=2
8. 30.850 30.942 ↑ 470.0 2 1

Seq Scan on pyr_pay_group ppg (cost=0..397,574.81 rows=940 width=16,818) (actual time=0.79..30.942 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=57
9.          

SubPlan (for Seq Scan)

10. 0.074 0.092 ↓ 0.0 0 2

Bitmap Heap Scan on pyr_payroll_schedule pps (cost=4.92..422.11 rows=1 width=7) (actual time=0.045..0.046 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=18
11. 0.018 0.018 ↑ 1.1 108 2

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

  • Index Cond: ((pps.payroll_cycle_oid)::text = (ppg.payroll_cycle_oid)::text)
  • Buffers: shared hit=4
12. 3.095 3.095 ↑ 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.054..3.095 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=14
13. 0.245 2,312.115 ↑ 86.0 1 1

Hash Join (cost=34,393.63..469,204.27 rows=86 width=82) (actual time=2,282.26..2,312.115 rows=1 loops=1)

  • Buffers: shared hit=1,855
14. 0.004 32.489 ↑ 1,469.0 1 1

Nested Loop (cost=4.31..432,212.29 rows=1,469 width=18) (actual time=2.635..32.489 rows=1 loops=1)

  • Buffers: shared hit=76
15. 0.007 29.419 ↑ 324.0 1 1

Nested Loop (cost=4.31..397,612.62 rows=324 width=7) (actual time=0.581..29.419 rows=1 loops=1)

  • Buffers: shared hit=62
16. 0.045 0.062 ↑ 1.0 1 1

Bitmap Heap Scan on chr_emp_position person_1 (cost=4.31..16.66 rows=1 width=11) (actual time=0.036..0.062 rows=1 loops=1)

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

Bitmap Index Scan on chr_emp_position_idx15 (cost=0..4.31 rows=3 width=0) (actual time=0.017..0.017 rows=3 loops=1)

  • Index Cond: ((person_1.pfid)::text = '29449'::text)
  • Buffers: shared hit=2
18. 29.258 29.350 ↑ 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.542..29.35 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
19.          

SubPlan (for Seq Scan)

20. 0.074 0.092 ↓ 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.046..0.046 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
21. 0.018 0.018 ↑ 1.1 108 2

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

  • Index Cond: ((pps_1.payroll_cycle_oid)::text = (ppg_1.payroll_cycle_oid)::text)
  • Buffers: shared hit=4
22. 3.066 3.066 ↑ 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.051..3.066 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
23. 0.069 2,279.381 ↑ 549.0 72 1

Hash (cost=33,796.41..33,796.41 rows=39,527 width=50) (actual time=2,279.381..2,279.381 rows=72 loops=1)

  • Buffers: shared hit=1,779
24. 2,279.312 2,279.312 ↑ 549.0 72 1

Seq Scan on pyr_payroll_schedule p_1 (cost=0..33,796.41 rows=39,527 width=50) (actual time=39.665..2,279.312 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
25. 0.242 2,374.378 ↑ 85.0 1 1

Hash Join (cost=34,393.63..469,204.27 rows=85 width=82) (actual time=2,340.576..2,374.378 rows=1 loops=1)

  • Buffers: shared hit=1,855
26. 0.004 36.382 ↑ 1,469.0 1 1

Nested Loop (cost=4.31..432,212.29 rows=1,469 width=18) (actual time=2.581..36.382 rows=1 loops=1)

  • Buffers: shared hit=76
27. 0.006 33.203 ↑ 324.0 1 1

Nested Loop (cost=4.31..397,612.62 rows=324 width=7) (actual time=0.568..33.203 rows=1 loops=1)

  • Buffers: shared hit=62
28. 0.048 0.066 ↑ 1.0 1 1

Bitmap Heap Scan on chr_emp_position person_2 (cost=4.31..16.66 rows=1 width=11) (actual time=0.038..0.066 rows=1 loops=1)

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

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

  • Index Cond: ((person_2.pfid)::text = '29449'::text)
  • Buffers: shared hit=2
30. 33.041 33.131 ↑ 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.526..33.131 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
31.          

SubPlan (for Seq Scan)

32. 0.070 0.090 ↓ 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.045..0.045 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
33. 0.020 0.020 ↑ 1.1 108 2

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

  • Index Cond: ((pps_2.payroll_cycle_oid)::text = (ppg_2.payroll_cycle_oid)::text)
  • Buffers: shared hit=4
34. 3.175 3.175 ↑ 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=2.011..3.175 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
35. 0.065 2,337.754 ↑ 549.0 72 1

Hash (cost=33,796.41..33,796.41 rows=39,527 width=50) (actual time=2,337.754..2,337.754 rows=72 loops=1)

  • Buffers: shared hit=1,779
36. 2,337.689 2,337.689 ↑ 549.0 72 1

Seq Scan on pyr_payroll_schedule p_2 (cost=0..33,796.41 rows=39,527 width=50) (actual time=38.433..2,337.689 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.497 ms
Execution time : 4,721.148 ms