explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EI6H : q0-323

Settings
# exclusive inclusive rows x rows loops node
1. 3.953 17,566.930 ↓ 12,679.0 88,753 1

Limit (cost=20,001,155,350.69..20,001,155,351.08 rows=7 width=183) (actual time=17,494.082..17,566.930 rows=88,753 loops=1)

2.          

Initplan (forLimit)

3. 0.001 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)

4. 0.003 0.013 ↑ 1.0 1 1

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

  • Sort Key: dim_company.startdate DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
6. 0.001 0.019 ↑ 1.0 1 1

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

7. 0.011 0.018 ↑ 1.0 1 1

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

  • Sort Key: dim_company_1.startdate DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.007 0.007 ↑ 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.006..0.007 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
9. 0.001 0.017 ↑ 1.0 1 1

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

10. 0.004 0.016 ↑ 1.0 1 1

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

  • Sort Key: dim_company_2.startdate DESC
  • Sort Method: quicksort Memory: 25kB
11. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
12. 0.001 0.024 ↑ 1.0 1 1

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

13. 0.011 0.023 ↑ 1.0 1 1

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

  • Sort Key: dim_company_3.startdate DESC
  • Sort Method: quicksort Memory: 25kB
14. 0.012 0.012 ↑ 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.012 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
15. 58.117 17,562.903 ↓ 12,679.0 88,753 1

GroupAggregate (cost=20,001,155,341.45..20,001,155,341.84 rows=7 width=183) (actual time=17,494.081..17,562.903 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. 338.585 17,504.786 ↓ 13,630.6 95,414 1

Sort (cost=20,001,155,341.45..20,001,155,341.47 rows=7 width=129) (actual time=17,494.066..17,504.786 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. 207.164 17,166.201 ↓ 13,630.6 95,414 1

Hash Right Join (cost=20,000,856,827.80..20,001,155,341.36 rows=7 width=129) (actual time=14,822.368..17,166.201 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,185.907 2,185.907 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail rppd_1 (cost=0.43..293,882.71 rows=370,496 width=23) (actual time=0.047..2,185.907 rows=794,966 loops=1)

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

Hash (cost=20,000,856,827.24..20,000,856,827.24 rows=7 width=100) (actual time=14,773.130..14,773.130 rows=95,360 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11975kB
20. 92.786 14,731.217 ↓ 13,622.9 95,360 1

Merge Left Join (cost=20,000,852,195.94..20,000,856,827.24 rows=7 width=100) (actual time=14,597.168..14,731.217 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. 44.170 11,784.626 ↓ 13,594.7 95,163 1

Sort (cost=20,000,524,044.02..20,000,524,044.04 rows=7 width=95) (actual time=11,780.479..11,784.626 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. 132.447 11,740.456 ↓ 13,594.7 95,163 1

Hash Right Join (cost=20,000,500,941.95..20,000,524,043.92 rows=7 width=95) (actual time=11,432.414..11,740.456 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. 210.404 10,649.434 ↓ 1.3 761,578 1

Unique (cost=402,814.95..417,719.42 rows=596,179 width=49) (actual time=10,356.387..10,649.434 rows=761,578 loops=1)

24.          

Initplan (forUnique)

25. 0.001 0.012 ↑ 1.0 1 1

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

26. 0.003 0.011 ↑ 1.0 1 1

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

  • Sort Key: dim_company_4.startdate DESC
  • Sort Method: quicksort Memory: 25kB
27. 0.008 0.008 ↑ 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.007..0.008 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
28. 608.481 10,439.018 ↓ 1.4 854,753 1

Sort (cost=402,812.64..404,303.08 rows=596,179 width=49) (actual time=10,356.385..10,439.018 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. 143.315 9,830.537 ↓ 1.4 854,753 1

Hash Join (cost=296,025.36..333,977.02 rows=596,179 width=49) (actual time=9,381.507..9,830.537 rows=854,753 loops=1)

  • Hash Cond: (rppd_2.payprocessid = dpp.payprocessid)
30. 164.970 9,683.267 ↓ 1.4 854,753 1

Merge Right Join (cost=295,517.62..325,271.81 rows=596,179 width=41) (actual time=9,377.491..9,683.267 rows=854,753 loops=1)

  • Merge Cond: (pbeh.personbeneelectionpid = rppd_2.payamountsourcepid)
31. 307.073 307.073 ↑ 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..307.073 rows=556,086 loops=1)

32. 485.741 9,211.224 ↓ 2.3 854,753 1

Sort (cost=295,517.20..296,443.44 rows=370,496 width=19) (actual time=9,139.450..9,211.224 rows=854,753 loops=1)

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

Index Scan using ix_rppd_custid on rpt_pay_process_detail rppd_2 (cost=0.43..261,247.99 rows=370,496 width=19) (actual time=0.047..8,725.483 rows=794,966 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 508kB
35. 2.849 2.849 ↑ 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.009..2.849 rows=8,833 loops=1)

36. 23.110 958.575 ↓ 9,991.6 69,941 1

Hash (cost=20,000,098,126.92..20,000,098,126.92 rows=7 width=107) (actual time=958.575..958.575 rows=69,941 loops=1)

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

Merge Join (cost=20,000,098,014.09..20,000,098,126.92 rows=7 width=107) (actual time=899.418..935.465 rows=69,941 loops=1)

  • Merge Cond: (fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey)
38. 22.149 796.219 ↓ 131.4 70,031 1

Sort (cost=20,000,077,689.84..20,000,077,691.17 rows=533 width=30) (actual time=792.973..796.219 rows=70,031 loops=1)

  • Sort Key: fbt.personbeneelectionkey
  • Sort Method: quicksort Memory: 8544kB
39. 24.575 774.070 ↓ 131.4 70,031 1

Nested Loop (cost=20,000,019,340.17..20,000,077,665.70 rows=533 width=30) (actual time=570.026..774.070 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 4.128 ↑ 1.0 1 1

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

41. 2.879 2.879 ↑ 1.0 1 1

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

  • Filter: ((datevalue >= '2019-06-04'::date) AND (datevalue <= '2019-06-04'::date))
  • Rows Removed by Filter: 3099
42. 1.246 1.246 ↑ 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.615..1.246 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. 34.055 745.367 ↓ 160.6 227,887 1

Hash Join (cost=19,339.89..77,276.10 rows=1,419 width=38) (actual time=566.114..745.367 rows=227,887 loops=1)

  • Hash Cond: (fbt.personid = edw_positiondesc_reportdomain_access_data.personid)
44. 40.108 262.168 ↓ 80.3 227,887 1

Hash Join (cost=6,835.14..64,748.10 rows=2,839 width=42) (actual time=116.953..262.168 rows=227,887 loops=1)

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

Index Scan using ix_fbt_custkey on fact_benefits_transact fbt (cost=0.68..57,295.24 rows=235,571 width=36) (actual time=0.066..105.186 rows=235,596 loops=1)

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

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

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

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

  • Filter: (customerid = $1)
  • Rows Removed by Filter: 141099
48. 31.431 449.144 ↓ 1,340.6 268,113 1

Hash (cost=12,502.25..12,502.25 rows=200 width=4) (actual time=449.144..449.144 rows=268,113 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13522kB
49. 188.311 417.713 ↓ 1,340.6 268,113 1

HashAggregate (cost=12,500.25..12,502.25 rows=200 width=4) (actual time=368.171..417.713 rows=268,113 loops=1)

  • Group Key: edw_positiondesc_reportdomain_access_data.personid
50. 229.402 229.402 ↑ 1.5 679,158 1

Function Scan on edw_positiondesc_reportdomain_access_data (cost=0.25..10,000.25 rows=1,000,000 width=4) (actual time=152.774..229.402 rows=679,158 loops=1)

51. 45.370 111.331 ↓ 5.1 113,252 1

Sort (cost=20,324.18..20,379.26 rows=22,032 width=77) (actual time=106.439..111.331 rows=113,252 loops=1)

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

Index Scan using ix_dimpbe_custid on dim_person_bene_election (cost=0.43..18,734.87 rows=22,032 width=77) (actual time=0.052..65.961 rows=113,432 loops=1)

  • Index Cond: (customerid = $0)
53. 661.862 2,853.805 ↓ 2.2 797,542 1

Sort (cost=328,151.92..329,078.16 rows=370,496 width=23) (actual time=2,779.744..2,853.805 rows=797,542 loops=1)

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

Index Scan using ix_rppd_custid on rpt_pay_process_detail rppd (cost=0.43..293,882.71 rows=370,496 width=23) (actual time=0.039..2,191.943 rows=794,966 loops=1)

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