explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EkQE

Settings
# exclusive inclusive rows x rows loops node
1. 5.419 29,631.044 ↓ 366.0 366 1

Nested Loop Left Join (cost=2,014.80..2,089.63 rows=1 width=213) (actual time=29,595.734..29,631.044 rows=366 loops=1)

2. 0.108 29,624.893 ↓ 366.0 366 1

Nested Loop Left Join (cost=2,014.67..2,088.30 rows=1 width=189) (actual time=29,595.668..29,624.893 rows=366 loops=1)

  • Join Filter: ((ppas.periodstartdate = psp.periodstartdate) AND (ppas.periodenddate = psp.periodenddate) AND (bd.personid = ppp.personid))
3. 0.248 29,623.321 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,633.20..1,706.72 rows=1 width=157) (actual time=29,594.320..29,623.321 rows=366 loops=1)

4. 0.190 29,622.341 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,633.03..1,705.36 rows=1 width=152) (actual time=29,594.312..29,622.341 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.388 29,620.321 ↓ 366.0 366 1

Nested Loop (cost=1,632.75..1,704.97 rows=1 width=152) (actual time=29,594.300..29,620.321 rows=366 loops=1)

  • Join Filter: (ppp_1.ptoplanid = ppd_3.ptoplanid)
6. 0.621 29,614.443 ↓ 366.0 366 1

Nested Loop (cost=1,632.60..1,704.51 rows=1 width=153) (actual time=29,594.282..29,614.443 rows=366 loops=1)

  • Join Filter: (ppp.personid = pe_2.personid)
7. 0.149 29,611.992 ↓ 366.0 366 1

Nested Loop (cost=1,632.31..1,704.05 rows=1 width=201) (actual time=29,594.274..29,611.992 rows=366 loops=1)

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

Nested Loop Left Join (cost=1,632.02..1,703.72 rows=1 width=205) (actual time=29,594.269..29,611.477 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.042 29,600.883 ↓ 366.0 366 1

Nested Loop Left Join (cost=1,631.74..1,701.14 rows=1 width=205) (actual time=29,594.226..29,600.883 rows=366 loops=1)

10. 0.801 29,596.449 ↓ 366.0 366 1

Hash Join (cost=1,630.88..1,699.97 rows=1 width=197) (actual time=29,594.202..29,596.449 rows=366 loops=1)

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

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

  • Group Key: ppp_1.personid, ppp_1.ptoplanid
12. 1.523 1.523 ↑ 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.012..1.523 rows=8,693 loops=1)

  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 5
13. 1.102 29,590.234 ↓ 183.0 366 1

Hash (cost=1,241.96..1,241.96 rows=2 width=172) (actual time=29,590.234..29,590.234 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 87kB
14. 1.032 29,589.132 ↓ 183.0 366 1

Nested Loop Left Join (cost=400.89..1,241.96 rows=2 width=172) (actual time=51.801..29,589.132 rows=366 loops=1)

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

Nested Loop Left Join (cost=400.62..1,239.81 rows=2 width=176) (actual time=51.769..29,584.440 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.634 15.287 ↓ 183.0 366 1

Nested Loop Left Join (cost=6.20..225.46 rows=2 width=172) (actual time=0.111..15.287 rows=366 loops=1)

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

Nested Loop (cost=5.92..224.76 rows=2 width=172) (actual time=0.104..12.823 rows=366 loops=1)

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

Nested Loop (cost=5.63..221.67 rows=6 width=147) (actual time=0.096..9.881 rows=366 loops=1)

  • Join Filter: ((a.asofdate >= pp.effectivedate) AND (a.asofdate <= pp.enddate) AND (ppp.personid = pp.personid))
  • Rows Removed by Join Filter: 173
19. 0.355 6.055 ↓ 8.7 366 1

Nested Loop (cost=5.35..205.59 rows=42 width=122) (actual time=0.087..6.055 rows=366 loops=1)

  • Join Filter: (ppp.ptoplanid = ppas.ptoplanid)
20. 0.003 0.097 ↑ 1.0 1 1

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

21. 0.009 0.089 ↑ 1.0 1 1

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

22. 0.054 0.054 ↑ 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.024..0.054 rows=1 loops=1)

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

Bitmap Heap Scan on pto_plan_desc ppd (cost=4.20..13.77 rows=1 width=40) (actual time=0.026..0.026 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
24. 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)
25. 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
26. 0.467 5.603 ↓ 8.7 366 1

Nested Loop (cost=0.57..129.95 rows=42 width=68) (actual time=0.021..5.603 rows=366 loops=1)

27. 1.476 1.476 ↓ 8.7 366 1

Index Scan using personptoplansx on person_pto_plans ppp (cost=0.29..108.29 rows=42 width=44) (actual time=0.009..1.476 rows=366 loops=1)

  • Index Cond: ((ptoplanid = ppd.ptoplanid) AND (a.asofdate <= enddate))
  • Filter: ((a.asofdate >= effectivedate) AND (now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 469
28. 3.660 3.660 ↑ 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.009..0.010 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))
29. 2.928 2.928 ↑ 1.0 1 366

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

  • Index Cond: (personid = pc.personid)
  • Filter: ((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. 7,571.808 29,403.342 ↓ 323.5 3,235 366

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

33. 447.618 15,911.484 ↓ 359.4 3,235 366

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

34. 582.672 1,255.746 ↓ 68.8 3,235 366

Hash Join (cost=393.98..463.18 rows=47 width=33) (actual time=0.061..3.431 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. 656.532 658.068 ↓ 2.3 5,761 366

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

  • Group Key: ppp_1_1.personid, ppp_1_1.ptoplanid
36. 1.536 1.536 ↑ 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.536 rows=8,693 loops=1)

  • Filter: ((now() >= createts) AND (now() <= endts))
  • Rows Removed by Filter: 5
37. 2.196 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.810 ↓ 1.1 37 366

Nested Loop (cost=0.56..4.66 rows=34 width=12) (actual time=0.014..0.035 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. 9.150 9.150 ↓ 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.009..0.025 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.660 3.660 ↓ 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.010 rows=14 loops=366)

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

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

45. 0.366 2.196 ↑ 1.0 1 366

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

46. 1.464 1.464 ↑ 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.004..0.004 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. 1.830 1.830 ↑ 1.0 1 366

Index Scan using personemploymentenddate on person_employment pe2 (cost=0.29..0.50 rows=1 width=25) (actual time=0.005..0.005 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. 5.490 5.490 ↓ 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.015 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. 1.830 1.830 ↑ 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.005..0.005 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. 5.490 5.490 ↑ 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.015..0.015 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=381.47..381.52 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.125 1.464 ↓ 0.0 0 366

Sort (cost=381.47..381.47 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.000 1.339 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

65. 0.001 1.319 ↓ 0.0 0 1

Merge Join (cost=1.63..51.42 rows=1 width=8) (actual time=1.319..1.319 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.015 1.318 ↓ 0.0 0 1

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

67. 0.043 0.043 ↑ 1.0 42 1

Index Scan using pk_companyname on companyname cn (cost=0.14..17.83 rows=43 width=13) (actual time=0.007..0.043 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.000 1.260 ↓ 0.0 0 42

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

69. 1.270 1.270 ↓ 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.270..1.270 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..58.42 rows=160 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 : 147.354 ms
Execution time : 29,632.786 ms