#	exclusive	inclusive	rows x	rows	loops	node
1.	49.621	1,292.298	↓ 574.6	15,515	1	→ Result (cost=51,131.07..51,138.16 rows=27 width=84) (actual time=1,241.450..1,292.298 rows=15,515 loops=1)
2.	8.388	1,242.677	↓ 574.6	15,515	1	→ Sort (cost=51,131.07..51,131.14 rows=27 width=56) (actual time=1,241.424..1,242.677 rows=15,515 loops=1) Sort Key: transactions.id Sort Method: quicksort Memory: 1597kB
3.	7.838	1,234.289	↓ 574.6	15,515	1	→ Nested Loop (cost=16,479.55..51,130.43 rows=27 width=56) (actual time=134.412..1,234.289 rows=15,515 loops=1)
4.	20.601	181.407	↓ 7.2	15,834	1	→ Hash Semi Join (cost=16,479.30..23,369.58 rows=2,212 width=28) (actual time=134.338..181.407 rows=15,834 loops=1) Hash Cond: (transactions.id = x.id)
5.	15.595	27.757	↓ 7.2	16,312	1	→ Hash Join (cost=87.00..6,602.76 rows=2,253 width=24) (actual time=1.123..27.757 rows=16,312 loops=1) Hash Cond: (transactions.loan_id = l.id)
6.	11.057	11.057	↑ 1.0	82,216	1	→ Seq Scan on transactions (cost=0.00..5,478.16 rows=82,216 width=20) (actual time=0.011..11.057 rows=82,216 loops=1)
7.	0.069	1.105	↓ 6.0	319	1	→ Hash (cost=86.34..86.34 rows=53 width=8) (actual time=1.105..1.105 rows=319 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 21kB
8.	1.036	1.036	↓ 6.0	319	1	→ Seq Scan on loans l (cost=0.00..86.34 rows=53 width=8) (actual time=0.007..1.036 rows=319 loops=1) Filter: (is_community_advantage AND (community_advantage_approved_date IS NOT NULL)) Rows Removed by Filter: 1615
9.	16.299	133.049	↑ 1.0	80,131	1	→ Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=133.049..133.049 rows=80,131 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 2434kB
10.	14.889	116.750	↑ 1.0	80,131	1	→ Subquery Scan on x (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=56.539..116.750 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
11.	36.282	101.861	↑ 1.0	82,216	1	→ WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=56.537..101.861 rows=82,216 loops=1)
12.	48.108	65.579	↑ 1.0	82,216	1	→ Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=56.528..65.579 rows=82,216 loops=1) Sort Key: transactions_1.loan_id, transactions_1.id Sort Method: external merge Disk: 1704kB
13.	17.471	17.471	↑ 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.009..17.471 rows=82,216 loops=1)
14.	1,045.044	1,045.044	↑ 5.0	1	15,834	→ Function Scan on fn_prior_transaction pt (cost=0.25..12.75 rows=5 width=36) (actual time=0.066..0.066 rows=1 loops=15,834) Filter: (transactions.id = reference_transaction_id)

 Result  (cost=51131.07..51138.16 rows=27 width=84) (actual time=1241.450..1292.298 rows=15515 loops=1)
   ->  Sort  (cost=51131.07..51131.14 rows=27 width=56) (actual time=1241.424..1242.677 rows=15515 loops=1)
         Sort Key: transactions.id
         Sort Method: quicksort  Memory: 1597kB
         ->  Nested Loop  (cost=16479.55..51130.43 rows=27 width=56) (actual time=134.412..1234.289 rows=15515 loops=1)
               ->  Hash Semi Join  (cost=16479.30..23369.58 rows=2212 width=28) (actual time=134.338..181.407 rows=15834 loops=1)
                     Hash Cond: (transactions.id = x.id)
                     ->  Hash Join  (cost=87.00..6602.76 rows=2253 width=24) (actual time=1.123..27.757 rows=16312 loops=1)
                           Hash Cond: (transactions.loan_id = l.id)
                           ->  Seq Scan on transactions  (cost=0.00..5478.16 rows=82216 width=20) (actual time=0.011..11.057 rows=82216 loops=1)
                           ->  Hash  (cost=86.34..86.34 rows=53 width=8) (actual time=1.105..1.105 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.007..1.036 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=133.049..133.049 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=56.539..116.750 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=56.537..101.861 rows=82216 loops=1)
                                       ->  Sort  (cost=12189.92..12395.46 rows=82216 width=11) (actual time=56.528..65.579 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.009..17.471 rows=82216 loops=1)
               ->  Function Scan on fn_prior_transaction pt  (cost=0.25..12.75 rows=5 width=36) (actual time=0.066..0.066 rows=1 loops=15834)
                     Filter: (transactions.id = reference_transaction_id)
 Planning time: 0.690 ms
 Execution time: 1295.941 ms
(29 rows)

Per node type stats

node type	count	sum of times	% of query
Function Scan	1	1,045.044 ms	80.9 %
Hash	2	16.368 ms	1.3 %
Hash Join	1	15.595 ms	1.2 %
Hash Semi Join	1	20.601 ms	1.6 %
Nested Loop	1	7.838 ms	0.6 %
Result	1	49.621 ms	3.8 %
Seq Scan	3	29.564 ms	2.3 %
Sort	2	56.496 ms	4.4 %
Subquery Scan	1	14.889 ms	1.2 %
WindowAgg	1	36.282 ms	2.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
loans	1	1.036 ms	0.1 %
Seq Scan	1	1.036 ms	100.0 %
transactions	2	28.528 ms	2.2 %
Seq Scan	2	28.528 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4WGN

Color mode:

Visible columns:

Per node type stats

Per table stats