#	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

 Sort  (cost=45757.64..45757.65 rows=2 width=145) (actual time=7814.780..7814.817 rows=471 loops=1)
   Sort Key: ae.loan_id
   Sort Method: quicksort  Memory: 76kB
   CTE sale_dates
     ->  GroupAggregate  (cost=22805.61..22805.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)
           ->  Sort  (cost=22805.61..22805.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
                 ->  Hash Join  (cost=16589.82..22805.26 rows=17 width=8) (actual time=121.716..144.856 rows=608 loops=1)
                       Hash Cond: (parent.id = child.participation_loan_id)
                       ->  Hash Semi Join  (cost=16479.30..22694.52 rows=17 width=12) (actual time=121.152..144.124 rows=304 loops=1)
                             Hash Cond: (transactions.id = x.id)
                             ->  Hash Join  (cost=87.00..5983.98 rows=17 width=16) (actual time=0.701..13.996 rows=304 loops=1)
                                   Hash Cond: (transactions.loan_id = parent.id)
                                   ->  Seq Scan on transactions  (cost=0.00..5889.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
                                   ->  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
                                         ->  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
                             ->  Hash  (cost=15067.48..15067.48 rows=80706 width=4) (actual time=120.297..120.297 rows=80131 loops=1)
                                   Buckets: 131072  Batches: 2  Memory Usage: 2434kB
                                   ->  Subquery Scan on x  (cost=12189.92..15067.48 rows=80706 width=4) (actual time=51.785..106.049 rows=80131 loops=1)
                                         Filter: (((x.code)::text !~~ 'RV%'::text) AND ((COALESCE(x.next_code, ''::character varying))::text !~~ 'RV%'::text))
                                         Rows Removed by Filter: 2085
                                         ->  WindowAgg  (cost=12189.92..13834.24 rows=82216 width=43) (actual time=51.783..92.435 rows=82216 loops=1)
                                               ->  Sort  (cost=12189.92..12395.46 rows=82216 width=11) (actual time=51.774..59.976 rows=82216 loops=1)
                                                     Sort Key: transactions_1.loan_id, transactions_1.id
                                                     Sort Method: external merge  Disk: 1704kB
                                                     ->  Seq Scan on transactions transactions_1  (cost=0.00..5478.16 rows=82216 width=11) (actual time=0.005..14.983 rows=82216 loops=1)
                       ->  Hash  (cost=86.34..86.34 rows=1934 width=4) (actual time=0.557..0.557 rows=640 loops=1)
                             Buckets: 2048  Batches: 1  Memory Usage: 39kB
                             ->  Seq Scan on loans child  (cost=0.00..86.34 rows=1934 width=4) (actual time=0.010..0.402 rows=1934 loops=1)
   CTE loans_and_transactions
     ->  Result  (cost=22950.59..22950.85 rows=1 width=84) (actual time=7664.734..7667.351 rows=753 loops=1)
           ->  Sort  (cost=22950.59..22950.59 rows=1 width=56) (actual time=7664.705..7664.773 rows=753 loops=1)
                 Sort Key: transactions_2.id
                 Sort Method: quicksort  Memory: 83kB
                 ->  Nested Loop Semi Join  (cost=12277.17..22950.58 rows=1 width=56) (actual time=78.941..7663.904 rows=753 loops=1)
                       Join Filter: (transactions_2.id = x_1.id)
                       Rows Removed by Join Filter: 9637031
                       ->  Nested Loop  (cost=87.25..6874.27 rows=1 width=60) (actual time=1.089..60.345 rows=775 loops=1)
                             ->  Hash Join  (cost=87.00..6008.07 rows=69 width=24) (actual time=1.021..13.892 rows=778 loops=1)
                                   Hash Cond: (transactions_2.loan_id = l.id)
                                   ->  Seq Scan on transactions transactions_2  (cost=0.00..5889.24 rows=2522 width=20) (actual time=0.011..11.358 rows=2504 loops=1)
                                         Filter: ((date >= '2018-07-01'::date) AND (date <= '2018-07-31'::date))
                                         Rows Removed by Filter: 79712
                                   ->  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
                                         ->  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
                             ->  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)
                       ->  Subquery Scan on x_1  (cost=12189.92..15067.48 rows=80706 width=4) (actual time=0.084..8.838 rows=12436 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
                             ->  WindowAgg  (cost=12189.92..13834.24 rows=82216 width=43) (actual time=0.084..6.765 rows=12778 loops=775)
                                   ->  Sort  (cost=12189.92..12395.46 rows=82216 width=11) (actual time=0.082..1.418 rows=12779 loops=775)
                                         Sort Key: transactions_3.loan_id, transactions_3.id
                                         Sort Method: external sort  Disk: 2024kB
                                         ->  Seq Scan on transactions transactions_3  (cost=0.00..5478.16 rows=82216 width=11) (actual time=0.008..16.367 rows=82216 loops=1)
   CTE adjustment_events
     ->  Unique  (cost=0.62..0.64 rows=3 width=145) (actual time=7814.068..7814.237 rows=489 loops=1)
           ->  Sort  (cost=0.62..0.62 rows=3 width=145) (actual time=7814.068..7814.110 rows=499 loops=1)
                 Sort Key: lat.loan_id, lat.date
                 Sort Method: quicksort  Memory: 79kB
                 ->  Hash Right Join  (cost=0.15..0.59 rows=3 width=145) (actual time=7813.388..7813.885 rows=499 loops=1)
                       Hash Cond: (sd.loan_id = lat.loan_id)
                       ->  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)
                       ->  Hash  (cost=0.11..0.11 rows=3 width=140) (actual time=7668.340..7668.340 rows=499 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 45kB
                             ->  Append  (cost=0.00..0.11 rows=3 width=140) (actual time=7667.594..7668.265 rows=499 loops=1)
                                   ->  CTE Scan on loans_and_transactions lat  (cost=0.00..0.02 rows=1 width=140) (actual time=7667.593..7667.671 rows=3 loops=1)
                                         Filter: ((code)::text = 'TR'::text)
                                         Rows Removed by Filter: 750
                                   ->  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
                                   ->  Unique  (cost=0.03..0.04 rows=1 width=140) (actual time=0.294..0.408 rows=250 loops=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
                                               ->  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)
   ->  Hash Semi Join  (cost=0.09..0.18 rows=2 width=145) (actual time=7814.441..7814.661 rows=471 loops=1)
         Hash Cond: (ae.loan_id = ae2.loan_id)
         ->  CTE Scan on adjustment_events ae  (cost=0.00..0.06 rows=3 width=145) (actual time=7814.069..7814.127 rows=489 loops=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
               ->  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
 Planning time: 1.546 ms
 Execution time: 7820.949 ms
(98 rows)

Per node type stats

node type	count	sum of times	% of query
Append	1	0.047 ms	0.0 %
CTE Scan	6	15,627.719 ms	200.0 %
Function Scan	1	45.124 ms	0.6 %
GroupAggregate	1	0.180 ms	0.0 %
Hash	6	14.612 ms	0.2 %
Hash Join	3	1.865 ms	0.0 %
Hash Right Join	1	0.194 ms	0.0 %
Hash Semi Join	2	10.005 ms	0.1 %
Nested Loop	1	1.329 ms	0.0 %
Nested Loop Semi Join	1	754.109 ms	9.6 %
Result	1	2.578 ms	0.0 %
Seq Scan	7	57.857 ms	0.7 %
Sort	7	1,129.276 ms	14.5 %
Subquery Scan	2	1,620.189 ms	20.7 %
Unique	2	0.200 ms	0.0 %
WindowAgg	2	4,176.384 ms	53.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
loans	3	1.999 ms	0.0 %
Seq Scan	3	1.999 ms	100.0 %
transactions	4	55.858 ms	0.7 %
Seq Scan	4	55.858 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5zV2

Color mode:

Visible columns:

Per node type stats

Per table stats