explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bs8Q : q1-323

Settings
# exclusive inclusive rows x rows loops node
1. 3.591 6,527.049 ↓ 12,679.0 88,753 1

Limit (cost=20,000,605,272.70..20,000,605,273.08 rows=7 width=183) (actual time=6,462.090..6,527.049 rows=88,753 loops=1)

2.          

CTE rppd

3. 2,074.172 2,074.185 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail (cost=2.74..261,407.11 rows=372,532 width=34) (actual time=0.031..2,074.185 rows=794,966 loops=1)

  • Index Cond: (customerid = $0)
  • Filter: ((amount <> '0'::numeric) AND (payamountsource = 'DB'::text))
  • Rows Removed by Filter: 5293413
4.          

Initplan (forIndex Scan)

5. 0.002 0.013 ↑ 1.0 1 1

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

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

  • Index Cond: (companyid = 1596)
8.          

Initplan (forLimit)

9. 0.002 0.016 ↑ 1.0 1 1

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

10. 0.004 0.014 ↑ 1.0 1 1

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

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

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

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

13. 0.004 0.006 ↑ 1.0 1 1

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

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

  • Index Cond: (companyid = 1596)
15. 52.338 6,523.435 ↓ 12,679.0 88,753 1

GroupAggregate (cost=20,000,343,860.96..20,000,343,861.35 rows=7 width=183) (actual time=6,462.089..6,523.435 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. 304.573 6,471.097 ↓ 13,630.6 95,414 1

Sort (cost=20,000,343,860.96..20,000,343,860.98 rows=7 width=163) (actual time=6,462.078..6,471.097 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. 155.903 6,166.524 ↓ 13,630.6 95,414 1

Hash Right Join (cost=20,000,331,753.49..20,000,343,860.86 rows=7 width=163) (actual time=5,939.957..6,166.524 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. 73.680 73.680 ↓ 2.1 794,966 1

CTE Scan on rppd rppd_1 (cost=0.00..7,450.64 rows=372,532 width=66) (actual time=0.008..73.680 rows=794,966 loops=1)

19. 37.074 5,936.941 ↓ 13,622.9 95,360 1

Hash (cost=20,000,331,753.37..20,000,331,753.37 rows=7 width=117) (actual time=5,936.941..5,936.941 rows=95,360 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11975kB
20. 108.297 5,899.867 ↓ 13,622.9 95,360 1

Merge Left Join (cost=20,000,326,165.29..20,000,331,753.37 rows=7 width=117) (actual time=5,703.758..5,899.867 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. 43.789 5,100.939 ↓ 13,594.7 95,163 1

Sort (cost=20,000,275,510.40..20,000,275,510.41 rows=7 width=95) (actual time=5,096.866..5,100.939 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. 131.571 5,057.150 ↓ 13,594.7 95,163 1

Hash Right Join (cost=20,000,250,511.69..20,000,275,510.30 rows=7 width=95) (actual time=4,749.735..5,057.150 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. 207.924 4,241.142 ↓ 1.3 761,578 1

Unique (cost=151,049.10..167,714.82 rows=606,026 width=78) (actual time=3,950.154..4,241.142 rows=761,578 loops=1)

24. 691.071 4,033.218 ↓ 1.4 854,753 1

Sort (cost=151,049.10..152,564.17 rows=606,026 width=78) (actual time=3,950.153..4,033.218 rows=854,753 loops=1)

  • Sort Key: rppd_2.payamountsourcepid, (COALESCE(pbeh.activepid, rppd_2.payamountsourcepid)), rppd_2.payamountsource, rppd_2.payprocessid, rppd_2.checkdate, dpp.payyear, dpp.payperiod, pbeh.coverageamount, pbeh.monthlyamount, pbeh.monthlyemployeramount
  • Sort Method: external merge Disk: 48080kB
25. 182.448 3,342.147 ↓ 1.4 854,753 1

Merge Right Join (cost=47,553.38..77,455.28 rows=606,026 width=78) (actual time=3,024.155..3,342.147 rows=854,753 loops=1)

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

27. 415.484 2,891.603 ↓ 2.3 854,753 1

Sort (cost=47,552.96..48,484.29 rows=372,532 width=56) (actual time=2,819.551..2,891.603 rows=854,753 loops=1)

  • Sort Key: rppd_2.payamountsourcepid
  • Sort Method: external sort Disk: 32680kB
28. 141.952 2,476.119 ↓ 2.1 794,966 1

Hash Join (cost=507.74..13,080.70 rows=372,532 width=56) (actual time=2.169..2,476.119 rows=794,966 loops=1)

  • Hash Cond: (rppd_2.payprocessid = dpp.payprocessid)
29. 2,332.049 2,332.049 ↓ 2.1 794,966 1

CTE Scan on rppd rppd_2 (cost=0.00..7,450.64 rows=372,532 width=48) (actual time=0.033..2,332.049 rows=794,966 loops=1)

30. 0.838 2.118 ↑ 1.0 8,833 1

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

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

32. 17.806 684.437 ↓ 9,991.6 69,941 1

Hash (cost=20,000,099,462.50..20,000,099,462.50 rows=7 width=107) (actual time=684.437..684.437 rows=69,941 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9933kB
33. 24.875 666.631 ↓ 9,991.6 69,941 1

Merge Join (cost=20,000,099,349.69..20,000,099,462.50 rows=7 width=107) (actual time=634.549..666.631 rows=69,941 loops=1)

  • Merge Cond: (fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey)
34. 16.571 563.591 ↓ 132.4 70,031 1

Sort (cost=20,000,079,029.17..20,000,079,030.49 rows=529 width=30) (actual time=560.701..563.591 rows=70,031 loops=1)

  • Sort Key: fbt.personbeneelectionkey
  • Sort Method: quicksort Memory: 8544kB
35. 22.506 547.020 ↓ 132.4 70,031 1

Nested Loop (cost=20,000,019,340.17..20,000,079,005.24 rows=529 width=30) (actual time=396.092..547.020 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
36. 0.003 0.492 ↑ 1.0 1 1

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

37. 0.414 0.414 ↑ 1.0 1 1

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

  • Filter: ((datevalue >= '2019-06-04'::date) AND (datevalue <= '2019-06-04'::date))
  • Rows Removed by Filter: 3099
38. 0.075 0.075 ↑ 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.012..0.075 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
39. 30.240 524.022 ↓ 161.7 227,887 1

Hash Join (cost=19,339.89..78,615.94 rows=1,409 width=38) (actual time=395.435..524.022 rows=227,887 loops=1)

  • Hash Cond: (fbt.personid = edw_positiondesc_reportdomain_access_data.personid)
40. 37.149 139.938 ↓ 80.9 227,887 1

Hash Join (cost=6,835.14..66,088.13 rows=2,817 width=42) (actual time=41.582..139.938 rows=227,887 loops=1)

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

Index Scan using ix_fbt_custkey on fact_benefits_transact fbt (cost=0.68..58,639.99 rows=233,774 width=36) (actual time=0.011..61.223 rows=235,596 loops=1)

  • Index Cond: (customerkey = edw.get_customer_key(1596))
42. 1.093 41.566 ↓ 4.5 8,179 1

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

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

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

  • Filter: (customerid = $3)
  • Rows Removed by Filter: 141099
44. 25.619 353.844 ↓ 1,340.6 268,113 1

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

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13522kB
45. 157.654 328.225 ↓ 1,340.6 268,113 1

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

  • Group Key: edw_positiondesc_reportdomain_access_data.personid
46. 170.571 170.571 ↑ 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=98.943..170.571 rows=679,158 loops=1)

47. 36.554 78.165 ↓ 5.1 113,252 1

Sort (cost=20,320.45..20,375.53 rows=22,033 width=77) (actual time=73.843..78.165 rows=113,252 loops=1)

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

Index Scan using ix_dimpbe_custid on dim_person_bene_election (cost=0.43..18,731.06 rows=22,033 width=77) (actual time=0.030..41.611 rows=113,432 loops=1)

  • Index Cond: (customerid = $2)
49. 50.336 690.631 ↓ 2.1 797,542 1

Materialize (cost=50,654.90..52,517.56 rows=372,532 width=66) (actual time=538.249..690.631 rows=797,542 loops=1)

50. 528.997 640.295 ↓ 2.1 794,942 1

Sort (cost=50,654.90..51,586.23 rows=372,532 width=66) (actual time=538.244..640.295 rows=794,942 loops=1)

  • Sort Key: rppd.payamountsourcepid, rppd.payprocessid, rppd.edtcode
  • Sort Method: external merge Disk: 28456kB
51. 111.298 111.298 ↓ 2.1 794,966 1

CTE Scan on rppd (cost=0.00..7,450.64 rows=372,532 width=66) (actual time=0.031..111.298 rows=794,966 loops=1)

Planning time : 3.101 ms
Execution time : 6,571.911 ms