explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1q1s

Settings
# exclusive inclusive rows x rows loops node
1. 108.663 25,672.415 ↑ 899,441,528.9 39,510 1

Hash Left Join (cost=2,039,817.99..5,818,538,338,246.94 rows=35,536,934,805,061 width=254) (actual time=25,355.681..25,672.415 rows=39,510 loops=1)

  • Hash Cond: (p_2.loan_id = lms_loan.id)
2. 59.974 24,916.286 ↑ 899,441,528.9 39,510 1

Merge Right Join (cost=2,009,785.09..533,530,340,515.84 rows=35,536,934,805,061 width=209) (actual time=24,702.477..24,916.286 rows=39,510 loops=1)

  • Merge Cond: (submitted.id = p_2.id)
3. 166.108 1,838.008 ↑ 1.4 156,935 1

Sort (cost=137,429.25..137,994.54 rows=226,116 width=12) (actual time=1,800.147..1,838.008 rows=156,935 loops=1)

  • Sort Key: submitted.id
  • Sort Method: external sort Disk: 3384kB
4. 70.108 1,671.900 ↑ 1.4 156,935 1

Subquery Scan on submitted (cost=108,930.14..113,452.46 rows=226,116 width=12) (actual time=1,481.805..1,671.900 rows=156,935 loops=1)

5. 72.749 1,601.792 ↑ 1.4 156,935 1

Unique (cost=108,930.14..111,191.30 rows=226,116 width=16) (actual time=1,481.803..1,601.792 rows=156,935 loops=1)

6. 183.485 1,529.043 ↑ 1.4 156,935 1

Sort (cost=108,930.14..109,495.43 rows=226,116 width=16) (actual time=1,481.802..1,529.043 rows=156,935 loops=1)

  • Sort Key: p_1.master_user_id, p_1.id, (max(ph.ts))
  • Sort Method: external merge Disk: 4000kB
7. 113.402 1,345.558 ↑ 1.4 156,935 1

Finalize GroupAggregate (cost=58,351.78..84,953.35 rows=226,116 width=16) (actual time=1,156.550..1,345.558 rows=156,935 loops=1)

  • Group Key: p_1.id
8. 0.000 1,232.156 ↑ 1.2 156,935 1

Gather Merge (cost=58,351.78..81,750.04 rows=188,430 width=16) (actual time=1,156.539..1,232.156 rows=156,935 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 124.695 3,482.835 ↑ 1.8 52,312 3

Partial GroupAggregate (cost=57,351.75..59,000.52 rows=94,215 width=16) (actual time=1,096.181..1,160.945 rows=52,312 loops=3)

  • Group Key: p_1.id
10. 225.087 3,358.140 ↑ 1.4 67,324 3

Sort (cost=57,351.75..57,587.29 rows=94,215 width=16) (actual time=1,096.174..1,119.380 rows=67,324 loops=3)

  • Sort Key: p_1.id
  • Sort Method: quicksort Memory: 26kB
11. 397.380 3,133.053 ↑ 1.4 67,324 3

Hash Join (cost=28,484.43..49,567.87 rows=94,215 width=16) (actual time=861.711..1,044.351 rows=67,324 loops=3)

  • Hash Cond: (ph.loan_application_id = p_1.id)
12. 172.344 172.344 ↑ 1.3 78,149 3

Parallel Seq Scan on products_loanapplicationstatushistory ph (cost=0.00..16,749.15 rows=100,062 width=12) (actual time=0.009..57.448 rows=78,149 loops=3)

  • Filter: (status = 10)
  • Rows Removed by Filter: 260692
13. 1,125.045 2,563.329 ↓ 1.0 537,482 3

Hash (cost=19,696.94..19,696.94 rows=535,559 width=8) (actual time=854.443..854.443 rows=537,482 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3656kB
14. 1,438.284 1,438.284 ↓ 1.0 537,482 3

Seq Scan on products_loanapplication p_1 (cost=0.00..19,696.94 rows=535,559 width=8) (actual time=0.012..479.428 rows=537,482 loops=3)

  • Filter: (product_id = ANY ('{1,2,5}'::integer[]))
  • Rows Removed by Filter: 33056
15. 26.689 23,018.304 ↑ 795,560.4 39,510 1

Materialize (cost=1,872,355.84..552,867,613.78 rows=31,432,593,054 width=205) (actual time=22,847.142..23,018.304 rows=39,510 loops=1)

16. 55.614 22,991.615 ↑ 795,560.4 39,510 1

Merge Left Join (cost=1,872,355.84..474,286,131.15 rows=31,432,593,054 width=205) (actual time=22,847.134..22,991.615 rows=39,510 loops=1)

  • Merge Cond: (p_2.id = rejected.id)
17. 36.062 21,264.455 ↑ 1,334.1 39,510 1

Merge Left Join (cost=1,778,359.36..2,570,875.00 rows=52,708,772 width=197) (actual time=21,189.285..21,264.455 rows=39,510 loops=1)

  • Merge Cond: (p_2.id = preapproved.id)
18. 38.010 19,883.675 ↑ 2.7 39,510 1

Merge Left Join (cost=1,696,229.50..1,697,845.85 rows=107,672 width=189) (actual time=19,836.828..19,883.675 rows=39,510 loops=1)

  • Merge Cond: (p_2.id = approved.id)
19. 57.078 18,598.843 ↓ 155.6 39,510 1

Sort (cost=1,618,843.20..1,618,843.83 rows=254 width=181) (actual time=18,584.112..18,598.843 rows=39,510 loops=1)

  • Sort Key: p_2.id
  • Sort Method: external merge Disk: 4304kB
20. 33.245 18,541.765 ↓ 155.6 39,510 1

Nested Loop Left Join (cost=1,574,804.11..1,618,833.05 rows=254 width=181) (actual time=17,468.416..18,541.765 rows=39,510 loops=1)

21. 42.383 18,152.930 ↓ 155.6 39,510 1

Hash Left Join (cost=1,574,803.25..1,618,553.32 rows=254 width=149) (actual time=17,467.386..18,152.930 rows=39,510 loops=1)

  • Hash Cond: (p_2.master_user_id = address.master_user_id)
22. 38.745 15,192.608 ↓ 155.6 39,510 1

Merge Left Join (cost=562,942.91..606,689.77 rows=254 width=145) (actual time=14,549.329..15,192.608 rows=39,510 loops=1)

  • Merge Cond: (p_2.master_user_id = ub.master_user_id)
23. 103.801 14,306.427 ↓ 155.6 39,510 1

Merge Left Join (cost=508,002.58..549,105.76 rows=254 width=137) (actual time=13,717.905..14,306.427 rows=39,510 loops=1)

  • Merge Cond: (p_2.master_user_id = pt.master_user_id)
24. 46.720 8,299.504 ↓ 155.6 39,510 1

Merge Left Join (cost=300,061.33..305,604.37 rows=254 width=121) (actual time=8,072.492..8,299.504 rows=39,510 loops=1)

  • Merge Cond: (p_2.master_user_id = ua.master_user_id)
25. 66.935 7,417.176 ↓ 155.6 39,510 1

Merge Left Join (cost=247,400.33..250,649.43 rows=254 width=113) (actual time=7,263.784..7,417.176 rows=39,510 loops=1)

  • Merge Cond: (p_2.master_user_id = emp.master_user_id)
26. 51.586 4,737.267 ↓ 155.6 39,510 1

Sort (cost=140,823.93..140,824.56 rows=254 width=105) (actual time=4,722.435..4,737.267 rows=39,510 loops=1)

  • Sort Key: p_2.master_user_id
  • Sort Method: external merge Disk: 3976kB
27. 28.841 4,685.681 ↓ 155.6 39,510 1

Hash Left Join (cost=133,242.28..140,813.78 rows=254 width=105) (actual time=4,460.797..4,685.681 rows=39,510 loops=1)

  • Hash Cond: (p_2.master_user_id = nach.master_user_id)
28. 130.548 4,403.235 ↓ 155.6 39,510 1

Merge Left Join (cost=125,848.79..133,417.08 rows=254 width=97) (actual time=4,207.074..4,403.235 rows=39,510 loops=1)

  • Merge Cond: (um.id = p_2.master_user_id)
29. 67.369 3,280.926 ↓ 155.6 39,510 1

Sort (cost=44,205.06..44,205.70 rows=254 width=75) (actual time=3,268.675..3,280.926 rows=39,510 loops=1)

  • Sort Key: um.id
  • Sort Method: external merge Disk: 3344kB
30. 3,124.455 3,213.557 ↓ 155.6 39,510 1

Bitmap Heap Scan on users_masteruser um (cost=1,117.65..44,194.92 rows=254 width=75) (actual time=96.473..3,213.557 rows=39,510 loops=1)

  • Recheck Cond: (source_id = ANY ('{4,12}'::integer[]))
  • Filter: ((date(created_at) <= (CURRENT_DATE - 1)) AND (date(created_at) >= (date_trunc('month'::text, now()) - '3 mons'::interval)))
  • Rows Removed by Filter: 12018
  • Heap Blocks: exact=26905
31. 89.102 89.102 ↓ 1.2 58,449 1

Bitmap Index Scan on users_masteruser_source_id_bc2e316e (cost=0.00..1,117.59 rows=50,765 width=0) (actual time=89.102..89.102 rows=58,449 loops=1)

  • Index Cond: (source_id = ANY ('{4,12}'::integer[]))
32. 220.219 991.761 ↓ 1.3 491,552 1

Unique (cost=81,643.72..84,321.52 rows=391,097 width=22) (actual time=606.527..991.761 rows=491,552 loops=1)

33. 519.472 771.542 ↓ 1.0 537,176 1

Sort (cost=81,643.72..82,982.62 rows=535,559 width=22) (actual time=606.525..771.542 rows=537,176 loops=1)

  • Sort Key: p_2.master_user_id, p_2.created_at DESC
  • Sort Method: external merge Disk: 20008kB
34. 252.070 252.070 ↓ 1.0 537,482 1

Seq Scan on products_loanapplication p_2 (cost=0.00..19,696.94 rows=535,559 width=22) (actual time=0.014..252.070 rows=537,482 loops=1)

  • Filter: (product_id = ANY ('{1,2,5}'::integer[]))
  • Rows Removed by Filter: 33056
35. 29.791 253.605 ↓ 1.4 71,939 1

Hash (cost=6,755.58..6,755.58 rows=51,033 width=12) (actual time=253.605..253.605 rows=71,939 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
36. 28.969 223.814 ↓ 1.4 71,939 1

Subquery Scan on nach (cost=5,734.92..6,755.58 rows=51,033 width=12) (actual time=166.712..223.814 rows=71,939 loops=1)

37. 62.797 194.845 ↓ 1.4 71,939 1

HashAggregate (cost=5,734.92..6,245.25 rows=51,033 width=12) (actual time=166.709..194.845 rows=71,939 loops=1)

  • Group Key: users_debitinstruction.master_user_id, max(users_debitinstruction.created_at)
38. 65.799 132.048 ↓ 1.4 71,939 1

HashAggregate (cost=4,969.43..5,479.76 rows=51,033 width=12) (actual time=102.759..132.048 rows=71,939 loops=1)

  • Group Key: users_debitinstruction.master_user_id
39. 66.249 66.249 ↓ 1.4 71,939 1

Seq Scan on users_debitinstruction (cost=0.00..4,714.26 rows=51,033 width=12) (actual time=0.488..66.249 rows=71,939 loops=1)

  • Filter: (is_valid AND (signed_doc_id IS NOT NULL))
  • Rows Removed by Filter: 139198
40. 83.769 2,612.974 ↓ 1.0 170,307 1

Unique (cost=106,576.40..107,793.94 rows=162,339 width=12) (actual time=2,482.651..2,612.974 rows=170,307 loops=1)

41. 184.383 2,529.205 ↓ 1.0 170,307 1

Sort (cost=106,576.40..106,982.25 rows=162,339 width=12) (actual time=2,482.649..2,529.205 rows=170,307 loops=1)

  • Sort Key: emp.master_user_id, (max(emph.ts))
  • Sort Method: external sort Disk: 4344kB
42. 122.544 2,344.822 ↓ 1.1 170,575 1

Finalize GroupAggregate (cost=70,653.14..89,751.56 rows=162,339 width=12) (actual time=2,034.949..2,344.822 rows=170,575 loops=1)

  • Group Key: emp.master_user_id
43. 0.000 2,222.278 ↓ 1.4 189,045 1

Gather Merge (cost=70,653.14..87,451.76 rows=135,282 width=12) (actual time=2,034.942..2,222.278 rows=189,045 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 149.292 6,219.843 ↑ 1.1 63,015 3

Partial GroupAggregate (cost=69,653.12..70,836.83 rows=67,641 width=12) (actual time=1,998.403..2,073.281 rows=63,015 loops=3)

  • Group Key: emp.master_user_id
45. 336.099 6,070.551 ↓ 1.1 72,924 3

Sort (cost=69,653.12..69,822.22 rows=67,641 width=12) (actual time=1,998.396..2,023.517 rows=72,924 loops=3)

  • Sort Key: emp.master_user_id
  • Sort Method: external merge Disk: 1928kB
46. 628.824 5,734.452 ↓ 1.1 72,924 3

Hash Join (cost=39,289.96..64,226.41 rows=67,641 width=12) (actual time=1,332.890..1,911.484 rows=72,924 loops=3)

  • Hash Cond: (emph.entity_id = emp.id)
47. 1,139.460 1,139.460 ↑ 1.2 111,132 3

Parallel Seq Scan on users_employmenthistory emph (cost=0.00..19,372.49 rows=137,660 width=12) (actual time=1.280..379.820 rows=111,132 loops=3)

  • Filter: (status = 15)
  • Rows Removed by Filter: 236725
48. 1,196.949 3,966.168 ↑ 1.0 628,418 3

Hash (cost=28,874.62..28,874.62 rows=634,827 width=8) (actual time=1,322.056..1,322.056 rows=628,418 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2567kB
49. 2,769.219 2,769.219 ↑ 1.0 628,418 3

Seq Scan on users_employment emp (cost=0.00..28,874.62 rows=634,827 width=8) (actual time=0.010..923.073 rows=628,418 loops=3)

  • Filter: is_valid
  • Rows Removed by Filter: 666482
50. 46.983 835.608 ↑ 1.2 93,799 1

Unique (cost=52,661.00..53,520.71 rows=114,628 width=12) (actual time=761.664..835.608 rows=93,799 loops=1)

51. 101.455 788.625 ↑ 1.2 93,799 1

Sort (cost=52,661.00..52,947.57 rows=114,628 width=12) (actual time=761.662..788.625 rows=93,799 loops=1)

  • Sort Key: ua.master_user_id, (max(uah.ts))
  • Sort Method: external sort Disk: 2392kB
52. 64.948 687.170 ↑ 1.2 93,982 1

Finalize GroupAggregate (cost=27,582.89..41,068.47 rows=114,628 width=12) (actual time=515.205..687.170 rows=93,982 loops=1)

  • Group Key: ua.master_user_id
53. 0.000 622.222 ↓ 1.1 102,645 1

Gather Merge (cost=27,582.89..39,444.57 rows=95,524 width=12) (actual time=515.197..622.222 rows=102,645 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
54. 82.791 1,636.788 ↑ 1.4 34,215 3

Partial GroupAggregate (cost=26,582.87..27,418.70 rows=47,762 width=12) (actual time=502.262..545.596 rows=34,215 loops=3)

  • Group Key: ua.master_user_id
55. 130.371 1,553.997 ↑ 1.2 39,585 3

Sort (cost=26,582.87..26,702.27 rows=47,762 width=12) (actual time=502.255..517.999 rows=39,585 loops=3)

  • Sort Key: ua.master_user_id
  • Sort Method: quicksort Memory: 3562kB
56. 213.246 1,423.626 ↑ 1.2 39,585 3

Hash Join (cost=14,838.69..22,870.91 rows=47,762 width=12) (actual time=291.150..474.542 rows=39,585 loops=3)

  • Hash Cond: (uah.entity_id = ua.id)
57. 346.263 346.263 ↑ 1.2 62,271 3

Parallel Seq Scan on users_aadhaarhistory uah (cost=0.00..5,829.56 rows=75,537 width=12) (actual time=1.252..115.421 rows=62,271 loops=3)

  • Filter: (status = 15)
  • Rows Removed by Filter: 45018
58. 265.566 864.117 ↓ 1.0 131,251 3

Hash (cost=12,695.32..12,695.32 rows=130,590 width=8) (actual time=288.039..288.039 rows=131,251 loops=3)

  • Buckets: 131072 Batches: 2 Memory Usage: 3601kB
59. 598.551 598.551 ↓ 1.0 131,251 3

Seq Scan on users_aadhaar ua (cost=0.00..12,695.32 rows=130,590 width=8) (actual time=0.011..199.517 rows=131,251 loops=3)

  • Filter: is_valid
  • Rows Removed by Filter: 76444
60. 273.263 5,903.122 ↓ 2.6 259,783 1

Finalize GroupAggregate (cost=207,941.25..242,235.95 rows=101,132 width=20) (actual time=4,898.100..5,903.122 rows=259,783 loops=1)

  • Group Key: pt.master_user_id
61. 0.000 5,629.859 ↓ 2.2 442,592 1

Gather Merge (cost=207,941.25..239,707.65 rows=202,264 width=20) (actual time=4,898.089..5,629.859 rows=442,592 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
62. 820.950 16,090.137 ↓ 1.5 147,587 3

Partial GroupAggregate (cost=206,941.22..215,361.34 rows=101,132 width=20) (actual time=4,892.539..5,363.379 rows=147,587 loops=3)

  • Group Key: pt.master_user_id
63. 1,927.554 15,269.187 ↑ 1.3 454,183 3

Sort (cost=206,941.22..208,422.98 rows=592,704 width=28) (actual time=4,892.529..5,089.729 rows=454,183 loops=3)

  • Sort Key: pt.master_user_id
  • Sort Method: external merge Disk: 14264kB
64. 1,838.892 13,341.633 ↑ 1.3 454,256 3

Merge Left Join (cost=33.55..135,927.94 rows=592,704 width=28) (actual time=73.034..4,447.211 rows=454,256 loops=3)

  • Merge Cond: (pt.id = pd.transaction_id)
65. 3,656.910 3,656.910 ↑ 1.3 452,503 3

Parallel Index Scan using external_perfiostransaction_pkey on external_perfiostransaction pt (cost=0.43..98,724.91 rows=592,704 width=16) (actual time=0.650..1,218.970 rows=452,503 loops=3)

66. 7,845.831 7,845.831 ↑ 1.0 482,929 3

Index Scan using external_perfiosdata_transaction_id_d5373b94 on external_perfiosdata pd (cost=0.42..32,433.32 rows=493,192 width=20) (actual time=0.022..2,615.277 rows=482,929 loops=3)

67. 31.832 847.436 ↑ 2.1 63,786 1

Unique (cost=54,940.33..55,931.15 rows=132,109 width=12) (actual time=797.551..847.436 rows=63,786 loops=1)

68. 64.258 815.604 ↑ 2.1 63,786 1

Sort (cost=54,940.33..55,270.61 rows=132,109 width=12) (actual time=797.549..815.604 rows=63,786 loops=1)

  • Sort Key: ub.master_user_id, (max(ubh.ts))
  • Sort Method: external sort Disk: 1624kB
69. 70.423 751.346 ↑ 2.1 63,809 1

GroupAggregate (cost=38,843.10..41,442.56 rows=132,109 width=12) (actual time=629.570..751.346 rows=63,809 loops=1)

  • Group Key: ub.master_user_id
70. 184.576 680.923 ↓ 1.1 186,374 1

Sort (cost=38,843.10..39,269.22 rows=170,449 width=12) (actual time=629.559..680.923 rows=186,374 loops=1)

  • Sort Key: ub.master_user_id
  • Sort Method: external merge Disk: 4760kB
71. 196.935 496.347 ↓ 1.1 186,374 1

Hash Join (cost=8,096.92..21,116.45 rows=170,449 width=12) (actual time=163.887..496.347 rows=186,374 loops=1)

  • Hash Cond: (ubh.entity_id = ub.id)
72. 136.600 136.600 ↓ 1.0 266,511 1

Seq Scan on users_bankaccounthistory ubh (cost=0.00..6,603.05 rows=263,405 width=12) (actual time=0.484..136.600 rows=266,511 loops=1)

73. 53.836 162.812 ↓ 1.0 133,398 1

Hash (cost=5,928.56..5,928.56 rows=132,109 width=8) (actual time=162.812..162.812 rows=133,398 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3627kB
74. 108.976 108.976 ↓ 1.0 133,398 1

Seq Scan on users_bankaccount ub (cost=0.00..5,928.56 rows=132,109 width=8) (actual time=0.422..108.976 rows=133,398 loops=1)

  • Filter: is_valid
  • Rows Removed by Filter: 71774
75. 51.830 2,917.939 ↓ 3.2 128,867 1

Hash (cost=1,011,360.34..1,011,360.34 rows=40,000 width=12) (actual time=2,917.939..2,917.939 rows=128,867 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3804kB
76. 56.097 2,866.109 ↓ 3.2 128,867 1

Subquery Scan on address (cost=1,010,560.34..1,011,360.34 rows=40,000 width=12) (actual time=2,752.996..2,866.109 rows=128,867 loops=1)

77. 151.507 2,810.012 ↓ 3.2 128,867 1

HashAggregate (cost=1,010,560.34..1,010,960.34 rows=40,000 width=12) (actual time=2,752.993..2,810.012 rows=128,867 loops=1)

  • Group Key: COALESCE(uad.master_user_id, ua_1.master_user_id)
78. 109.478 2,658.505 ↑ 229.2 189,951 1

Merge Full Join (cost=136,481.63..792,921.89 rows=43,527,690 width=20) (actual time=2,332.736..2,658.505 rows=189,951 loops=1)

  • Merge Cond: (uad.master_user_id = ua_1.master_user_id)
79. 36.499 1,931.679 ↓ 1.0 76,051 1

Unique (cost=95,808.11..96,377.70 rows=75,946 width=44) (actual time=1,869.971..1,931.679 rows=76,051 loops=1)

80. 109.969 1,895.180 ↓ 1.0 76,051 1

Sort (cost=95,808.11..95,997.97 rows=75,946 width=44) (actual time=1,869.969..1,895.180 rows=76,051 loops=1)

  • Sort Key: uad.master_user_id, uadh.ts
  • Sort Method: external merge Disk: 3136kB
81. 22.755 1,785.211 ↓ 1.0 76,051 1

Gather (cost=60,298.48..87,313.67 rows=75,946 width=44) (actual time=1,258.839..1,785.211 rows=76,051 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
82. 120.938 1,762.456 ↑ 1.2 25,350 3

Hash Join (cost=59,298.48..78,719.07 rows=31,644 width=44) (actual time=1,271.615..1,762.456 rows=25,350 loops=3)

  • Hash Cond: (uadh.entity_id = uad.id)
83. 379.826 379.826 ↑ 1.2 54,756 3

Parallel Seq Scan on users_addresshistory uadh (cost=0.00..15,292.77 rows=68,263 width=12) (actual time=1.468..379.826 rows=54,756 loops=3)

  • Filter: (status = 15)
  • Rows Removed by Filter: 321099
84. 435.211 1,261.692 ↑ 1.1 628,411 3

Hash (cost=48,388.85..48,388.85 rows=664,930 width=8) (actual time=1,261.692..1,261.692 rows=628,411 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2551kB
85. 826.481 826.481 ↑ 1.1 628,411 3

Seq Scan on users_address uad (cost=0.00..48,388.85 rows=664,930 width=8) (actual time=0.010..826.481 rows=628,411 loops=3)

  • Filter: (is_valid AND (address_type = 1))
  • Rows Removed by Filter: 812415
86. 55.715 617.348 ↓ 1.2 138,565 1

Materialize (cost=40,673.52..42,966.08 rows=114,628 width=12) (actual time=462.757..617.348 rows=138,565 loops=1)

87. 57.016 561.633 ↓ 1.0 118,755 1

Unique (cost=40,673.52..41,533.23 rows=114,628 width=44) (actual time=462.751..561.633 rows=118,755 loops=1)

88. 136.927 504.617 ↓ 1.0 118,755 1

Sort (cost=40,673.52..40,960.09 rows=114,628 width=44) (actual time=462.749..504.617 rows=118,755 loops=1)

  • Sort Key: ua_1.master_user_id, uah_1.ts
  • Sort Method: external merge Disk: 4888kB
89. 128.404 367.690 ↓ 1.0 118,755 1

Hash Join (cost=14,838.69..27,512.98 rows=114,628 width=44) (actual time=136.569..367.690 rows=118,755 loops=1)

  • Hash Cond: (uah_1.entity_id = ua_1.id)
90. 103.263 103.263 ↓ 1.0 186,812 1

Seq Scan on users_aadhaarhistory uah_1 (cost=0.00..8,102.54 rows=181,288 width=12) (actual time=0.019..103.263 rows=186,812 loops=1)

  • Filter: (status = 15)
  • Rows Removed by Filter: 135055
91. 50.310 136.023 ↓ 1.0 131,251 1

Hash (cost=12,695.32..12,695.32 rows=130,590 width=8) (actual time=136.023..136.023 rows=131,251 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3601kB
92. 85.713 85.713 ↓ 1.0 131,251 1

Seq Scan on users_aadhaar ua_1 (cost=0.00..12,695.32 rows=130,590 width=8) (actual time=0.008..85.713 rows=131,251 loops=1)

  • Filter: is_valid
  • Rows Removed by Filter: 76444
93. 35.585 355.590 ↓ 0.0 0 39,510

Nested Loop Left Join (cost=0.86..1.09 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=39,510)

94. 35.845 316.080 ↓ 0.0 0 39,510

Nested Loop Left Join (cost=0.71..0.92 rows=1 width=48) (actual time=0.008..0.008 rows=0 loops=39,510)

95. 237.060 237.060 ↓ 0.0 0 39,510

Index Scan using products_loanapplication_loan_id_key on products_loanapplication p (cost=0.42..0.60 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=39,510)

  • Index Cond: (p_2.loan_id = loan_id)
  • Filter: ((status = 50) AND (product_id = ANY ('{1,2,5}'::integer[])))
  • Rows Removed by Filter: 0
96. 43.175 43.175 ↓ 0.0 0 3,925

Index Only Scan using products_loanapplication_loanapplication_id_rejec_304e5fd7_uniq on products_loanapplication_rejection_reasons plr (cost=0.29..0.32 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=3,925)

  • Index Cond: (loanapplication_id = p.id)
  • Heap Fetches: 1154
97. 3.925 3.925 ↓ 0.0 0 3,925

Index Scan using products_rejectionreason_pkey on products_rejectionreason pr (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=0 loops=3,925)

  • Index Cond: (plr.rejectionreason_id = id)
98. 34.111 1,246.822 ↑ 1.6 54,144 1

Sort (cost=77,386.31..77,598.26 rows=84,781 width=12) (actual time=1,234.735..1,246.822 rows=54,144 loops=1)

  • Sort Key: approved.id
  • Sort Method: quicksort Memory: 3904kB
99. 22.208 1,212.711 ↑ 1.6 54,144 1

Subquery Scan on approved (cost=68,750.74..70,446.36 rows=84,781 width=12) (actual time=1,152.921..1,212.711 rows=54,144 loops=1)

100. 24.631 1,190.503 ↑ 1.6 54,144 1

Unique (cost=68,750.74..69,598.55 rows=84,781 width=16) (actual time=1,152.918..1,190.503 rows=54,144 loops=1)

101. 40.413 1,165.872 ↑ 1.6 54,144 1

Sort (cost=68,750.74..68,962.70 rows=84,781 width=16) (actual time=1,152.916..1,165.872 rows=54,144 loops=1)

  • Sort Key: p_3.master_user_id, p_3.id, (max(ph_1.ts))
  • Sort Method: quicksort Memory: 3904kB
102. 34.388 1,125.459 ↑ 1.6 54,144 1

Finalize GroupAggregate (cost=51,836.79..61,810.80 rows=84,781 width=16) (actual time=1,051.961..1,125.459 rows=54,144 loops=1)

  • Group Key: p_3.id
103. 0.000 1,091.071 ↑ 1.3 54,144 1

Gather Merge (cost=51,836.79..60,609.74 rows=70,650 width=16) (actual time=1,051.951..1,091.071 rows=54,144 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
104. 37.011 3,076.215 ↑ 2.0 18,048 3

Partial GroupAggregate (cost=50,836.77..51,454.96 rows=35,325 width=16) (actual time=1,008.249..1,025.405 rows=18,048 loops=3)

  • Group Key: p_3.id
105. 49.494 3,039.204 ↑ 2.0 18,075 3

Sort (cost=50,836.77..50,925.08 rows=35,325 width=16) (actual time=1,008.241..1,013.068 rows=18,075 loops=3)

  • Sort Key: p_3.id
  • Sort Method: quicksort Memory: 990kB
106. 345.768 2,989.710 ↑ 2.0 18,075 3

Hash Join (cost=28,484.43..48,168.25 rows=35,325 width=16) (actual time=745.765..996.570 rows=18,075 loops=3)

  • Hash Cond: (ph_1.loan_application_id = p_3.id)
107. 411.369 411.369 ↑ 1.3 28,670 3

Parallel Seq Scan on products_loanapplicationstatushistory ph_1 (cost=0.00..16,749.15 rows=37,518 width=12) (actual time=0.018..137.123 rows=28,670 loops=3)

  • Filter: (status = 20)
  • Rows Removed by Filter: 310172
108. 965.790 2,232.573 ↓ 1.0 537,482 3

Hash (cost=19,696.94..19,696.94 rows=535,559 width=8) (actual time=744.191..744.191 rows=537,482 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3656kB
109. 1,266.783 1,266.783 ↓ 1.0 537,482 3

Seq Scan on products_loanapplication p_3 (cost=0.00..19,696.94 rows=535,559 width=8) (actual time=0.011..422.261 rows=537,482 loops=3)

  • Filter: (product_id = ANY ('{1,2,5}'::integer[]))
  • Rows Removed by Filter: 33056
110. 70.867 1,344.718 ↑ 1.4 67,990 1

Sort (cost=82,129.85..82,374.62 rows=97,906 width=12) (actual time=1,326.265..1,344.718 rows=67,990 loops=1)

  • Sort Key: preapproved.id
  • Sort Method: external sort Disk: 1736kB
111. 29.387 1,273.851 ↑ 1.4 67,990 1

Subquery Scan on preapproved (cost=72,055.76..74,013.88 rows=97,906 width=12) (actual time=1,194.878..1,273.851 rows=67,990 loops=1)

112. 31.470 1,244.464 ↑ 1.4 67,990 1

Unique (cost=72,055.76..73,034.82 rows=97,906 width=16) (actual time=1,194.876..1,244.464 rows=67,990 loops=1)

113. 75.272 1,212.994 ↑ 1.4 67,990 1

Sort (cost=72,055.76..72,300.53 rows=97,906 width=16) (actual time=1,194.873..1,212.994 rows=67,990 loops=1)

  • Sort Key: p_4.master_user_id, p_4.id, (max(ph_2.ts))
  • Sort Method: external merge Disk: 1744kB
114. 42.593 1,137.722 ↑ 1.4 67,990 1

Finalize GroupAggregate (cost=52,421.62..63,939.79 rows=97,906 width=16) (actual time=1,001.462..1,137.722 rows=67,990 loops=1)

  • Group Key: p_4.id
115. 0.000 1,095.129 ↑ 1.2 67,990 1

Gather Merge (cost=52,421.62..62,552.79 rows=81,588 width=16) (actual time=1,001.450..1,095.129 rows=67,990 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
116. 43.014 2,943.549 ↑ 1.8 22,663 3

Partial GroupAggregate (cost=51,421.59..52,135.49 rows=40,794 width=16) (actual time=960.308..981.183 rows=22,663 loops=3)

  • Group Key: p_4.id
117. 88.794 2,900.535 ↑ 1.7 23,347 3

Sort (cost=51,421.59..51,523.58 rows=40,794 width=16) (actual time=960.301..966.845 rows=23,347 loops=3)

  • Sort Key: p_4.id
  • Sort Method: external merge Disk: 1784kB
118. 227.637 2,811.741 ↑ 1.7 23,347 3

Hash Join (cost=28,484.43..48,297.57 rows=40,794 width=16) (actual time=820.103..937.247 rows=23,347 loops=3)

  • Hash Cond: (ph_2.loan_application_id = p_4.id)
119. 127.893 127.893 ↑ 1.3 34,207 3

Parallel Seq Scan on products_loanapplicationstatushistory ph_2 (cost=0.00..16,749.15 rows=43,326 width=12) (actual time=0.008..42.631 rows=34,207 loops=3)

  • Filter: (status = 15)
  • Rows Removed by Filter: 304634
120. 1,049.562 2,456.211 ↓ 1.0 537,482 3

Hash (cost=19,696.94..19,696.94 rows=535,559 width=8) (actual time=818.737..818.737 rows=537,482 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3656kB
121. 1,406.649 1,406.649 ↓ 1.0 537,482 3

Seq Scan on products_loanapplication p_4 (cost=0.00..19,696.94 rows=535,559 width=8) (actual time=0.014..468.883 rows=537,482 loops=3)

  • Filter: (product_id = ANY ('{1,2,5}'::integer[]))
  • Rows Removed by Filter: 33056
122. 55.653 1,671.546 ↓ 1.1 130,285 1

Materialize (cost=93,996.48..94,592.82 rows=119,269 width=12) (actual time=1,582.208..1,671.546 rows=130,285 loops=1)

123. 135.893 1,615.893 ↓ 1.1 130,285 1

Sort (cost=93,996.48..94,294.65 rows=119,269 width=12) (actual time=1,582.199..1,615.893 rows=130,285 loops=1)

  • Sort Key: rejected.id
  • Sort Method: external sort Disk: 2808kB
124. 57.211 1,480.000 ↓ 1.1 130,285 1

Subquery Scan on rejected (cost=79,513.92..81,899.30 rows=119,269 width=12) (actual time=1,324.595..1,480.000 rows=130,285 loops=1)

125. 59.679 1,422.789 ↓ 1.1 130,285 1

Unique (cost=79,513.92..80,706.61 rows=119,269 width=16) (actual time=1,324.593..1,422.789 rows=130,285 loops=1)

126. 155.097 1,363.110 ↓ 1.1 130,285 1

Sort (cost=79,513.92..79,812.09 rows=119,269 width=16) (actual time=1,324.591..1,363.110 rows=130,285 loops=1)

  • Sort Key: p_5.master_user_id, p_5.id, (max(ph_3.ts))
  • Sort Method: external merge Disk: 3328kB
127. 94.988 1,208.013 ↓ 1.1 130,285 1

Finalize GroupAggregate (cost=53,385.37..67,416.74 rows=119,269 width=16) (actual time=1,038.120..1,208.013 rows=130,285 loops=1)

  • Group Key: p_5.id
128. 0.000 1,113.025 ↓ 1.3 130,285 1

Gather Merge (cost=53,385.37..65,727.10 rows=99,390 width=16) (actual time=1,038.109..1,113.025 rows=130,285 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
129. 95.982 3,123.669 ↑ 1.1 43,428 3

Partial GroupAggregate (cost=52,385.34..53,255.01 rows=49,695 width=16) (actual time=993.706..1,041.223 rows=43,428 loops=3)

  • Group Key: p_5.id
130. 147.879 3,027.687 ↑ 1.1 43,485 3

Sort (cost=52,385.34..52,509.58 rows=49,695 width=16) (actual time=993.699..1,009.229 rows=43,485 loops=3)

  • Sort Key: p_5.id
  • Sort Method: quicksort Memory: 25kB
131. 205.698 2,879.808 ↑ 1.1 43,485 3

Hash Join (cost=28,484.43..48,508.93 rows=49,695 width=16) (actual time=853.974..959.936 rows=43,485 loops=3)

  • Hash Cond: (ph_3.loan_application_id = p_5.id)
132. 126.456 126.456 ↑ 1.2 43,496 3

Parallel Seq Scan on products_loanapplicationstatushistory ph_3 (cost=0.00..16,749.15 rows=52,780 width=12) (actual time=0.031..42.152 rows=43,496 loops=3)

  • Filter: (status = 50)
  • Rows Removed by Filter: 295345
133. 1,068.798 2,547.654 ↓ 1.0 537,482 3

Hash (cost=19,696.94..19,696.94 rows=535,559 width=8) (actual time=849.218..849.218 rows=537,482 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3656kB
134. 1,478.856 1,478.856 ↓ 1.0 537,482 3

Seq Scan on products_loanapplication p_5 (cost=0.00..19,696.94 rows=535,559 width=8) (actual time=0.013..492.952 rows=537,482 loops=3)

  • Filter: (product_id = ANY ('{1,2,5}'::integer[]))
  • Rows Removed by Filter: 33056
135. 237.471 647.466 ↓ 1.0 570,539 1

Hash (cost=19,667.95..19,667.95 rows=564,556 width=17) (actual time=647.466..647.466 rows=570,539 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2110kB
136. 409.995 409.995 ↓ 1.0 570,539 1

Seq Scan on lms_loan (cost=0.00..19,667.95 rows=564,556 width=17) (actual time=0.683..409.995 rows=570,539 loops=1)