explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fpog

Settings
# exclusive inclusive rows x rows loops node
1. 131.457 21,117.366 ↑ 491.8 117,576 1

Merge Right Join (cost=451,635.13..1,319,759.02 rows=57,821,410 width=513) (actual time=20,849.284..21,117.366 rows=117,576 loops=1)

  • Merge Cond: (p.loan_id = l.loan_id)
2.          

CTE loans

3. 721.826 721.826 ↓ 1.1 117,576 1

Seq Scan on loans_loan l_1 (cost=0.00..18,062.68 rows=109,097 width=115) (actual time=0.432..721.826 rows=117,576 loops=1)

  • Filter: ((disbursed_at < '2019-11-01 00:00:00+00'::timestamp with time zone) AND ((provider)::text = 'iifl'::text))
  • Rows Removed by Filter: 60,312
4.          

CTE payments

5. 1,544.374 20,484.657 ↓ 1.1 117,516 1

GroupAggregate (cost=11,311.20..384,995.02 rows=106,000 width=108) (actual time=970.660..20,484.657 rows=117,516 loops=1)

  • Group Key: p_1.loan_id
6. 1,872.820 18,940.283 ↑ 1.4 1,849,385 1

Merge Join (cost=11,311.20..297,188.83 rows=2,644,652 width=28) (actual time=969.150..18,940.283 rows=1,849,385 loops=1)

  • Merge Cond: (p_1.loan_id = l_2.loan_id)
7. 15,557.072 15,557.072 ↑ 1.1 2,255,143 1

Index Scan using core_payment_loan_id_fc6dbb6a_uniq on core_payment p_1 (cost=0.43..239,784.34 rows=2,569,577 width=28) (actual time=0.858..15,557.072 rows=2,255,143 loops=1)

8. 655.265 1,510.391 ↓ 17.0 1,849,443 1

Sort (cost=11,310.77..11,583.51 rows=109,097 width=4) (actual time=968.280..1,510.391 rows=1,849,443 loops=1)

  • Sort Key: l_2.loan_id
  • Sort Method: external sort Disk: 2,072kB
9. 855.126 855.126 ↓ 1.1 117,576 1

CTE Scan on loans l_2 (cost=0.00..2,181.94 rows=109,097 width=4) (actual time=0.437..855.126 rows=117,576 loops=1)

10. 160.753 20,761.124 ↓ 1.1 117,516 1

Sort (cost=17,127.66..17,392.66 rows=106,000 width=108) (actual time=20,727.720..20,761.124 rows=117,516 loops=1)

  • Sort Key: p.loan_id
  • Sort Method: external sort Disk: 5,032kB
11. 20,600.371 20,600.371 ↓ 1.1 117,516 1

CTE Scan on payments p (cost=0.00..2,120.00 rows=106,000 width=108) (actual time=970.663..20,600.371 rows=117,516 loops=1)

12. 51.752 224.785 ↓ 1.1 117,576 1

Materialize (cost=31,449.77..31,995.25 rows=109,097 width=405) (actual time=121.548..224.785 rows=117,576 loops=1)

13. 137.294 173.033 ↓ 1.1 117,576 1

Sort (cost=31,449.77..31,722.51 rows=109,097 width=405) (actual time=121.540..173.033 rows=117,576 loops=1)

  • Sort Key: l.loan_id
  • Sort Method: external merge Disk: 11,464kB
14. 35.739 35.739 ↓ 1.1 117,576 1

CTE Scan on loans l (cost=0.00..2,181.94 rows=109,097 width=405) (actual time=0.022..35.739 rows=117,576 loops=1)