explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hQrm

Settings
# exclusive inclusive rows x rows loops node
1. 99,087.070 1,849,216.580 ↑ 33.3 227,989 1

Hash Right Join (cost=21,402,916.79..23,749,916.63 rows=7,587,203 width=832) (actual time=1,748,269.305..1,849,216.580 rows=227,989 loops=1)

  • Hash Cond: ((frdp.id = apcc.loan_app_id) AND (frdp.lir_borrower_id = apcc.lir_borrower_id))
  • Filter: (GREATEST(fborrower.update_date, (s_acc.change_date_time)::timestamp with time zone, (s_addr.change_date_time)::timestamp with time zone, (s_ph.change_date_time)::timestamp with time zone, frdp.fraud_point_update_date) > (now() - '5 days'::interval))
  • Rows Removed by Filter: 22,931,522
2.          

CTE frdp

3. 1,577.439 10,706.148 ↓ 53.6 163,515 1

GroupAggregate (cost=191,805.52..192,113.51 rows=3,052 width=440) (actual time=9,019.858..10,706.148 rows=163,515 loops=1)

  • Group Key: fp.id, fp.lir_borrower_id
4. 416.966 9,128.709 ↓ 205.1 632,270 1

Sort (cost=191,805.52..191,813.23 rows=3,083 width=86) (actual time=9,019.795..9,128.709 rows=632,270 loops=1)

  • Sort Key: fp.id, fp.lir_borrower_id
  • Sort Method: quicksort Memory: 113,489kB
5. 75.719 8,711.743 ↓ 205.1 632,270 1

Subquery Scan on fp (cost=166,962.54..191,626.86 rows=3,083 width=86) (actual time=8,181.297..8,711.743 rows=632,270 loops=1)

  • Filter: (fp.rownum = 1)
  • Rows Removed by Filter: 9,681
6. 377.820 8,636.024 ↓ 1.0 641,951 1

WindowAgg (cost=166,962.54..183,919.26 rows=616,608 width=110) (actual time=8,181.254..8,636.024 rows=641,951 loops=1)

7. 493.762 8,258.204 ↓ 1.0 641,951 1

Sort (cost=166,962.54..168,504.06 rows=616,608 width=77) (actual time=8,181.225..8,258.204 rows=641,951 loops=1)

  • Sort Key: frdv.fraud_review_data_point_group_id, frg.borrower_id, frg.verification_type, frdv.create_date DESC
  • Sort Method: quicksort Memory: 114,865kB
8. 501.324 7,764.442 ↓ 1.0 641,951 1

Hash Join (cost=70,198.77..107,663.37 rows=616,608 width=77) (actual time=5,169.130..7,764.442 rows=641,951 loops=1)

  • Hash Cond: (frd.id = frdv.fraud_review_data_point_id)
9. 2,140.796 2,140.796 ↑ 1.0 798,278 1

Seq Scan on fraud_review_data_point frd (cost=0.00..28,304.65 rows=798,365 width=22) (actual time=0.892..2,140.796 rows=798,278 loops=1)

10. 320.402 5,122.322 ↓ 1.0 641,951 1

Hash (cost=62,491.17..62,491.17 rows=616,608 width=71) (actual time=5,122.322..5,122.322 rows=641,951 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 75,474kB
11. 504.772 4,801.920 ↓ 1.0 641,951 1

Hash Join (cost=34,700.18..62,491.17 rows=616,608 width=71) (actual time=2,377.737..4,801.920 rows=641,951 loops=1)

  • Hash Cond: (frdv.fraud_review_data_point_group_id = frg.id)
12. 1,927.289 1,927.289 ↓ 1.0 767,898 1

Seq Scan on fraud_review_data_point_verification frdv (cost=0.00..18,745.48 rows=767,848 width=24) (actual time=2.004..1,927.289 rows=767,898 loops=1)

13. 300.827 2,369.859 ↑ 1.0 657,316 1

Hash (cost=26,227.71..26,227.71 rows=677,798 width=55) (actual time=2,369.859..2,369.859 rows=657,316 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 66,746kB
14. 2,069.032 2,069.032 ↑ 1.0 657,316 1

Seq Scan on fraud_review_data_point_group frg (cost=0.00..26,227.71 rows=677,798 width=55) (actual time=1.599..2,069.032 rows=657,316 loops=1)

  • Filter: ((verification_type)::text = ANY ('{NAME,ADDRESS,DATE_OF_BIRTH,SSN}'::text[]))
  • Rows Removed by Filter: 164,329
15. 10,901.049 10,901.049 ↓ 53.6 163,515 1

CTE Scan on frdp (cost=0.00..61.04 rows=3,052 width=440) (actual time=9,019.864..10,901.049 rows=163,515 loops=1)

16. 39,349.906 1,739,228.461 ↓ 1.0 23,159,511 1

Hash (cost=18,579,881.13..18,579,881.13 rows=22,761,610 width=795) (actual time=1,739,228.461..1,739,228.461 rows=23,159,511 loops=1)

  • Buckets: 524,288 Batches: 64 Memory Usage: 173,498kB
17. 11,645.667 1,699,878.555 ↓ 1.0 23,159,511 1

Hash Left Join (cost=9,542,422.76..18,579,881.13 rows=22,761,610 width=795) (actual time=908,846.236..1,699,878.555 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.loan_app_id = s_acc.account_number)
18. 6,675.549 1,686,735.883 ↓ 1.0 23,159,511 1

Hash Left Join (cost=9,399,656.30..18,346,165.05 rows=22,761,610 width=787) (actual time=907,340.623..1,686,735.883 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.lead_id = contact_d.lead_id)
19. 6,258.060 1,679,042.841 ↓ 1.0 23,159,511 1

Hash Left Join (cost=9,396,377.90..18,254,834.64 rows=22,761,610 width=704) (actual time=906,322.520..1,679,042.841 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.spectrum_customer_id = s_ph.customer_id)
20. 5,926.370 1,670,000.822 ↓ 1.0 23,159,511 1

Hash Left Join (cost=9,340,851.80..17,175,028.60 rows=22,761,610 width=672) (actual time=903,538.537..1,670,000.822 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.lir_borrower_id = lirborrower.id)
21. 6,330.433 1,662,811.555 ↓ 1.0 23,159,511 1

Hash Left Join (cost=9,242,778.08..16,988,991.63 rows=22,761,610 width=587) (actual time=902,270.275..1,662,811.555 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.spectrum_address_id = s_addr.address_id)
22. 176,734.912 1,655,590.375 ↓ 1.0 23,159,511 1

Hash Left Join (cost=9,221,251.76..16,880,669.48 rows=22,761,610 width=570) (actual time=901,371.054..1,655,590.375 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.loan_app_id = lga.loan_application_id)
23. 4,752.767 1,030,527.685 ↓ 1.0 23,159,511 1

Gather (cost=8,449,009.39..16,022,945.22 rows=22,761,610 width=472) (actual time=453,042.912..1,030,527.685 rows=23,159,511 loops=1)

  • Workers Planned: 4
  • Workers Launched: 0
24. 233,785.528 1,025,774.918 ↓ 4.1 23,159,511 1

Hash Left Join (cost=8,448,009.39..13,745,784.22 rows=5,690,402 width=472) (actual time=453,040.984..1,025,774.918 rows=23,159,511 loops=1)

  • Hash Cond: (apcc.partner_lead_id = pl.id)
25. 243,254.395 543,381.427 ↓ 4.1 23,159,511 1

Hash Join (cost=3,387,367.31..7,622,396.51 rows=5,690,402 width=390) (actual time=204,398.649..543,381.427 rows=23,159,511 loops=1)

  • Hash Cond: (fborrower.id = apcc.funnel_borrower_id)
26. 15,595.427 95,781.541 ↓ 4.1 23,164,249 1

Hash Join (cost=2.37..2,863,421.42 rows=5,690,402 width=350) (actual time=4.507..95,781.541 rows=23,164,249 loops=1)

  • Hash Cond: ((fborrower.state)::text = (state_abbr.state_long_name)::text)
27. 80,186.067 80,186.067 ↓ 4.1 23,164,249 1

Parallel Seq Scan on borrower fborrower (cost=0.00..2,785,176.02 rows=5,690,402 width=356) (actual time=4.431..80,186.067 rows=23,164,249 loops=1)

28. 0.027 0.047 ↑ 1.0 61 1

Hash (cost=1.61..1.61 rows=61 width=14) (actual time=0.047..0.047 rows=61 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
29. 0.020 0.020 ↑ 1.0 61 1

Seq Scan on state_abbreviation state_abbr (cost=0.00..1.61 rows=61 width=14) (actual time=0.007..0.020 rows=61 loops=1)

30. 8,277.075 204,345.491 ↑ 2.1 23,159,511 1

Hash (cost=2,346,825.64..2,346,825.64 rows=48,876,664 width=48) (actual time=204,345.491..204,345.491 rows=23,159,511 loops=1)

  • Buckets: 8,388,608 Batches: 16 Memory Usage: 132,699kB
31. 196,068.416 196,068.416 ↑ 2.1 23,159,511 1

Seq Scan on applicant_credit_char apcc (cost=0.00..2,346,825.64 rows=48,876,664 width=48) (actual time=0.037..196,068.416 rows=23,159,511 loops=1)

32. 9,321.034 248,607.963 ↑ 1.0 19,248,167 1

Hash (cost=4,510,634.76..4,510,634.76 rows=19,555,786 width=98) (actual time=248,607.963..248,607.963 rows=19,248,167 loops=1)

  • Buckets: 4,194,304 Batches: 8 Memory Usage: 339,404kB
33. 30,633.595 239,286.929 ↑ 1.0 19,248,167 1

Hash Right Join (cost=901,834.19..4,510,634.76 rows=19,555,786 width=98) (actual time=47,593.989..239,286.929 rows=19,248,167 loops=1)

  • Hash Cond: (lpl.uuid = pl.uuid)
34. 161,108.965 161,108.965 ↑ 1.0 20,534,944 1

Seq Scan on partner_lead lpl (cost=0.00..2,044,116.19 rows=20,666,819 width=106) (actual time=1.760..161,108.965 rows=20,534,944 loops=1)

35. 6,591.568 47,544.369 ↑ 1.0 19,248,167 1

Hash (cost=542,801.86..542,801.86 rows=19,555,786 width=24) (actual time=47,544.369..47,544.369 rows=19,248,167 loops=1)

  • Buckets: 8,388,608 Batches: 4 Memory Usage: 328,773kB
36. 40,952.801 40,952.801 ↑ 1.0 19,248,167 1

Seq Scan on partner_lead pl (cost=0.00..542,801.86 rows=19,555,786 width=24) (actual time=4.434..40,952.801 rows=19,248,167 loops=1)

37. 4,258.135 448,327.778 ↓ 200.0 4,481,591 1

Hash (cost=771,962.25..771,962.25 rows=22,409 width=106) (actual time=448,327.778..448,327.778 rows=4,481,591 loops=1)

  • Buckets: 4,194,304 (originally 32768) Batches: 2 (originally 1) Memory Usage: 367,233kB
38. 2,832.282 444,069.643 ↓ 200.0 4,481,591 1

Nested Loop Left Join (cost=581,870.80..771,962.25 rows=22,409 width=106) (actual time=8,194.046..444,069.643 rows=4,481,591 loops=1)

39. 1,017.020 15,486.216 ↓ 200.0 4,481,591 1

Subquery Scan on lga (cost=581,870.37..727,529.13 rows=22,409 width=16) (actual time=8,190.195..15,486.216 rows=4,481,591 loops=1)

  • Filter: (lga.row_num = 1)
  • Rows Removed by Filter: 40
40. 4,861.889 14,469.196 ↑ 1.0 4,481,631 1

WindowAgg (cost=581,870.37..671,506.53 rows=4,481,808 width=56) (actual time=8,190.106..14,469.196 rows=4,481,631 loops=1)

41. 4,106.844 9,607.307 ↑ 1.0 4,481,631 1

Sort (cost=581,870.37..593,074.89 rows=4,481,808 width=24) (actual time=8,190.095..9,607.307 rows=4,481,631 loops=1)

  • Sort Key: login_application.loan_application_id, login_application.update_date DESC
  • Sort Method: external merge Disk: 148,952kB
42. 5,500.463 5,500.463 ↑ 1.0 4,481,631 1

Seq Scan on login_application (cost=0.00..86,728.08 rows=4,481,808 width=24) (actual time=1.745..5,500.463 rows=4,481,631 loops=1)

43. 425,751.145 425,751.145 ↑ 1.0 1 4,481,591

Index Scan using login_pkey on login (cost=0.43..1.97 rows=1 width=98) (actual time=0.092..0.095 rows=1 loops=4,481,591)

  • Index Cond: (lga.login_external_id = id)
44. 152.023 890.747 ↑ 1.0 308,755 1

Hash (cost=17,661.69..17,661.69 rows=309,171 width=33) (actual time=890.747..890.747 rows=308,755 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,415kB
45. 738.724 738.724 ↑ 1.0 308,755 1

Seq Scan on s_address_e01 s_addr (cost=0.00..17,661.69 rows=309,171 width=33) (actual time=3.831..738.724 rows=308,755 loops=1)

  • Filter: (((is_active)::text = 'Y'::text) AND ((is_primary)::text = 'Y'::text))
  • Rows Removed by Filter: 47,036
46. 259.326 1,262.897 ↑ 1.0 553,138 1

Hash (cost=91,075.54..91,075.54 rows=559,854 width=93) (actual time=1,262.897..1,262.897 rows=553,138 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 77,335kB
47. 1,003.571 1,003.571 ↑ 1.0 553,138 1

Seq Scan on borrower lirborrower (cost=0.00..91,075.54 rows=559,854 width=93) (actual time=0.019..1,003.571 rows=553,138 loops=1)

48. 87.512 2,783.959 ↓ 186.0 305,654 1

Hash (cost=55,505.56..55,505.56 rows=1,643 width=48) (actual time=2,783.959..2,783.959 rows=305,654 loops=1)

  • Buckets: 524,288 (originally 2048) Batches: 1 (originally 1) Memory Usage: 23,200kB
49. 58.628 2,696.447 ↓ 186.0 305,655 1

Subquery Scan on s_ph (cost=44,004.60..55,505.56 rows=1,643 width=48) (actual time=2,080.160..2,696.447 rows=305,655 loops=1)

  • Filter: (s_ph.row_num = 1)
  • Rows Removed by Filter: 22,351
50. 512.404 2,637.819 ↑ 1.0 328,006 1

WindowAgg (cost=44,004.60..51,398.08 rows=328,599 width=2,386) (actual time=2,080.135..2,637.819 rows=328,006 loops=1)

51. 235.211 2,125.415 ↑ 1.0 328,006 1

Sort (cost=44,004.60..44,826.10 rows=328,599 width=34) (actual time=2,079.929..2,125.415 rows=328,006 loops=1)

  • Sort Key: s_phone_number_e18.customer_id, s_phone_number_e18.phone_number_number DESC
  • Sort Method: quicksort Memory: 37,914kB
52. 1,890.204 1,890.204 ↑ 1.0 328,006 1

Seq Scan on s_phone_number_e18 (cost=0.00..13,895.12 rows=328,599 width=34) (actual time=5.587..1,890.204 rows=328,006 loops=1)

  • Filter: ((in_service)::text = 'Y'::text)
  • Rows Removed by Filter: 11,424
53. 28.572 1,017.493 ↑ 1.0 65,981 1

Hash (cost=2,446.51..2,446.51 rows=66,551 width=99) (actual time=1,017.493..1,017.493 rows=65,981 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 9,725kB
54. 988.921 988.921 ↑ 1.0 65,981 1

Seq Scan on contact_detail contact_d (cost=0.00..2,446.51 rows=66,551 width=99) (actual time=2.982..988.921 rows=65,981 loops=1)

55. 153.623 1,497.005 ↑ 2.5 404,142 1

Hash (cost=130,317.87..130,317.87 rows=995,887 width=16) (actual time=1,497.005..1,497.005 rows=404,142 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 27,137kB
56. 1,343.382 1,343.382 ↑ 2.5 404,142 1

Seq Scan on s_account_e30 s_acc (cost=0.00..130,317.87 rows=995,887 width=16) (actual time=0.043..1,343.382 rows=404,142 loops=1)

Planning time : 53.805 ms
Execution time : 1,849,445.063 ms