explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4en : q2-323

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

Limit (cost=20,000,640,676.28..20,000,640,676.67 rows=7 width=183) (actual time=6,757.341..6,828.556 rows=88,753 loops=1)

2.          

CTE rppd

3. 637.517 2,863.988 ↓ 2.1 794,966 1

Sort (cost=295,879.37..296,810.70 rows=372,532 width=34) (actual time=2,743.834..2,863.988 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.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)

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,226.457 2,226.457 ↓ 2.1 794,966 1

Index Scan using ix_rppd_custid on rpt_pay_process_detail (cost=0.43..261,404.80 rows=372,532 width=34) (actual time=0.033..2,226.457 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.001 0.015 ↑ 1.0 1 1

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

11. 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
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.009..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.007..0.007 rows=1 loops=1)

14. 0.003 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.003 0.003 ↑ 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.003..0.003 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
16. 56.769 6,824.616 ↓ 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,757.340..6,824.616 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
17. 317.323 6,767.847 ↓ 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,757.328..6,767.847 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
18. 178.634 6,450.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=6,260.262..6,450.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))
19. 68.613 68.613 ↓ 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..68.613 rows=794,966 loops=1)

20. 38.922 6,203.277 ↓ 13,622.9 95,360 1

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

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11975kB
21. 109.683 6,164.355 ↓ 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,983.300..6,164.355 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))
22. 43.999 5,655.457 ↓ 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,651.063..5,655.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
23. 133.351 5,611.458 ↓ 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=5,300.414..5,611.458 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
24. 211.563 4,734.768 ↓ 1.3 761,578 1

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

25. 539.107 4,523.205 ↓ 1.4 854,753 1

Sort (cost=151,049.10..152,564.17 rows=606,026 width=78) (actual time=4,439.923..4,523.205 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
26. 185.525 3,984.098 ↓ 1.4 854,753 1

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

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

28. 327.829 3,483.889 ↓ 2.3 854,753 1

Sort (cost=47,552.96..48,484.29 rows=372,532 width=56) (actual time=3,412.769..3,483.889 rows=854,753 loops=1)

  • Sort Key: rppd_2.payamountsourcepid
  • Sort Method: external sort Disk: 32680kB
29. 156.662 3,156.060 ↓ 2.1 794,966 1

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

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

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

31. 0.900 2.279 ↑ 1.0 8,833 1

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

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

33. 19.769 743.339 ↓ 9,991.6 69,941 1

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

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9933kB
34. 27.837 723.570 ↓ 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=687.841..723.570 rows=69,941 loops=1)

  • Merge Cond: (fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey)
35. 18.247 609.469 ↓ 132.4 70,031 1

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

  • Sort Key: fbt.personbeneelectionkey
  • Sort Method: quicksort Memory: 8544kB
36. 23.971 591.222 ↓ 132.4 70,031 1

Nested Loop (cost=20,000,019,340.17..20,000,079,005.24 rows=529 width=30) (actual time=429.456..591.222 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
37. 0.002 0.507 ↑ 1.0 1 1

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

38. 0.427 0.427 ↑ 1.0 1 1

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

  • Filter: ((datevalue >= '2019-06-04'::date) AND (datevalue <= '2019-06-04'::date))
  • Rows Removed by Filter: 3099
39. 0.078 0.078 ↑ 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.014..0.078 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
40. 34.218 566.744 ↓ 161.7 227,887 1

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

  • Hash Cond: (fbt.personid = edw_positiondesc_reportdomain_access_data.personid)
41. 39.115 148.377 ↓ 80.9 227,887 1

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

  • Hash Cond: (fbt.personkey = dim_person.personkey)
42. 64.661 64.661 ↓ 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..64.661 rows=235,596 loops=1)

  • Index Cond: (customerkey = edw.get_customer_key(1596))
43. 1.188 44.601 ↓ 4.5 8,179 1

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

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

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

  • Filter: (customerid = $3)
  • Rows Removed by Filter: 141099
45. 26.668 384.149 ↓ 1,340.6 268,113 1

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

  • Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13522kB
46. 177.339 357.481 ↓ 1,340.6 268,113 1

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

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

48. 42.781 86.264 ↓ 5.1 113,252 1

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

  • Sort Key: dim_person_bene_election.personbeneelectionkey
  • Sort Method: quicksort Memory: 18209kB
49. 43.483 43.483 ↓ 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.029..43.483 rows=113,432 loops=1)

  • Index Cond: (customerid = $2)
50. 51.088 399.215 ↓ 2.1 797,542 1

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

51. 240.809 348.127 ↓ 2.1 794,942 1

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

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

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

Planning time : 2.692 ms
Execution time : 6,875.415 ms