explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oixQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.153 486.388 ↓ 245.0 490 1

Sort (cost=47,261.59..47,261.59 rows=2 width=113) (actual time=486.354..486.388 rows=490 loops=1)

  • Sort Key: ae.loan_id
  • Sort Method: quicksort Memory: 63kB
2.          

CTE sale_dates

3. 0.183 147.853 ↓ 17.9 304 1

GroupAggregate (cost=22,805.61..22,805.95 rows=17 width=8) (actual time=147.624..147.853 rows=304 loops=1)

  • Group Key: parent.id, transactions.date
  • Filter: (count(1) = 2)
4. 0.234 147.670 ↓ 35.8 608 1

Sort (cost=22,805.61..22,805.65 rows=17 width=8) (actual time=147.618..147.670 rows=608 loops=1)

  • Sort Key: parent.id, transactions.date
  • Sort Method: quicksort Memory: 53kB
5. 0.178 147.436 ↓ 35.8 608 1

Hash Join (cost=16,589.82..22,805.26 rows=17 width=8) (actual time=127.005..147.436 rows=608 loops=1)

  • Hash Cond: (parent.id = child.participation_loan_id)
6. 7.710 146.693 ↓ 17.9 304 1

Hash Semi Join (cost=16,479.30..22,694.52 rows=17 width=12) (actual time=126.433..146.693 rows=304 loops=1)

  • Hash Cond: (transactions.id = x.id)
7. 0.139 13.365 ↓ 17.9 304 1

Hash Join (cost=87.00..5,983.98 rows=17 width=16) (actual time=0.660..13.365 rows=304 loops=1)

  • Hash Cond: (transactions.loan_id = parent.id)
8. 12.580 12.580 ↑ 1.0 609 1

Seq Scan on transactions (cost=0.00..5,889.24 rows=613 width=12) (actual time=0.010..12.580 rows=609 loops=1)

  • Filter: ((date < '2018-07-31'::date) AND ((code)::text = 'TR'::text))
  • Rows Removed by Filter: 81607
9. 0.038 0.646 ↓ 6.0 319 1

Hash (cost=86.34..86.34 rows=53 width=4) (actual time=0.646..0.646 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
10. 0.608 0.608 ↓ 6.0 319 1

Seq Scan on loans parent (cost=0.00..86.34 rows=53 width=4) (actual time=0.004..0.608 rows=319 loops=1)

  • Filter: (is_community_advantage AND (community_advantage_approved_date IS NOT NULL))
  • Rows Removed by Filter: 1615
11. 14.474 125.618 ↑ 1.0 80,131 1

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=125.618..125.618 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2434kB
12. 13.983 111.144 ↑ 1.0 80,131 1

Subquery Scan on x (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=55.626..111.144 rows=80,131 loops=1)

  • Filter: (((x.code)::text !~~ 'RV%'::text) AND ((COALESCE(x.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
13. 33.178 97.161 ↑ 1.0 82,216 1

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=55.624..97.161 rows=82,216 loops=1)

14. 47.502 63.983 ↑ 1.0 82,216 1

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=55.615..63.983 rows=82,216 loops=1)

  • Sort Key: transactions_1.loan_id, transactions_1.id
  • Sort Method: external merge Disk: 1704kB
15. 16.481 16.481 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_1 (cost=0.00..5,478.16 rows=82,216 width=11) (actual time=0.004..16.481 rows=82,216 loops=1)

16. 0.146 0.565 ↑ 3.0 640 1

Hash (cost=86.34..86.34 rows=1,934 width=4) (actual time=0.565..0.565 rows=640 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 39kB
17. 0.419 0.419 ↑ 1.0 1,934 1

Seq Scan on loans child (cost=0.00..86.34 rows=1,934 width=4) (actual time=0.012..0.419 rows=1,934 loops=1)

18.          

CTE loans_and_transactions

19. 0.191 332.718 ↓ 753.0 753 1

Sort (cost=24,454.79..24,454.80 rows=1 width=140) (actual time=332.677..332.718 rows=753 loops=1)

  • Sort Key: ut.id
  • Sort Method: quicksort Memory: 83kB
20.          

CTE indexed_unreversed_transactions

21. 62.448 299.941 ↓ 7.2 15,834 1

WindowAgg (cost=23,738.47..24,335.71 rows=2,212 width=505) (actual time=232.644..299.941 rows=15,834 loops=1)

22. 30.533 237.493 ↓ 7.2 15,834 1

Sort (cost=23,738.47..23,744.00 rows=2,212 width=469) (actual time=232.597..237.493 rows=15,834 loops=1)

  • Sort Key: transactions_2.loan_id, transactions_2.id
  • Sort Method: external merge Disk: 6696kB
23. 34.295 206.960 ↓ 7.2 15,834 1

Hash Semi Join (cost=16,479.30..23,615.58 rows=2,212 width=469) (actual time=144.906..206.960 rows=15,834 loops=1)

  • Hash Cond: (transactions_2.id = x_1.id)
24. 16.314 28.873 ↓ 7.2 16,312 1

Hash Join (cost=87.00..6,602.76 rows=2,253 width=469) (actual time=0.956..28.873 rows=16,312 loops=1)

  • Hash Cond: (transactions_2.loan_id = l.id)
25. 11.616 11.616 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_2 (cost=0.00..5,478.16 rows=82,216 width=465) (actual time=0.008..11.616 rows=82,216 loops=1)

26. 0.052 0.943 ↓ 6.0 319 1

Hash (cost=86.34..86.34 rows=53 width=8) (actual time=0.943..0.943 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
27. 0.891 0.891 ↓ 6.0 319 1

Seq Scan on loans l (cost=0.00..86.34 rows=53 width=8) (actual time=0.006..0.891 rows=319 loops=1)

  • Filter: (is_community_advantage AND (community_advantage_approved_date IS NOT NULL))
  • Rows Removed by Filter: 1615
28. 16.779 143.792 ↑ 1.0 80,131 1

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=143.792..143.792 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2434kB
29. 15.008 127.013 ↑ 1.0 80,131 1

Subquery Scan on x_1 (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=65.877..127.013 rows=80,131 loops=1)

  • Filter: (((x_1.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_1.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
30. 36.603 112.005 ↑ 1.0 82,216 1

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=65.874..112.005 rows=82,216 loops=1)

31. 55.771 75.402 ↑ 1.0 82,216 1

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=65.866..75.402 rows=82,216 loops=1)

  • Sort Key: transactions_3.loan_id, transactions_3.id
  • Sort Method: external merge Disk: 1704kB
32. 19.631 19.631 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_3 (cost=0.00..5,478.16 rows=82,216 width=11) (actual time=0.005..19.631 rows=82,216 loops=1)

33. 2.277 332.527 ↓ 753.0 753 1

Hash Join (cost=55.47..119.07 rows=1 width=140) (actual time=326.139..332.527 rows=753 loops=1)

  • Hash Cond: ((pt.loan_id = ut.loan_id) AND (pt.row_number = (ut.row_number - 1)))
34. 236.830 236.830 ↓ 7.2 15,834 1

CTE Scan on indexed_unreversed_transactions pt (cost=0.00..44.24 rows=2,212 width=44) (actual time=232.647..236.830 rows=15,834 loops=1)

35. 0.289 93.420 ↓ 68.7 756 1

Hash (cost=55.30..55.30 rows=11 width=116) (actual time=93.420..93.420 rows=756 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
36. 93.131 93.131 ↓ 68.7 756 1

CTE Scan on indexed_unreversed_transactions ut (cost=0.00..55.30 rows=11 width=116) (actual time=0.442..93.131 rows=756 loops=1)

  • Filter: ((date >= '2018-07-01'::date) AND (date <= '2018-07-31'::date))
  • Rows Removed by Filter: 15078
37.          

CTE adjustment_events

38. 0.145 483.401 ↓ 166.0 498 1

Unique (cost=0.62..0.64 rows=3 width=113) (actual time=483.180..483.401 rows=498 loops=1)

39. 1.630 483.256 ↓ 169.7 509 1

Sort (cost=0.62..0.62 rows=3 width=113) (actual time=483.180..483.256 rows=509 loops=1)

  • Sort Key: lat.loan_id, lat.date
  • Sort Method: quicksort Memory: 64kB
40. 0.204 481.626 ↓ 169.7 509 1

Hash Right Join (cost=0.15..0.59 rows=3 width=113) (actual time=481.114..481.626 rows=509 loops=1)

  • Hash Cond: (sd.loan_id = lat.loan_id)
41. 147.940 147.940 ↓ 17.9 304 1

CTE Scan on sale_dates sd (cost=0.00..0.34 rows=17 width=8) (actual time=147.626..147.940 rows=304 loops=1)

42. 0.076 333.482 ↓ 169.7 509 1

Hash (cost=0.11..0.11 rows=3 width=108) (actual time=333.482..333.482 rows=509 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
43. 0.046 333.406 ↓ 169.7 509 1

Append (cost=0.00..0.11 rows=3 width=108) (actual time=332.833..333.406 rows=509 loops=1)

44. 332.837 332.837 ↓ 3.0 3 1

CTE Scan on loans_and_transactions lat (cost=0.00..0.02 rows=1 width=108) (actual time=332.832..332.837 rows=3 loops=1)

  • Filter: ((code)::text = 'TR'::text)
  • Rows Removed by Filter: 750
45. 0.134 0.134 ↓ 256.0 256 1

CTE Scan on loans_and_transactions lat_1 (cost=0.00..0.02 rows=1 width=108) (actual time=0.003..0.134 rows=256 loops=1)

  • Filter: (current_interest_rate <> prior_interest_rate)
  • Rows Removed by Filter: 497
46. 0.071 0.389 ↓ 250.0 250 1

Unique (cost=0.03..0.04 rows=1 width=108) (actual time=0.278..0.389 rows=250 loops=1)

47. 0.211 0.318 ↓ 753.0 753 1

Sort (cost=0.03..0.04 rows=1 width=108) (actual time=0.278..0.318 rows=753 loops=1)

  • Sort Key: lat_2.loan_id, lat_2.id DESC
  • Sort Method: quicksort Memory: 83kB
48. 0.107 0.107 ↓ 753.0 753 1

CTE Scan on loans_and_transactions lat_2 (cost=0.00..0.02 rows=1 width=108) (actual time=0.001..0.107 rows=753 loops=1)

49. 1.398 486.235 ↓ 245.0 490 1

Hash Semi Join (cost=0.09..0.18 rows=2 width=113) (actual time=485.985..486.235 rows=490 loops=1)

  • Hash Cond: (ae.loan_id = ae2.loan_id)
50. 483.244 483.244 ↓ 166.0 498 1

CTE Scan on adjustment_events ae (cost=0.00..0.06 rows=3 width=113) (actual time=483.181..483.244 rows=498 loops=1)

51. 1.166 1.593 ↓ 124.5 249 1

Hash (cost=0.07..0.07 rows=2 width=4) (actual time=1.593..1.593 rows=249 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
52. 0.427 0.427 ↓ 124.5 249 1

CTE Scan on adjustment_events ae2 (cost=0.00..0.07 rows=2 width=4) (actual time=0.008..0.427 rows=249 loops=1)

  • Filter: (adjustment_type = ANY ('{interest_change,sale}'::text[]))
  • Rows Removed by Filter: 249