explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4WGN

Settings
# 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)