explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cmU1

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 537.088 ↑ 466.0 2 1

Unique (cost=204,291.56..204,305.54 rows=932 width=47) (actual time=537.083..537.088 rows=2 loops=1)

  • Buffers: shared hit=22,162
2.          

CTE pyr_scd

3. 0.785 0.793 ↓ 1.9 43 1

Bitmap Heap Scan on pyr_payroll_schedule pyr_payroll_schedule (cost=4.52..270.64 rows=23 width=7) (actual time=0.041..0.793 rows=43 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(pyr_payroll_schedule.vpd_key, 'PYR_PAYROLL_SCHEDULE'::character varying))
  • Heap Blocks: exact=3
  • Buffers: shared hit=4
4. 0.008 0.008 ↑ 1.5 45 1

Bitmap Index Scan on pyr_payroll_schedule_idx5 (cost=0..4.52 rows=69 width=0) (actual time=0.008..0.008 rows=45 loops=1)

  • Index Cond: ((pyr_payroll_schedule.payroll_cycle_oid)::text = '533489'::text)
  • Buffers: shared hit=1
5.          

CTE tp_data

6. 473.119 534.580 ↑ 2,097.0 9 1

Bitmap Heap Scan on wfm_timepair tp_1 (cost=90,161.94..186,999.65 rows=18,873 width=31) (actual time=177.976..534.58 rows=9 loops=1)

  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(tp_1.vpd_key, 'WFM_TIMEPAIR'::character varying))
  • Heap Blocks: exact=7,825
  • Buffers: shared hit=21,919
7. 0.002 61.461 ↓ 0.0 0 1

BitmapOr (cost=90,161.94..90,161.94 rows=56,859 width=0) (actual time=61.461..61.461 rows=0 loops=1)

  • Buffers: shared hit=14,094
8. 2.449 2.449 ↑ 1.1 26,456 1

Bitmap Index Scan on timepair25_idx (cost=0..766.28 rows=28,985 width=0) (actual time=2.449..2.449 rows=26,456 loops=1)

  • Index Cond: ((tp_1.intime >= '2020-03-28 00:00:00'::timestamp without time zone) AND (tp_1.intime < '2020-04-10 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=118
9. 59.010 59.010 ↑ 1.1 26,369 1

Bitmap Index Scan on timepair25_idx (cost=0..89,386.22 rows=27,874 width=0) (actual time=59.01..59.01 rows=26,369 loops=1)

  • Index Cond: ((tp_1.outtime >= '2020-03-28 00:00:00'::timestamp without time zone) AND (tp_1.outtime < '2020-04-10 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=13,976
10. 0.020 537.083 ↑ 103.6 9 1

Sort (cost=17,021.27..17,023.6 rows=932 width=47) (actual time=537.082..537.083 rows=9 loops=1)

  • Sort Key: associate.aoid, associate.given_name, associate.family_name, ep.employeeid, pt.payroll_status
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=22,162
11. 0.018 537.063 ↑ 103.6 9 1

Nested Loop (cost=887.52..16,975.31 rows=932 width=47) (actual time=180.036..537.063 rows=9 loops=1)

  • Buffers: shared hit=22,162
12. 0.011 536.856 ↑ 103.6 9 1

Nested Loop (cost=887.09..10,806.51 rows=932 width=53) (actual time=180.001..536.856 rows=9 loops=1)

  • Buffers: shared hit=22,126
13. 0.015 536.737 ↑ 244.0 9 1

Nested Loop (cost=887.09..8,062.1 rows=2,196 width=35) (actual time=179.981..536.737 rows=9 loops=1)

  • Buffers: shared hit=22,108
14. 0.021 536.560 ↑ 732.1 9 1

Hash Join (cost=886.81..1,707.79 rows=6,589 width=126) (actual time=179.944..536.56 rows=9 loops=1)

  • Buffers: shared hit=22,081
15. 534.593 534.593 ↑ 2,097.0 9 1

CTE Scan on tp_data tp (cost=0..377.46 rows=18,873 width=146) (actual time=177.985..534.593 rows=9 loops=1)

  • Buffers: shared hit=21,919
16. 0.025 1.946 ↑ 1.5 46 1

Hash (cost=885.93..885.93 rows=70 width=8) (actual time=1.946..1.946 rows=46 loops=1)

  • Buffers: shared hit=162
17. 0.051 1.921 ↑ 1.5 46 1

Nested Loop (cost=0.94..885.93 rows=70 width=8) (actual time=0.892..1.921 rows=46 loops=1)

  • Buffers: shared hit=162
18. 0.025 0.838 ↓ 1.9 43 1

HashAggregate (cost=0.52..0.75 rows=23 width=78) (actual time=0.831..0.838 rows=43 loops=1)

  • Group Key: (pyr_scd.oid)::text
  • Buffers: shared hit=4
19. 0.813 0.813 ↓ 1.9 43 1

CTE Scan on pyr_scd pyr_scd (cost=0..0.46 rows=23 width=78) (actual time=0.043..0.813 rows=43 loops=1)

  • Buffers: shared hit=4
20. 1.032 1.032 ↑ 3.0 1 43

Index Scan using timecard5_idx on wfm_timecard tc (cost=0.42..38.46 rows=3 width=15) (actual time=0.01..0.024 rows=1 loops=43)

  • Index Cond: ((tc.payrollscheduleoid)::text = (pyr_scd.oid)::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(tc.vpd_key, 'WFM_TIMECARD'::character varying))
  • Buffers: shared hit=158
21. 0.162 0.162 ↑ 1.0 1 9

Index Scan using chr_emp_position_idx13 on chr_emp_position ep (cost=0.29..0.95 rows=1 width=27) (actual time=0.018..0.018 rows=1 loops=9)

  • Index Cond: ((ep.employeeid)::text = (tp.employeeid)::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(ep.vpd_key))
  • Buffers: shared hit=27
22. 0.108 0.108 ↑ 1.0 1 9

Index Scan using chr_associate_idx8 on chr_associate associate (cost=0..1.24 rows=1 width=44) (actual time=0.012..0.012 rows=1 loops=9)

  • Index Cond: ((associate.userid)::text = (ep.userid)::text)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all_static(associate.vpd_key))
  • Buffers: shared hit=18
23. 0.189 0.189 ↑ 1.0 1 9

Index Scan using payroll5_idx on wfm_payroll pt (cost=0.43..6.61 rows=1 width=10) (actual time=0.021..0.021 rows=1 loops=9)

  • Index Cond: (pt.timepairoid = tp.objectid)
  • Filter: ((("current_user"())::text <> ANY ('{wfmadmin,skipvpd}'::text[])) AND vpd_sec_usr.f_sel_policy_all(pt.vpd_key, 'WFM_PAYROLL'::character varying))
  • Buffers: shared hit=36
Planning time : 1.588 ms
Execution time : 537.283 ms