explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5zV2

Settings
# exclusive inclusive rows x rows loops node
1. 0.156 7,814.817 ↓ 235.5 471 1

Sort (cost=45,757.64..45,757.65 rows=2 width=145) (actual time=7,814.780..7,814.817 rows=471 loops=1)

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

CTE sale_dates

3. 0.180 145.263 ↓ 17.9 304 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Subquery Scan on x (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=51.785..106.049 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. 32.459 92.435 ↑ 1.0 82,216 1

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

14. 44.993 59.976 ↑ 1.0 82,216 1

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

  • Sort Key: transactions_1.loan_id, transactions_1.id
  • Sort Method: external merge Disk: 1704kB
15. 14.983 14.983 ↑ 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.005..14.983 rows=82,216 loops=1)

16. 0.155 0.557 ↑ 3.0 640 1

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

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

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

18.          

CTE loans_and_transactions

19. 2.578 7,667.351 ↓ 753.0 753 1

Result (cost=22,950.59..22,950.85 rows=1 width=84) (actual time=7,664.734..7,667.351 rows=753 loops=1)

20. 0.869 7,664.773 ↓ 753.0 753 1

Sort (cost=22,950.59..22,950.59 rows=1 width=56) (actual time=7,664.705..7,664.773 rows=753 loops=1)

  • Sort Key: transactions_2.id
  • Sort Method: quicksort Memory: 83kB
21. 754.109 7,663.904 ↓ 753.0 753 1

Nested Loop Semi Join (cost=12,277.17..22,950.58 rows=1 width=56) (actual time=78.941..7,663.904 rows=753 loops=1)

  • Join Filter: (transactions_2.id = x_1.id)
  • Rows Removed by Join Filter: 9637031
22. 1.329 60.345 ↓ 775.0 775 1

Nested Loop (cost=87.25..6,874.27 rows=1 width=60) (actual time=1.089..60.345 rows=775 loops=1)

23. 1.530 13.892 ↓ 11.3 778 1

Hash Join (cost=87.00..6,008.07 rows=69 width=24) (actual time=1.021..13.892 rows=778 loops=1)

  • Hash Cond: (transactions_2.loan_id = l.id)
24. 11.358 11.358 ↑ 1.0 2,504 1

Seq Scan on transactions transactions_2 (cost=0.00..5,889.24 rows=2,522 width=20) (actual time=0.011..11.358 rows=2,504 loops=1)

  • Filter: ((date >= '2018-07-01'::date) AND (date <= '2018-07-31'::date))
  • Rows Removed by Filter: 79712
25. 0.054 1.004 ↓ 6.0 319 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
26. 0.950 0.950 ↓ 6.0 319 1

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

  • Filter: (is_community_advantage AND (community_advantage_approved_date IS NOT NULL))
  • Rows Removed by Filter: 1615
27. 45.124 45.124 ↑ 5.0 1 778

Function Scan on fn_prior_transaction pt (cost=0.25..12.75 rows=5 width=36) (actual time=0.058..0.058 rows=1 loops=778)

  • Filter: (transactions_2.id = reference_transaction_id)
28. 1,606.575 6,849.450 ↑ 6.5 12,436 775

Subquery Scan on x_1 (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=0.084..8.838 rows=12,436 loops=775)

  • Filter: (((x_1.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_1.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 342
29. 4,143.925 5,242.875 ↑ 6.4 12,778 775

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=0.084..6.765 rows=12,778 loops=775)

30. 1,082.583 1,098.950 ↑ 6.4 12,779 775

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=0.082..1.418 rows=12,779 loops=775)

  • Sort Key: transactions_3.loan_id, transactions_3.id
  • Sort Method: external sort Disk: 2024kB
31. 16.367 16.367 ↑ 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.008..16.367 rows=82,216 loops=1)

32.          

CTE adjustment_events

33. 0.127 7,814.237 ↓ 163.0 489 1

Unique (cost=0.62..0.64 rows=3 width=145) (actual time=7,814.068..7,814.237 rows=489 loops=1)

34. 0.225 7,814.110 ↓ 166.3 499 1

Sort (cost=0.62..0.62 rows=3 width=145) (actual time=7,814.068..7,814.110 rows=499 loops=1)

  • Sort Key: lat.loan_id, lat.date
  • Sort Method: quicksort Memory: 79kB
35. 0.194 7,813.885 ↓ 166.3 499 1

Hash Right Join (cost=0.15..0.59 rows=3 width=145) (actual time=7,813.388..7,813.885 rows=499 loops=1)

  • Hash Cond: (sd.loan_id = lat.loan_id)
36. 145.351 145.351 ↓ 17.9 304 1

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

37. 0.075 7,668.340 ↓ 166.3 499 1

Hash (cost=0.11..0.11 rows=3 width=140) (actual time=7,668.340..7,668.340 rows=499 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
38. 0.047 7,668.265 ↓ 166.3 499 1

Append (cost=0.00..0.11 rows=3 width=140) (actual time=7,667.594..7,668.265 rows=499 loops=1)

39. 7,667.671 7,667.671 ↓ 3.0 3 1

CTE Scan on loans_and_transactions lat (cost=0.00..0.02 rows=1 width=140) (actual time=7,667.593..7,667.671 rows=3 loops=1)

  • Filter: ((code)::text = 'TR'::text)
  • Rows Removed by Filter: 750
40. 0.139 0.139 ↓ 246.0 246 1

CTE Scan on loans_and_transactions lat_1 (cost=0.00..0.02 rows=1 width=140) (actual time=0.003..0.139 rows=246 loops=1)

  • Filter: (current_interest_rate <> prior_interest_rate)
  • Rows Removed by Filter: 507
41. 0.073 0.408 ↓ 250.0 250 1

Unique (cost=0.03..0.04 rows=1 width=140) (actual time=0.294..0.408 rows=250 loops=1)

42. 0.223 0.335 ↓ 753.0 753 1

Sort (cost=0.03..0.04 rows=1 width=140) (actual time=0.294..0.335 rows=753 loops=1)

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

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

44. 0.174 7,814.661 ↓ 235.5 471 1

Hash Semi Join (cost=0.09..0.18 rows=2 width=145) (actual time=7,814.441..7,814.661 rows=471 loops=1)

  • Hash Cond: (ae.loan_id = ae2.loan_id)
45. 7,814.127 7,814.127 ↓ 163.0 489 1

CTE Scan on adjustment_events ae (cost=0.00..0.06 rows=3 width=145) (actual time=7,814.069..7,814.127 rows=489 loops=1)

46. 0.041 0.360 ↓ 119.5 239 1

Hash (cost=0.07..0.07 rows=2 width=4) (actual time=0.360..0.360 rows=239 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
47. 0.319 0.319 ↓ 119.5 239 1

CTE Scan on adjustment_events ae2 (cost=0.00..0.07 rows=2 width=4) (actual time=0.003..0.319 rows=239 loops=1)

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