explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9AgM : q3-323

Settings
# exclusive inclusive rows x rows loops node
1. 3.812 5,812.248 ↓ 12,679.0 88,753 1

Limit (cost=20,000,596,499.83..20,000,596,500.28 rows=7 width=183) (actual time=5,724.923..5,812.248 rows=88,753 loops=1)

2.          

CTE rppd

3. 598.709 2,617.839 ↓ 2.1 794,966 1

Sort (cost=295,671.57..296,611.21 rows=375,853 width=34) (actual time=2,510.410..2,617.839 rows=794,966 loops=1)

  • Sort Key: rpt_pay_process_detail.payamountsourcepid, rpt_pay_process_detail.payprocessid, rpt_pay_process_detail.edtcode
  • Sort Method: external merge Disk: 40456kB
4.          

Initplan (forSort)

5. 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)

6. 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
7. 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)
8. 2,019.117 2,019.117 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail (cost=0.43..260,865.63 rows=375,853 width=34) (actual time=0.033..2,019.117 rows=794,966 loops=1)

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

Initplan (forLimit)

10. 0.002 0.015 ↑ 1.0 1 1

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

11. 0.003 0.013 ↑ 1.0 1 1

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

  • Sort Key: dim_company_1.startdate DESC
  • Sort Method: quicksort Memory: 25kB
12. 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)
13. 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)

14. 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
15. 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)
16. 75.905 5,808.414 ↓ 12,679.0 88,753 1

GroupAggregate (cost=20,000,299,884.00..20,000,299,884.46 rows=7 width=183) (actual time=5,724.923..5,808.414 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_1.checkdate
17. 287.261 5,732.509 ↓ 14,469.7 101,288 1

Sort (cost=20,000,299,884.00..20,000,299,884.02 rows=7 width=173) (actual time=5,724.909..5,732.509 rows=101,288 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_1.checkdate
  • Sort Method: quicksort Memory: 17845kB
18. 99.860 5,445.248 ↓ 14,469.7 101,288 1

Hash Left Join (cost=20,000,268,872.81..20,000,299,883.90 rows=7 width=173) (actual time=5,055.397..5,445.248 rows=101,288 loops=1)

  • Hash Cond: ((rppd_1.payamountsourcepid = rppd.payamountsourcepid) AND (rppd_1.payprocessid = rppd.payprocessid))
  • Join Filter: ((rppd.edtcode = (dim_person_bene_election.edtcodeee)::text) OR (rppd.edtcode = (dim_person_bene_election.edtcodeer)::text))
19. 117.566 5,110.449 ↓ 13,594.7 95,163 1

Hash Right Join (cost=20,000,251,312.96..20,000,277,257.88 rows=7 width=95) (actual time=4,820.165..5,110.449 rows=95,163 loops=1)

  • Hash Cond: ((COALESCE(pbeh.activepid, rppd_1.payamountsourcepid)) = dim_person_bene_election.personbeneelectionpid)
  • Join Filter: ((fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey) AND (sdater.datevalue <= rppd_1.checkdate) AND (rppd_1.checkdate <= edater.datevalue))
  • Rows Removed by Join Filter: 279836
20. 206.461 4,293.075 ↓ 1.2 761,578 1

Unique (cost=152,131.98..168,919.85 rows=610,468 width=78) (actual time=4,006.450..4,293.075 rows=761,578 loops=1)

21. 507.705 4,086.614 ↓ 1.4 854,753 1

Sort (cost=152,131.98..153,658.15 rows=610,468 width=78) (actual time=4,006.449..4,086.614 rows=854,753 loops=1)

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

Merge Right Join (cost=47,996.84..77,965.36 rows=610,468 width=78) (actual time=3,288.529..3,578.909 rows=854,753 loops=1)

  • Merge Cond: (pbeh.personbeneelectionpid = rppd_1.payamountsourcepid)
23. 244.235 244.235 ↑ 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.014..244.235 rows=556,086 loops=1)

24. 284.794 3,168.561 ↓ 2.3 854,753 1

Sort (cost=47,996.41..48,936.04 rows=375,853 width=56) (actual time=3,102.219..3,168.561 rows=854,753 loops=1)

  • Sort Key: rppd_1.payamountsourcepid
  • Sort Method: external sort Disk: 32680kB
25. 144.434 2,883.767 ↓ 2.1 794,966 1

Hash Join (cost=507.74..13,192.78 rows=375,853 width=56) (actual time=2,512.787..2,883.767 rows=794,966 loops=1)

  • Hash Cond: (rppd_1.payprocessid = dpp.payprocessid)
26. 2,737.020 2,737.020 ↓ 2.1 794,966 1

CTE Scan on rppd rppd_1 (cost=0.00..7,517.06 rows=375,853 width=48) (actual time=2,510.413..2,737.020 rows=794,966 loops=1)

27. 0.975 2.313 ↑ 1.0 8,833 1

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

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

29. 20.449 699.808 ↓ 9,991.6 69,941 1

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

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9933kB
30. 25.904 679.359 ↓ 9,991.6 69,941 1

Merge Join (cost=20,000,099,068.05..20,000,099,180.89 rows=7 width=107) (actual time=646.328..679.359 rows=69,941 loops=1)

  • Merge Cond: (fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey)
31. 19.228 571.864 ↓ 131.4 70,031 1

Sort (cost=20,000,078,735.25..20,000,078,736.58 rows=533 width=30) (actual time=569.016..571.864 rows=70,031 loops=1)

  • Sort Key: fbt.personbeneelectionkey
  • Sort Method: quicksort Memory: 8544kB
32. 21.705 552.636 ↓ 131.4 70,031 1

Nested Loop (cost=20,000,019,340.17..20,000,078,711.11 rows=533 width=30) (actual time=403.871..552.636 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
33. 0.002 0.524 ↑ 1.0 1 1

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

34. 0.442 0.442 ↑ 1.0 1 1

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

  • Filter: ((datevalue >= '2019-06-04'::date) AND (datevalue <= '2019-06-04'::date))
  • Rows Removed by Filter: 3099
35. 0.080 0.080 ↑ 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.013..0.080 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
36. 30.085 530.407 ↓ 160.5 227,887 1

Hash Join (cost=19,339.89..78,321.48 rows=1,420 width=38) (actual time=403.189..530.407 rows=227,887 loops=1)

  • Hash Cond: (fbt.personid = edw_positiondesc_reportdomain_access_data.personid)
37. 35.919 138.703 ↓ 80.3 227,887 1

Hash Join (cost=6,835.14..65,793.48 rows=2,839 width=42) (actual time=41.560..138.703 rows=227,887 loops=1)

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

Index Scan using ix_fbt_custkey on fact_benefits_transact fbt (cost=0.68..58,340.53 rows=235,604 width=36) (actual time=0.011..61.240 rows=235,596 loops=1)

  • Index Cond: (customerkey = edw.get_customer_key(1596))
39. 1.139 41.544 ↓ 4.5 8,179 1

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

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

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

  • Filter: (customerid = $3)
  • Rows Removed by Filter: 141099
41. 26.476 361.619 ↓ 1,340.6 268,113 1

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

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13522kB
42. 162.991 335.143 ↓ 1,340.6 268,113 1

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

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

44. 41.310 81.591 ↓ 5.1 113,252 1

Sort (cost=20,332.73..20,387.82 rows=22,035 width=77) (actual time=77.305..81.591 rows=113,252 loops=1)

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

Index Scan using ix_dimpbe_custid on dim_person_bene_election (cost=0.43..18,743.18 rows=22,035 width=77) (actual time=0.028..40.281 rows=113,432 loops=1)

  • Index Cond: (customerid = $2)
46. 129.476 234.939 ↓ 2.1 794,966 1

Hash (cost=7,517.06..7,517.06 rows=375,853 width=66) (actual time=234.939..234.939 rows=794,966 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 27485kB
47. 105.463 105.463 ↓ 2.1 794,966 1

CTE Scan on rppd (cost=0.00..7,517.06 rows=375,853 width=66) (actual time=0.020..105.463 rows=794,966 loops=1)

Planning time : 2.435 ms
Execution time : 5,851.209 ms