explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hs52 : q4-323

Settings
# exclusive inclusive rows x rows loops node
1. 4.075 2,062.993 ↓ 12,679.0 88,753 1

Limit (cost=30,000,281,293.35..30,000,281,293.81 rows=7 width=183) (actual time=1,966.739..2,062.993 rows=88,753 loops=1)

2.          

CTE DimDate_Ranges

3. 0.003 0.521 ↑ 1.0 1 1

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

4. 0.439 0.439 ↑ 1.0 1 1

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

  • Filter: ((datevalue >= '2019-06-04'::date) AND (datevalue <= '2019-06-04'::date))
  • Rows Removed by Filter: 3099
5. 0.079 0.079 ↑ 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.015..0.079 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
6.          

CTE rppd

7. 22.167 459.473 ↓ 1.3 53,346 1

Sort (cost=10,000,150,231.14..10,000,150,334.69 rows=41,422 width=34) (actual time=457.084..459.473 rows=53,346 loops=1)

  • Sort Key: rpt_pay_process_detail.payamountsourcepid, rpt_pay_process_detail.payprocessid, rpt_pay_process_detail.edtcode
  • Sort Method: quicksort Memory: 5704kB
8.          

Initplan (forSort)

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

10. 0.004 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
11. 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)
12. 3.737 437.293 ↓ 1.3 53,346 1

Nested Loop (cost=10,000,080,684.44..10,000,147,052.15 rows=41,422 width=34) (actual time=390.718..437.293 rows=53,346 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on "DimDate_Ranges" "DimDate_Ranges_1" (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

14. 44.088 433.555 ↓ 1.3 53,346 1

Bitmap Heap Scan on rpt_pay_process_detail (cost=80,684.44..146,637.91 rows=41,422 width=34) (actual time=390.711..433.555 rows=53,346 loops=1)

  • Recheck Cond: (("DimDate_Ranges_1".fromdate <= checkdate) AND (checkdate <= "DimDate_Ranges_1".asoftodate) AND (customerid = $2) AND (payamountsource = 'DB'::text))
  • Filter: (amount <> '0'::numeric)
  • Rows Removed by Filter: 50719
  • Heap Blocks: exact=9726
15. 2.739 389.467 ↓ 0.0 0 1

BitmapAnd (cost=80,684.44..80,684.44 rows=68,554 width=0) (actual time=389.467..389.467 rows=0 loops=1)

16. 19.383 19.383 ↑ 2.6 426,411 1

Bitmap Index Scan on ix_rppd_chkdt (cost=0.00..16,833.71 rows=1,087,527 width=0) (actual time=19.382..19.383 rows=426,411 loops=1)

  • Index Cond: (("DimDate_Ranges_1".fromdate <= checkdate) AND (checkdate <= "DimDate_Ranges_1".asoftodate))
17. 237.245 237.245 ↓ 2.5 6,088,379 1

Bitmap Index Scan on ix_rppd_custid (cost=0.00..31,667.46 rows=2,446,936 width=0) (actual time=237.245..237.245 rows=6,088,379 loops=1)

  • Index Cond: (customerid = $2)
18. 130.100 130.100 ↓ 1.0 2,471,990 1

Bitmap Index Scan on ix_rppd_payamtsrc (cost=0.00..31,986.03 rows=2,467,946 width=0) (actual time=130.100..130.100 rows=2,471,990 loops=1)

  • Index Cond: (payamountsource = 'DB'::text)
19.          

Initplan (forLimit)

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

21. 0.004 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
22. 0.009 0.009 ↑ 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.009 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
23. 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)

24. 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
25. 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.002..0.003 rows=1 loops=1)

  • Index Cond: (companyid = 1596)
26. 81.924 2,058.896 ↓ 12,679.0 88,753 1

GroupAggregate (cost=10,000,130,607.01..10,000,130,607.46 rows=7 width=183) (actual time=1,966.738..2,058.896 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
27. 327.119 1,976.972 ↓ 14,469.7 101,288 1

Sort (cost=10,000,130,607.01..10,000,130,607.03 rows=7 width=173) (actual time=1,966.726..1,976.972 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
28. 47.962 1,649.853 ↓ 14,469.7 101,288 1

Hash Left Join (cost=10,000,128,849.34..10,000,130,606.91 rows=7 width=173) (actual time=1,555.723..1,649.853 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))
29. 32.987 1,587.562 ↓ 13,594.7 95,163 1

Hash Right Join (cost=10,000,127,399.57..10,000,129,084.41 rows=7 width=95) (actual time=1,541.335..1,587.562 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 ("DimDate_Ranges".fromdate <= rppd_1.checkdate) AND (rppd_1.checkdate <= "DimDate_Ranges".asoftodate))
30. 32.342 807.249 ↑ 1.4 48,298 1

HashAggregate (cost=28,590.06..29,263.99 rows=67,393 width=78) (actual time=794.001..807.249 rows=48,298 loops=1)

  • Group 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
31. 61.919 774.907 ↑ 1.2 54,025 1

Merge Right Join (cost=5,082.84..26,905.24 rows=67,393 width=78) (actual time=678.685..774.907 rows=54,025 loops=1)

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

33. 8.040 484.956 ↓ 1.3 54,025 1

Sort (cost=5,082.41..5,185.97 rows=41,422 width=56) (actual time=482.658..484.956 rows=54,025 loops=1)

  • Sort Key: rppd_1.payamountsourcepid
  • Sort Method: quicksort Memory: 5704kB
34. 9.381 476.916 ↓ 1.3 53,346 1

Hash Join (cost=507.74..1,905.74 rows=41,422 width=56) (actual time=459.238..476.916 rows=53,346 loops=1)

  • Hash Cond: (rppd_1.payprocessid = dpp.payprocessid)
35. 465.406 465.406 ↓ 1.3 53,346 1

CTE Scan on rppd rppd_1 (cost=0.00..828.44 rows=41,422 width=48) (actual time=457.086..465.406 rows=53,346 loops=1)

36. 0.833 2.129 ↑ 1.0 8,833 1

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

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

38. 21.599 747.326 ↓ 9,991.6 69,941 1

Hash (cost=10,000,098,809.42..10,000,098,809.42 rows=7 width=107) (actual time=747.326..747.326 rows=69,941 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9933kB
39. 27.139 725.727 ↓ 9,991.6 69,941 1

Merge Join (cost=10,000,098,696.56..10,000,098,809.42 rows=7 width=107) (actual time=690.577..725.727 rows=69,941 loops=1)

  • Merge Cond: (fbt.personbeneelectionkey = dim_person_bene_election.personbeneelectionkey)
40. 20.608 611.198 ↓ 130.9 70,031 1

Sort (cost=10,000,078,363.48..10,000,078,364.82 rows=535 width=30) (actual time=607.937..611.198 rows=70,031 loops=1)

  • Sort Key: fbt.personbeneelectionkey
  • Sort Method: quicksort Memory: 8544kB
41. 23.097 590.590 ↓ 130.9 70,031 1

Nested Loop (cost=10,000,019,339.89..10,000,078,339.24 rows=535 width=30) (actual time=434.890..590.590 rows=70,031 loops=1)

  • Join Filter: (((fbt.startdatekey <= "DimDate_Ranges".fromdatekey) AND ("DimDate_Ranges".fromdatekey <= fbt.enddatekey)) OR ((fbt.startdatekey <= "DimDate_Ranges".asoftodatekey) AND ("DimDate_Ranges".asoftodatekey <= fbt.enddatekey)) OR (("DimDate_Ranges".fromdatekey <= fbt.startdatekey) AND (fbt.startdatekey <= "DimDate_Ranges".asoftodatekey)) OR (("DimDate_Ranges".fromdatekey <= fbt.enddatekey) AND (fbt.enddatekey <= "DimDate_Ranges".asoftodatekey)))
  • Rows Removed by Join Filter: 157856
42. 0.523 0.523 ↑ 1.0 1 1

CTE Scan on "DimDate_Ranges" (cost=0.00..0.02 rows=1 width=24) (actual time=0.430..0.523 rows=1 loops=1)

43. 31.915 566.970 ↓ 160.0 227,887 1

Hash Join (cost=19,339.89..78,296.50 rows=1,424 width=38) (actual time=434.202..566.970 rows=227,887 loops=1)

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

Hash Join (cost=6,835.14..65,768.43 rows=2,848 width=42) (actual time=57.650..158.513 rows=227,887 loops=1)

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

Index Scan using ix_fbt_custkey on fact_benefits_transact fbt (cost=0.68..58,313.70 rows=236,283 width=36) (actual time=0.011..63.234 rows=235,596 loops=1)

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

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

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

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

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

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

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

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

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

51. 44.391 87.390 ↓ 5.1 113,252 1

Sort (cost=20,333.01..20,388.10 rows=22,036 width=77) (actual time=82.633..87.390 rows=113,252 loops=1)

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

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

  • Index Cond: (customerid = $6)
53. 7.434 14.329 ↓ 1.3 53,346 1

Hash (cost=828.44..828.44 rows=41,422 width=66) (actual time=14.329..14.329 rows=53,346 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3639kB
54. 6.895 6.895 ↓ 1.3 53,346 1

CTE Scan on rppd (cost=0.00..828.44 rows=41,422 width=66) (actual time=0.002..6.895 rows=53,346 loops=1)

Planning time : 2.278 ms
Execution time : 2,072.399 ms