explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q1um

Settings
# exclusive inclusive rows x rows loops node
1. 6.009 28,394.522 ↓ 366.0 366 1

Nested Loop Left Join (cost=2,008.26..2,083.09 rows=1 width=213) (actual time=28,355.779..28,394.522 rows=366 loops=1)

2. 0.202 28,387.781 ↓ 366.0 366 1

Nested Loop Left Join (cost=2,008.12..2,081.76 rows=1 width=189) (actual time=28,355.712..28,387.781 rows=366 loops=1)

  • Join Filter: ((ppas.periodstartdate = psp.periodstartdate) AND (ppas.periodenddate = psp.periodenddate) AND (bd.personid = ppp.personid))
3. 0.278 28,386.115 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,610.16..1,683.69 rows=1 width=157) (actual time=28,354.244..28,386.115 rows=366 loops=1)

4. 0.328 28,385.105 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,610.00..1,682.32 rows=1 width=152) (actual time=28,354.213..28,385.105 rows=366 loops=1)

  • Join Filter: ((a.asofdate >= ppay.effectivedate) AND (a.asofdate <= ppay.enddate) AND (ppas.payunitid = ppay.payunitid))
  • Rows Removed by Join Filter: 394
  • Filter: ((ppas.payunitid IS NULL) OR (ppay.payunitid IS NOT NULL))
5. 0.030 28,382.947 ↓ 366.0 366 1

Nested Loop (cost=1,609.72..1,681.94 rows=1 width=152) (actual time=28,354.201..28,382.947 rows=366 loops=1)

  • Join Filter: (ppp_1.ptoplanid = ppd_3.ptoplanid)
6. 0.583 28,376.695 ↓ 366.0 366 1

Nested Loop (cost=1,609.57..1,681.48 rows=1 width=153) (actual time=28,354.183..28,376.695 rows=366 loops=1)

  • Join Filter: (ppp.personid = pe_2.personid)
7. 0.208 28,373.916 ↓ 366.0 366 1

Nested Loop (cost=1,609.27..1,681.02 rows=1 width=201) (actual time=28,354.175..28,373.916 rows=366 loops=1)

  • Join Filter: ((a_3.asofdate >= (min(ppp_1.effectivedate))) AND (a_3.asofdate <= (max(ppp_1.enddate))))
8. 5.540 28,373.342 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,608.98..1,680.68 rows=1 width=205) (actual time=28,354.169..28,373.342 rows=366 loops=1)

  • Join Filter: ((a.asofdate >= pt.effectivedate) AND (a.asofdate <= pt.enddate) AND CASE WHEN (ppd.ptotierdatecalc = 4) THEN (((a_2.asofdate - pe2.emplservicedate) >= pt.servicemonthsmin) AND ((a_2.asofdate - pe2.emplservicedate) <= pt.servicemonthsmax)) ELSE ((COALESCE(GREATEST(((date_part('year'::text, age(((CASE WHEN (ppd_1.ptotierdatecalc = 2) THEN CASE WHEN (CASE WHEN (ppd_2.ptoplanyeartype = 'A'::bpchar) THEN to_date((((((date_part('month'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) WHEN (ppd_2.ptoplanyeartype = 'N'::bpchar) THEN (min(ppp_1_1.effectivedate)) WHEN (ppd_2.ptoplanyeartype = 'P'::bpchar) THEN to_date((((((ppd_2.ptoplanyearmonth)::text || '/'::text) || (ppd_2.ptoplanyearday)::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) ELSE to_date(('01/01/'::text || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) END > a_1.asofdate) THEN ((CASE WHEN (ppd_2.ptoplanyeartype = 'A'::bpchar) THEN to_date((((((date_part('month'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) WHEN (ppd_2.ptoplanyeartype = 'N'::bpchar) THEN (min(ppp_1_1.effectivedate)) WHEN (ppd_2.ptoplanyeartype = 'P'::bpchar) THEN to_date((((((ppd_2.ptoplanyearmonth)::text || '/'::text) || (ppd_2.ptoplanyearday)::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) ELSE to_date(('01/01/'::text || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) END - '1 year'::interval))::date ELSE CASE WHEN (ppd_2.ptoplanyeartype = 'A'::bpchar) THEN to_date((((((date_part('month'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) WHEN (ppd_2.ptoplanyeartype = 'N'::bpchar) THEN (min(ppp_1_1.effectivedate)) WHEN (ppd_2.ptoplanyeartype = 'P'::bpchar) THEN to_date((((((ppd_2.ptoplanyearmonth)::text || '/'::text) || (ppd_2.ptoplanyearday)::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) ELSE to_date(('01/01/'::text || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) END END WHEN (ppd_1.ptotierdatecalc = 3) THEN a_1.asofdate ELSE CASE WHEN (CASE WHEN (ppd_2.ptoplanyeartype = 'A'::bpchar) THEN to_date((((((date_part('month'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) WHEN (ppd_2.ptoplanyeartype = 'N'::bpchar) THEN (min(ppp_1_1.effectivedate)) WHEN (ppd_2.ptoplanyeartype = 'P'::bpchar) THEN to_date((((((ppd_2.ptoplanyearmonth)::text || '/'::text) || (ppd_2.ptoplanyearday)::text) || '/'::text) || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) ELSE to_date(('01/01/'::text || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) END > a_1.asofdate) THEN CASE WHEN (((date_part('year'::text, (((CASE WHEN (ppd_2.ptoplanyeartype = 'A'::bpchar) THEN (to_date((((((date_part('month'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text || '/'::text) || (date_part('day'::text, (pe_1.empllasthiredate)::timestamp without time zone))::text) || '/'::text) || ((date_part('year'::text, (a_1.asofdate)::timestamp without time zone) + '1'::double precision))::text), 'mm/dd/yyyy'::text) - 1) WHEN (ppd_2.ptoplanyeartype = 'N'::bpchar) THEN to_date('12/31/2199'::text, 'mm/dd/yyyy'::text) WHEN (ppd_2.ptoplanyeartype = 'P'::bpchar) THEN (to_date((((((ppd_2.ptoplanyearmonth)::text || '/'::text) || (ppd_2.ptoplanyearday)::text) || '/'::text) || ((date_part('year'::text, (a_1.asofdate)::timestamp without time zone) + '1'::double precision))::text), 'mm/dd/yyyy'::text) - 1) ELSE to_date(('12/31/'::text || (date_part('year'::text, (a_1.asofdate)::timestamp without time zone))::text), 'mm/dd/yyyy'::text) END - '1 year'::interval))::date)::timestamp without time zone))::integer % 4) = 0) THEN (CASE WHEN (ppd_2.ptoplanyeartype = 'A'::bpchar) T (...)
  • Rows Removed by Join Filter: 17934
9. 0.333 28,361.580 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,608.71..1,678.10 rows=1 width=205) (actual time=28,354.122..28,361.580 rows=366 loops=1)

10. 0.890 28,356.489 ↓ 366.0 366 1

Hash Join (cost=1,607.84..1,676.93 rows=1 width=197) (actual time=28,354.092..28,356.489 rows=366 loops=1)

  • Hash Cond: ((ppp_1.personid = ppp.personid) AND (ppp_1.ptoplanid = ppas.ptoplanid))
11. 4.093 5.680 ↓ 2.3 5,761 1

HashAggregate (cost=388.89..414.01 rows=2,512 width=25) (actual time=4.136..5.680 rows=5,761 loops=1)

  • Group Key: ppp_1.personid, ppp_1.ptoplanid
12. 1.587 1.587 ↑ 1.0 8,693 1

Seq Scan on person_pto_plans ppp_1 (cost=0.00..301.96 rows=8,693 width=25) (actual time=0.013..1.587 rows=8,693 loops=1)

  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 5
13. 1.060 28,349.919 ↓ 183.0 366 1

Hash (cost=1,218.92..1,218.92 rows=2 width=172) (actual time=28,349.919..28,349.919 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 87kB
14. 1.370 28,348.859 ↓ 183.0 366 1

Nested Loop Left Join (cost=400.89..1,218.92 rows=2 width=172) (actual time=22.912..28,348.859 rows=366 loops=1)

  • Join Filter: ((a.asofdate >= cpp.effectivedate) AND (a.asofdate <= cpp.enddate))
  • Rows Removed by Join Filter: 4745
15. 160.869 28,344.195 ↓ 183.0 366 1

Nested Loop Left Join (cost=400.62..1,216.78 rows=2 width=176) (actual time=22.874..28,344.195 rows=366 loops=1)

  • Join Filter: ((ppp.personid = ppp_1_1.personid) AND (ppp.ptoplanid = ppp_1_1.ptoplanid))
  • Rows Removed by Join Filter: 1183644
16. 0.599 15.600 ↓ 183.0 366 1

Nested Loop Left Join (cost=6.20..202.43 rows=2 width=172) (actual time=0.165..15.600 rows=366 loops=1)

  • Join Filter: ((a.asofdate >= pcp.effectivedate) AND (a.asofdate <= pcp.enddate))
  • Rows Removed by Join Filter: 1
17. 0.549 13.171 ↓ 183.0 366 1

Nested Loop (cost=5.92..201.73 rows=2 width=172) (actual time=0.159..13.171 rows=366 loops=1)

  • Join Filter: (ppp.personid = pe.personid)
18. 0.842 10.426 ↓ 61.0 366 1

Nested Loop (cost=5.63..198.63 rows=6 width=147) (actual time=0.149..10.426 rows=366 loops=1)

19. 0.644 6.656 ↓ 20.3 366 1

Nested Loop (cost=5.34..189.35 rows=18 width=131) (actual time=0.140..6.656 rows=366 loops=1)

  • Join Filter: ((a.asofdate >= pp.effectivedate) AND (a.asofdate <= pp.enddate))
  • Rows Removed by Join Filter: 173
20. 0.484 3.084 ↓ 2.7 366 1

Nested Loop (cost=5.06..135.81 rows=134 width=106) (actual time=0.080..3.084 rows=366 loops=1)

  • Join Filter: (ppas.ptoplanid = ppp.ptoplanid)
  • Rows Removed by Join Filter: 2871
21. 0.003 0.088 ↑ 1.0 1 1

Nested Loop (cost=4.78..75.12 rows=1 width=70) (actual time=0.068..0.088 rows=1 loops=1)

22. 0.009 0.080 ↑ 1.0 1 1

Nested Loop (cost=4.48..70.80 rows=1 width=66) (actual time=0.061..0.080 rows=1 loops=1)

23. 0.042 0.042 ↑ 1.0 1 1

Index Scan using pk_pto_plan_accrual_schedule on pto_plan_accrual_schedule ppas (cost=0.28..57.02 rows=1 width=26) (actual time=0.025..0.042 rows=1 loops=1)

  • Index Cond: (ptoplanaccrualpid = 173)
  • Filter: ((now() >= createts) AND (now() <= endts) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
24. 0.022 0.029 ↑ 1.0 1 1

Bitmap Heap Scan on pto_plan_desc ppd (cost=4.20..13.77 rows=1 width=40) (actual time=0.028..0.029 rows=1 loops=1)

  • Recheck Cond: (ptoplanid = ppas.ptoplanid)
  • Filter: ((now() >= createts) AND (now() <= endts) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 13
  • Heap Blocks: exact=4
25. 0.007 0.007 ↓ 2.0 14 1

Bitmap Index Scan on pk_pto_plan_desc (cost=0.00..4.20 rows=7 width=0) (actual time=0.007..0.007 rows=14 loops=1)

  • Index Cond: (ptoplanid = ppas.ptoplanid)
26. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using pk_asof on asof a (cost=0.29..4.31 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (asofdate = CASE WHEN (ppd.grantatperiodend = 'Y'::bpchar) THEN ppas.periodenddate WHEN (ppd.grantatperiodend = 'N'::bpchar) THEN ppas.periodstartdate ELSE ppas.effectivedate END)
  • Heap Fetches: 0
27. 2.512 2.512 ↓ 3.4 3,237 1

Index Scan using person_pto_plans_idx2 on person_pto_plans ppp (cost=0.29..48.61 rows=966 width=44) (actual time=0.007..2.512 rows=3,237 loops=1)

  • Index Cond: ((a.asofdate >= effectivedate) AND (a.asofdate <= enddate))
  • Filter: ((now() >= createts) AND (now() <= endts))
28. 2.928 2.928 ↑ 1.0 1 366

Index Scan using perspos_idx on pers_pos pp (cost=0.28..0.38 rows=1 width=33) (actual time=0.007..0.008 rows=1 loops=366)

  • Index Cond: (personid = ppp.personid)
  • Filter: ((now() >= createts) AND (now() <= endts))
29. 2.928 2.928 ↑ 1.0 1 366

Index Scan using personcompensation_idx on person_compensation pc (cost=0.29..0.51 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=366)

  • Index Cond: ((personid = ppp.personid) AND (a.asofdate >= effectivedate) AND (a.asofdate <= enddate))
  • Filter: ((earningscode = ANY ('{Regular,RegHrly,ExcHrly}'::bpchar[])) AND (now() >= createts) AND (now() <= endts))
30. 2.196 2.196 ↑ 1.0 1 366

Index Scan using personemploymentenddate on person_employment pe (cost=0.29..0.50 rows=1 width=29) (actual time=0.005..0.006 rows=1 loops=366)

  • Index Cond: ((personid = pc.personid) AND (a.asofdate <= enddate) AND (a.asofdate >= effectivedate))
  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 0
31. 1.830 1.830 ↑ 1.0 1 366

Index Scan using pk_position_comp_plan on position_comp_plan pcp (cost=0.28..0.33 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=366)

  • Index Cond: (pp.positionid = positionid)
  • Filter: ((now() >= createts) AND (now() <= endts))
32. 6,952.902 28,167.726 ↓ 323.5 3,235 366

Nested Loop (cost=394.42..507.03 rows=10 width=25) (actual time=0.088..76.961 rows=3,235 loops=366)

33. 0.000 15,294.774 ↓ 359.4 3,235 366

Nested Loop (cost=394.13..485.39 rows=9 width=40) (actual time=0.075..41.789 rows=3,235 loops=366)

34. 551.196 1,173.030 ↓ 68.8 3,235 366

Hash Join (cost=393.98..463.18 rows=47 width=33) (actual time=0.060..3.205 rows=3,235 loops=366)

  • Hash Cond: (ppp_1_1.ptoplanid = ppd_1.ptoplanid)
  • Join Filter: ((a_1.asofdate >= (min(ppp_1_1.effectivedate))) AND (a_1.asofdate <= (max(ppp_1_1.enddate))))
  • Rows Removed by Join Filter: 2526
35. 605.328 606.828 ↓ 2.3 5,761 366

HashAggregate (cost=388.89..414.01 rows=2,512 width=25) (actual time=0.012..1.658 rows=5,761 loops=366)

  • Group Key: ppp_1_1.personid, ppp_1_1.ptoplanid
36. 1.500 1.500 ↑ 1.0 8,693 1

Seq Scan on person_pto_plans ppp_1_1 (cost=0.00..301.96 rows=8,693 width=25) (actual time=0.007..1.500 rows=8,693 loops=1)

  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 5
37. 2.562 15.006 ↓ 1.1 37 366

Hash (cost=4.66..4.66 rows=34 width=12) (actual time=0.041..0.041 rows=37 loops=366)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
38. 2.196 12.444 ↓ 1.1 37 366

Nested Loop (cost=0.56..4.66 rows=34 width=12) (actual time=0.014..0.034 rows=37 loops=366)

39. 1.464 1.464 ↑ 1.0 1 366

Index Only Scan using pk_asof on asof a_1 (cost=0.29..0.32 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=366)

  • Index Cond: (asofdate = a.asofdate)
  • Heap Fetches: 0
40. 8.784 8.784 ↓ 1.1 37 366

Index Scan using ptobenefitplanid on pto_plan_desc ppd_1 (cost=0.27..4.00 rows=34 width=16) (actual time=0.008..0.024 rows=37 loops=366)

  • Index Cond: ((a_1.asofdate >= effectivedate) AND (a_1.asofdate <= enddate))
  • Filter: ((now() >= createts) AND (now() <= endts))
41. 14,208.120 14,208.120 ↑ 1.0 1 1,184,010

Index Scan using pk_pto_plan_desc on pto_plan_desc ppd_2 (cost=0.15..0.46 rows=1 width=15) (actual time=0.012..0.012 rows=1 loops=1,184,010)

  • Index Cond: (ptoplanid = ppp_1_1.ptoplanid)
  • Filter: ((now() >= createts) AND (now() <= endts) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 10
42. 5,920.050 5,920.050 ↑ 1.0 1 1,184,010

Index Scan using personemploymentenddate on person_employment pe_1 (cost=0.30..1.19 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=1,184,010)

  • Index Cond: ((personid = ppp_1_1.personid) AND (('now'::cstring)::date <= enddate) AND (('now'::cstring)::date >= effectivedate))
  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 0
43. 3.294 3.294 ↓ 14.0 14 366

Index Scan using pk_compplan_pto_plans on compplan_pto_plans cpp (cost=0.27..1.06 rows=1 width=16) (actual time=0.005..0.009 rows=14 loops=366)

  • Index Cond: ((pcp.compplanid = compplanid) AND (ppd.ptoplanid = ptoplanid))
  • Filter: ((now() >= createts) AND (now() <= endts))
44. 0.000 4.758 ↑ 1.0 1 366

Nested Loop (cost=0.86..1.16 rows=1 width=21) (actual time=0.013..0.013 rows=1 loops=366)

45. 0.366 2.562 ↑ 1.0 1 366

Nested Loop (cost=0.57..0.65 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=366)

46. 1.830 1.830 ↑ 1.0 1 366

Index Only Scan using person_pkey on person p (cost=0.29..0.33 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=366)

  • Index Cond: (personid = ppp.personid)
  • Heap Fetches: 0
47. 0.366 0.366 ↑ 1.0 1 366

Index Only Scan using pk_asof on asof a_2 (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=366)

  • Index Cond: (asofdate = a.asofdate)
  • Heap Fetches: 0
48. 2.196 2.196 ↑ 1.0 1 366

Index Scan using personemploymentenddate on person_employment pe2 (cost=0.29..0.50 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=366)

  • Index Cond: ((personid = p.personid) AND (a_2.asofdate <= enddate) AND (a_2.asofdate >= effectivedate))
  • Filter: ((emplstatus <> 'T'::bpchar) AND (now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 0
49. 6.222 6.222 ↓ 2.5 50 366

Index Scan using pk_pto_tier on pto_tier pt (cost=0.28..0.83 rows=20 width=40) (actual time=0.002..0.017 rows=50 loops=366)

  • Index Cond: (ptoplanid = ppp.ptoplanid)
  • Filter: ((now() >= createts) AND (now() <= endts))
50. 0.366 0.366 ↑ 1.0 1 366

Index Only Scan using pk_asof on asof a_3 (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=366)

  • Index Cond: (asofdate = a.asofdate)
  • Heap Fetches: 0
51. 2.196 2.196 ↑ 1.0 1 366

Index Scan using personemploymentenddate on person_employment pe_2 (cost=0.30..0.45 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=366)

  • Index Cond: ((personid = pe.personid) AND (('now'::cstring)::date <= enddate) AND (('now'::cstring)::date >= effectivedate))
  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 1
52. 6.222 6.222 ↑ 1.0 1 366

Index Scan using pk_pto_plan_desc on pto_plan_desc ppd_3 (cost=0.15..0.45 rows=1 width=15) (actual time=0.016..0.017 rows=1 loops=366)

  • Index Cond: (ptoplanid = ppp.ptoplanid)
  • Filter: ((now() >= createts) AND (now() <= endts) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 13
53. 1.830 1.830 ↑ 1.0 1 366

Index Scan using pk_person_payroll on person_payroll ppay (cost=0.28..0.37 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=366)

  • Index Cond: (ppp.personid = personid)
  • Filter: ((payunitrelationship = 'M'::bpchar) AND (now() >= createts) AND (now() <= endts))
54. 0.732 0.732 ↓ 0.0 0 366

Hash Right Join (cost=0.16..1.35 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=366)

  • Hash Cond: (fcpay.frequencycode = pu.frequencycode)
55. 0.000 0.000 ↓ 0.0 0

Seq Scan on frequency_codes fcpay (cost=0.00..1.13 rows=13 width=8) (never executed)

56. 0.000 0.000 ↓ 0.0 0 366

Hash (cost=0.15..0.15 rows=1 width=7) (actual time=0.000..0.000 rows=0 loops=366)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
57. 0.000 0.000 ↓ 0.0 0 366

Index Scan using pk_pay_unit on pay_unit pu (cost=0.13..0.15 rows=1 width=7) (actual time=0.000..0.000 rows=0 loops=366)

  • Index Cond: (ppay.payunitid = payunitid)
58. 0.000 1.464 ↓ 0.0 0 366

GroupAggregate (cost=397.96..398.02 rows=2 width=57) (actual time=0.004..0.004 rows=0 loops=366)

  • Group Key: bd.personid, psp.periodstartdate, psp.periodenddate, psp.payscheduleperiodid
59. 0.003 1.464 ↓ 0.0 0 366

Sort (cost=397.96..397.97 rows=2 width=30) (actual time=0.004..0.004 rows=0 loops=366)

  • Sort Key: bd.personid, psp.periodstartdate, psp.periodenddate, psp.payscheduleperiodid
  • Sort Method: quicksort Memory: 25kB
60. 0.001 1.461 ↓ 0.0 0 1

Nested Loop (cost=95.48..397.95 rows=2 width=30) (actual time=1.461..1.461 rows=0 loops=1)

  • Join Filter: (peo.etv_id = bd.etv_id)
61. 0.003 1.460 ↓ 0.0 0 1

Hash Join (cost=95.05..139.78 rows=4 width=20) (actual time=1.460..1.460 rows=0 loops=1)

  • Hash Cond: (bh.payscheduleperiodid = psp.payscheduleperiodid)
62. 0.034 0.034 ↑ 361.0 1 1

Seq Scan on batch_header bh (cost=0.00..43.34 rows=361 width=8) (actual time=0.034..0.034 rows=1 loops=1)

  • Filter: ((effectivedate <= enddate) AND (now() >= createts) AND (now() <= endts))
63. 0.000 1.423 ↓ 0.0 0 1

Hash (cost=94.85..94.85 rows=16 width=16) (actual time=1.423..1.423 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
64. 0.000 1.423 ↓ 0.0 0 1

Nested Loop (cost=21.40..94.85 rows=16 width=16) (actual time=1.423..1.423 rows=0 loops=1)

65. 0.001 1.423 ↓ 0.0 0 1

Merge Join (cost=1.63..51.42 rows=1 width=8) (actual time=1.423..1.423 rows=0 loops=1)

  • Merge Cond: (cn.companyid = pu_1.companyid)
  • Join Filter: ((("substring"(((cn.companycode)::text || (pu_1.payunitxid)::text), 1, 6))::character varying(6))::bpchar = peo.group_key)
66. 0.000 1.422 ↓ 0.0 0 1

Nested Loop (cost=0.55..1,066.11 rows=43 width=23) (actual time=1.422..1.422 rows=0 loops=1)

67. 0.046 0.046 ↑ 1.0 42 1

Index Scan using pk_companyname on companyname cn (cost=0.14..17.83 rows=43 width=13) (actual time=0.008..0.046 rows=42 loops=1)

  • Filter: ((now() >= createts) AND (now() <= endts) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 2
68. 0.018 1.386 ↓ 0.0 0 42

Materialize (cost=0.41..1,047.75 rows=1 width=10) (actual time=0.033..0.033 rows=0 loops=42)

69. 1.368 1.368 ↓ 0.0 0 1

Index Scan using pk_pspay_etv_operators on pspay_etv_operators peo (cost=0.41..1,047.74 rows=1 width=10) (actual time=1.368..1.368 rows=0 loops=1)

  • Index Cond: (operand = 'WS100'::bpchar)
  • Filter: ((now() >= createts) AND (now() <= endts))
70. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.08..1.09 rows=4 width=19) (never executed)

  • Sort Key: pu_1.companyid
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on pay_unit pu_1 (cost=0.00..1.04 rows=4 width=19) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pay_schedule_period psp (cost=19.76..39.30 rows=413 width=16) (never executed)

  • Recheck Cond: (payunitid = pu_1.payunitid)
73. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pk_pay_schedule_period_to_pay_unit (cost=0.00..19.66 rows=413 width=0) (never executed)

  • Index Cond: (payunitid = pu_1.payunitid)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using batch_details_valid on batch_detail bd (cost=0.43..62.48 rows=165 width=26) (never executed)

  • Index Cond: ((batchheaderid = bh.batchheaderid) AND (now() >= createts) AND (now() <= endts))
  • Filter: (effectivedate <= enddate)
75. 0.732 0.732 ↑ 1.0 1 366

Index Scan using pk_frequency_codes on frequency_codes fcpos (cost=0.14..0.15 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=366)

  • Index Cond: (pp.schedulefrequency = frequencycode)
Planning time : 151.361 ms
Execution time : 28,396.788 ms