explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JrgTj

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 94.958 ↑ 21,231.7 3 1

Merge Join (cost=426,110.14..446,005.46 rows=63,695 width=450) (actual time=94.954..94.958 rows=3 loops=1)

  • Buffers: shared hit=2,306
2.          

CTE data_p

3. 0.013 34.243 ↑ 275.6 48 1

Nested Loop (cost=4.31..412,107.33 rows=13,231 width=50) (actual time=0.638..34.243 rows=48 loops=1)

  • Buffers: shared hit=76
4. 0.006 31.354 ↑ 324.0 1 1

Nested Loop (cost=4.31..397,710.03 rows=324 width=7) (actual time=0.605..31.354 rows=1 loops=1)

  • Buffers: shared hit=62
5. 0.055 0.071 ↑ 1.0 1 1

Bitmap Heap Scan on chr_emp_position person (cost=4.31..16.66 rows=1 width=11) (actual time=0.045..0.071 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
6. 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
7. 31.195 31.277 ↑ 470.0 2 1

Seq Scan on pyr_pay_group ppg (cost=0..397,672.22 rows=940 width=16,818) (actual time=0.555..31.277 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
8.          

SubPlan (for Seq Scan)

9. 0.068 0.082 ↓ 0.0 0 2

Bitmap Heap Scan on pyr_payroll_schedule pps (cost=4.92..422.21 rows=1 width=7) (actual time=0.041..0.041 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
10. 0.014 0.014 ↑ 1.1 108 2

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

  • Index Cond: ((pps.payroll_cycle_oid)::text = (ppg.payroll_cycle_oid)::text)
  • Buffers: shared hit=4
11. 2.876 2.876 ↓ 1.2 48 1

Index Scan using pyr_payroll_schedule_idx5 on pyr_payroll_schedule p_1 (cost=0..44.03 rows=41 width=50) (actual time=0.03..2.876 rows=48 loops=1)

  • Index Cond: ((p_1.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_1.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying) AND (p_1.f_unscheduled = '0'::numeric))
  • Buffers: shared hit=14
12.          

CTE data_q

13. 55.171 60.615 ↑ 4,012.5 2 1

Bitmap Heap Scan on pyr_payroll_schedule q_1 (cost=3,626.93..12,151.49 rows=8,025 width=15) (actual time=6.529..60.615 rows=2 loops=1)

  • 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))
  • Heap Blocks: exact=1,613
  • Buffers: shared hit=2,230
14. 5.444 5.444 ↑ 8.9 2,692 1

Bitmap Index Scan on pyr_payroll_schedule_idx4 (cost=0..3,624.92 rows=24,077 width=0) (actual time=5.444..5.444 rows=2,692 loops=1)

  • Index Cond: ((CURRENT_DATE >= q_1.start_date) AND (CURRENT_DATE <= q_1.end_date))
  • Buffers: shared hit=617
15. 0.009 60.629 ↑ 4,012.5 2 1

Sort (cost=680.93..701 rows=8,025 width=90) (actual time=60.628..60.629 rows=2 loops=1)

  • Sort Key: q.payroll_cycle_oid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,230
16. 60.620 60.620 ↑ 4,012.5 2 1

CTE Scan on data_q q (cost=0..160.5 rows=8,025 width=90) (actual time=6.532..60.62 rows=2 loops=1)

  • Buffers: shared hit=2,230
17. 0.020 34.301 ↑ 275.6 48 1

Sort (cost=1,170.39..1,203.47 rows=13,231 width=418) (actual time=34.298..34.301 rows=48 loops=1)

  • Sort Key: p.payrollcycleoid
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=76
18. 34.281 34.281 ↑ 275.6 48 1

CTE Scan on data_p p (cost=0..264.62 rows=13,231 width=418) (actual time=0.645..34.281 rows=48 loops=1)

  • Buffers: shared hit=76
Planning time : 1.487 ms
Execution time : 95.135 ms