explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UjgV : q0-323

Settings
# exclusive inclusive rows x rows loops node
1. 3.893 48,879.051 ↓ 12,679.0 88,753 1

Limit (cost=20,001,142,911.33..20,001,142,911.71 rows=7 width=183) (actual time=48,812.469..48,879.051 rows=88,753 loops=1)

2.          

Initplan (forLimit)

3. 0.001 0.013 ↑ 1.0 1 1

Limit (cost=2.30..2.31 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)

4. 0.003 0.012 ↑ 1.0 1 1

Sort (cost=2.30..2.31 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Sort Key: dim_company.startdate DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.009 0.009 ↑ 1.0 1 1

Index Scan using ix_dimcomp_compid on dim_company (cost=0.28..2.29 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
6. 0.002 0.024 ↑ 1.0 1 1

Limit (cost=2.30..2.31 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=1)

7. 0.010 0.022 ↑ 1.0 1 1

Sort (cost=2.30..2.31 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=1)

  • Sort Key: dim_company_1.startdate DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.012 0.012 ↑ 1.0 1 1

Index Scan using ix_dimcomp_compid on dim_company dim_company_1 (cost=0.28..2.29 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
9. 0.002 0.026 ↑ 1.0 1 1

Limit (cost=2.30..2.31 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=1)

10. 0.004 0.024 ↑ 1.0 1 1

Sort (cost=2.30..2.31 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=1)

  • Sort Key: dim_company_2.startdate DESC
  • Sort Method: quicksort Memory: 25kB
11. 0.020 0.020 ↑ 1.0 1 1

Index Scan using ix_dimcomp_compid on dim_company dim_company_2 (cost=0.28..2.29 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
12. 0.002 0.022 ↑ 1.0 1 1

Limit (cost=2.30..2.31 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=1)

13. 0.007 0.020 ↑ 1.0 1 1

Sort (cost=2.30..2.31 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Sort Key: dim_company_3.startdate DESC
  • Sort Method: quicksort Memory: 25kB
14. 0.013 0.013 ↑ 1.0 1 1

Index Scan using ix_dimcomp_compid on dim_company dim_company_3 (cost=0.28..2.29 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
15. 53.874 48,875.073 ↓ 12,679.0 88,753 1

GroupAggregate (cost=20,001,142,902.09..20,001,142,902.47 rows=7 width=183) (actual time=48,812.468..48,875.073 rows=88,753 loops=1)

  • Group Key: ((((dim_person.lastname)::text || ', '::text) || (dim_person.firstname)::text)), ((dim_person_bene_election.benefitsubclassdesc || CASE WHEN (dim_person_bene_election.benefitelection = 'P'::text) THEN ' (Pending)'::text ELSE ''::text END)), dim_person_bene_election.benefitplandesc, dim_person_bene_election.benefitelection, dim_person_bene_election.monthlyamount, dim_person_bene_election.monthlyemployeramount, dim_person_bene_election.edtcodeee, dim_person_bene_election.edtcodeer, rppd_2.checkdate
16. 296.051 48,821.199 ↓ 13,630.6 95,414 1

Sort (cost=20,001,142,902.09..20,001,142,902.10 rows=7 width=129) (actual time=48,812.453..48,821.199 rows=95,414 loops=1)

  • Sort Key: ((((dim_person.lastname)::text || ', '::text) || (dim_person.firstname)::text)), ((dim_person_bene_election.benefitsubclassdesc || CASE WHEN (dim_person_bene_election.benefitelection = 'P'::text) THEN ' (Pending)'::text ELSE ''::text END)), dim_person_bene_election.benefitplandesc, dim_person_bene_election.benefitelection, dim_person_bene_election.monthlyamount, dim_person_bene_election.monthlyemployeramount, dim_person_bene_election.edtcodeee, dim_person_bene_election.edtcodeer, rppd_2.checkdate
  • Sort Method: quicksort Memory: 17206kB
17. 179.899 48,525.148 ↓ 13,630.6 95,414 1

Hash Right Join (cost=20,000,844,696.91..20,001,142,901.99 rows=7 width=129) (actual time=46,332.940..48,525.148 rows=95,414 loops=1)

  • Hash Cond: ((rppd_1.payamountsourcepid = rppd_2.payamountsourcepid) AND (rppd_1.payprocessid = rppd_2.payprocessid) AND (rppd_1.edtcode = (dim_person_bene_election.edtcodeer)::text))
18. 2,058.241 2,058.241 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail rppd_1 (cost=0.43..293,548.43 rows=372,560 width=23) (actual time=0.043..2,058.241 rows=794,966 loops=1)

  • Index Cond: (customerid = $3)
  • Filter: ((amount <> '0'::numeric) AND (payamountsource = 'DB'::text))
  • Rows Removed by Filter: 5293413
19. 35.752 46,287.008 ↓ 13,622.9 95,360 1

Hash (cost=20,000,844,696.35..20,000,844,696.35 rows=7 width=100) (actual time=46,287.008..46,287.008 rows=95,360 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11975kB
20. 85.116 46,251.256 ↓ 13,622.9 95,360 1

Merge Left Join (cost=20,000,840,039.26..20,000,844,696.35 rows=7 width=100) (actual time=46,129.083..46,251.256 rows=95,360 loops=1)

  • Merge Cond: ((rppd_2.payamountsourcepid = rppd.payamountsourcepid) AND (rppd_2.payprocessid = rppd.payprocessid) AND (((dim_person_bene_election.edtcodeee)::text) = rppd.edtcode))
21. 40.088 43,521.457 ↓ 13,594.7 95,163 1

Sort (cost=20,000,512,015.78..20,000,512,015.79 rows=7 width=95) (actual time=43,517.676..43,521.457 rows=95,163 loops=1)

  • Sort Key: rppd_2.payamountsourcepid, rppd_2.payprocessid, ((dim_person_bene_election.edtcodeee)::text)
  • Sort Method: quicksort Memory: 17464kB
22. 123.237 43,481.369 ↓ 13,594.7 95,163 1

Hash Right Join (cost=20,000,488,797.61..20,000,512,015.68 rows=7 width=95) (actual time=43,190.207..43,481.369 rows=95,163 loops=1)

  • Hash Cond: ((COALESCE(pbeh.activepid, rppd_2.payamountsourcepid)) = dim_person_bene_election.personbeneelectionpid)
  • Join Filter: ((fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey) AND (sdater.datevalue <= rppd_2.checkdate) AND (rppd_2.checkdate <= edater.datevalue))
  • Rows Removed by Join Filter: 279836
23. 199.269 9,102.948 ↓ 1.3 761,578 1

Unique (cost=403,264.95..418,244.32 rows=599,175 width=49) (actual time=8,823.436..9,102.948 rows=761,578 loops=1)

24.          

Initplan (forUnique)

25. 0.002 0.014 ↑ 1.0 1 1

Limit (cost=2.30..2.31 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

26. 0.003 0.012 ↑ 1.0 1 1

Sort (cost=2.30..2.31 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Sort Key: dim_company_4.startdate DESC
  • Sort Method: quicksort Memory: 25kB
27. 0.009 0.009 ↑ 1.0 1 1

Index Scan using ix_dimcomp_compid on dim_company dim_company_4 (cost=0.28..2.29 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
28. 573.522 8,903.665 ↓ 1.4 854,753 1

Sort (cost=403,262.64..404,760.57 rows=599,175 width=49) (actual time=8,823.435..8,903.665 rows=854,753 loops=1)

  • Sort Key: rppd_2.payamountsourcepid, (COALESCE(pbeh.activepid, rppd_2.payamountsourcepid)), rppd_2.payprocessid, rppd_2.checkdate, dpp.payyear, dpp.payperiod, pbeh.coverageamount, pbeh.monthlyamount, pbeh.monthlyemployeramount
  • Sort Method: external merge Disk: 48080kB
29. 134.180 8,330.143 ↓ 1.4 854,753 1

Hash Join (cost=296,022.17..334,059.95 rows=599,175 width=49) (actual time=7,907.252..8,330.143 rows=854,753 loops=1)

  • Hash Cond: (rppd_2.payprocessid = dpp.payprocessid)
30. 156.166 8,187.176 ↓ 1.4 854,753 1

Merge Right Join (cost=295,514.42..325,313.55 rows=599,175 width=41) (actual time=7,898.409..8,187.176 rows=854,753 loops=1)

  • Merge Cond: (pbeh.personbeneelectionpid = rppd_2.payamountsourcepid)
31. 287.035 287.035 ↑ 1.0 556,086 1

Index Scan using ix_dimpbeh_pbepid on dim_person_bene_election_history pbeh (cost=0.42..19,421.72 rows=556,086 width=30) (actual time=0.012..287.035 rows=556,086 loops=1)

32. 427.422 7,743.975 ↓ 2.3 854,753 1

Sort (cost=295,514.00..296,445.40 rows=372,560 width=19) (actual time=7,675.771..7,743.975 rows=854,753 loops=1)

  • Sort Key: rppd_2.payamountsourcepid
  • Sort Method: external sort Disk: 26456kB
33. 7,316.553 7,316.553 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail rppd_2 (cost=0.43..261,038.95 rows=372,560 width=19) (actual time=0.046..7,316.553 rows=794,966 loops=1)

  • Index Cond: (customerid = $4)
  • Filter: ((amount <> '0'::numeric) AND (payamountsource = 'DB'::text))
  • Rows Removed by Filter: 5293413
34. 1.127 8.787 ↑ 1.0 8,833 1

Hash (cost=397.33..397.33 rows=8,833 width=12) (actual time=8.787..8.787 rows=8,833 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 508kB
35. 7.660 7.660 ↑ 1.0 8,833 1

Seq Scan on dim_pay_process dpp (cost=0.00..397.33 rows=8,833 width=12) (actual time=0.008..7.660 rows=8,833 loops=1)

36. 21.166 34,255.184 ↓ 9,991.6 69,941 1

Hash (cost=20,000,085,532.58..20,000,085,532.58 rows=7 width=107) (actual time=34,255.184..34,255.184 rows=69,941 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9933kB
37. 26.389 34,234.018 ↓ 9,991.6 69,941 1

Merge Join (cost=20,000,085,419.74..20,000,085,532.58 rows=7 width=107) (actual time=34,200.178..34,234.018 rows=69,941 loops=1)

  • Merge Cond: (fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey)
38. 18.885 34,105.464 ↓ 130.9 70,031 1

Sort (cost=20,000,065,092.81..20,000,065,094.14 rows=535 width=30) (actual time=34,102.379..34,105.464 rows=70,031 loops=1)

  • Sort Key: fbt.personbeneelectionkey
  • Sort Method: quicksort Memory: 8544kB
39. 23.197 34,086.579 ↓ 130.9 70,031 1

Nested Loop (cost=20,000,006,852.67..20,000,065,068.56 rows=535 width=30) (actual time=33,887.766..34,086.579 rows=70,031 loops=1)

  • Join Filter: (((fbt.startdatekey <= sdater.datekey) AND (sdater.datekey <= fbt.enddatekey)) OR ((fbt.startdatekey <= edater.datekey) AND (edater.datekey <= fbt.enddatekey)) OR ((sdater.datekey <= fbt.startdatekey) AND (fbt.startdatekey <= edater.datekey)) OR ((sdater.datekey <= fbt.enddatekey) AND (fbt.enddatekey <= edater.datekey)))
  • Rows Removed by Join Filter: 157856
40. 0.003 2.926 ↑ 1.0 1 1

Nested Loop (cost=10,000,000,000.28..10,000,000,347.03 rows=1 width=24) (actual time=1.900..2.926 rows=1 loops=1)

41. 1.364 1.364 ↑ 1.0 1 1

Seq Scan on dim_date sdater (cost=0.00..236.50 rows=1 width=12) (actual time=1.269..1.364 rows=1 loops=1)

  • Filter: ((datevalue >= '2019-06-04'::date) AND (datevalue <= '2019-06-04'::date))
  • Rows Removed by Filter: 3099
42. 1.559 1.559 ↑ 1.0 1 1

Index Scan using dim_date_pkey on dim_date edater (cost=0.28..110.52 rows=1 width=12) (actual time=0.628..1.559 rows=1 loops=1)

  • Index Cond: (sdater.datekey <= datekey)
  • Filter: ((datevalue >= '2019-07-05'::date) AND (datevalue <= '2019-07-05'::date))
  • Rows Removed by Filter: 388
43. 32.562 34,060.456 ↓ 159.9 227,887 1

Hash Join (cost=6,852.39..64,678.78 rows=1,425 width=38) (actual time=33,885.513..34,060.456 rows=227,887 loops=1)

  • Hash Cond: (fbt.personid = edw_positiondesc_reportdomain_access_data.personid)
44. 37.452 225.778 ↓ 80.0 227,887 1

Hash Join (cost=6,835.14..64,638.19 rows=2,850 width=42) (actual time=83.392..225.778 rows=227,887 loops=1)

  • Hash Cond: (fbt.personkey = dim_person.personkey)
45. 105.027 105.027 ↑ 1.0 235,596 1

Index Scan using ix_fbt_custkey on fact_benefits_transact fbt (cost=0.68..57,182.93 rows=236,487 width=36) (actual time=0.080..105.027 rows=235,596 loops=1)

  • Index Cond: (customerkey = edw.get_customer_key(1596))
46. 1.258 83.299 ↓ 4.5 8,179 1

Hash (cost=6,811.98..6,811.98 rows=1,799 width=22) (actual time=83.299..83.299 rows=8,179 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 527kB
47. 82.041 82.041 ↓ 4.5 8,179 1

Seq Scan on dim_person (cost=0.00..6,811.98 rows=1,799 width=22) (actual time=31.955..82.041 rows=8,179 loops=1)

  • Filter: (customerid = $1)
  • Rows Removed by Filter: 141099
48. 25.782 33,802.116 ↓ 1,340.6 268,114 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=33,802.116..33,802.116 rows=268,114 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13522kB
49. 157.295 33,776.334 ↓ 1,340.6 268,114 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=33,729.974..33,776.334 rows=268,114 loops=1)

  • Group Key: edw_positiondesc_reportdomain_access_data.personid
50. 33,619.039 33,619.039 ↓ 679.2 679,159 1

Function Scan on edw_positiondesc_reportdomain_access_data (cost=0.25..10.25 rows=1,000 width=4) (actual time=33,549.015..33,619.039 rows=679,159 loops=1)

51. 42.436 102.165 ↓ 5.1 113,252 1

Sort (cost=20,326.87..20,381.95 rows=22,031 width=77) (actual time=97.794..102.165 rows=113,252 loops=1)

  • Sort Key: dim_person_bene_election.personbeneelectionkey
  • Sort Method: quicksort Memory: 18209kB
52. 59.729 59.729 ↓ 5.1 113,432 1

Index Scan using ix_dimpbe_custid on dim_person_bene_election (cost=0.43..18,737.64 rows=22,031 width=77) (actual time=0.045..59.729 rows=113,432 loops=1)

  • Index Cond: (customerid = $0)
53. 605.371 2,644.683 ↓ 2.1 797,542 1

Sort (cost=328,023.48..328,954.88 rows=372,560 width=23) (actual time=2,576.051..2,644.683 rows=797,542 loops=1)

  • Sort Key: rppd.payamountsourcepid, rppd.payprocessid, rppd.edtcode
  • Sort Method: external sort Disk: 31576kB
54. 2,039.312 2,039.312 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail rppd (cost=0.43..293,548.43 rows=372,560 width=23) (actual time=0.046..2,039.312 rows=794,966 loops=1)

  • Index Cond: (customerid = $2)
  • Filter: ((amount <> '0'::numeric) AND (payamountsource = 'DB'::text))
  • Rows Removed by Filter: 5293413
Planning time : 22.316 ms
Execution time : 48,910.710 ms