explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cEgv : Optimization for: Optimization for: plan #gfML; plan #Cqgl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 109,516.212 109,516.212 ↑ 38.0 1 1

Unique (cost=13,631,163.59..13,631,166.72 rows=38 width=160) (actual time=109,516.212..109,516.212 rows=1 loops=1)

2.          

CTE original_instalment

3. 7,109.245 95,252.114 ↑ 1.0 47,768,807 1

Unique (cost=12,043,187.39..12,416,459.30 rows=49,769,588 width=44) (actual time=82,061.455..95,252.114 rows=47,768,807 loops=1)

4. 33,259.736 88,142.869 ↑ 1.0 49,449,042 1

Sort (cost=12,043,187.39..12,167,611.36 rows=49,769,588 width=44) (actual time=82,061.454..88,142.869 rows=49,449,042 loops=1)

  • Sort Key: pp.loan_id, i_1.number
  • Sort Method: external merge Disk: 1558480kB
5. 30,820.506 54,883.133 ↑ 1.0 49,449,042 1

Hash Join (cost=83,060.74..2,618,450.86 rows=49,769,588 width=44) (actual time=2,026.419..54,883.133 rows=49,449,042 loops=1)

  • Hash Cond: (i_1.payment_plan_id = pp.id)
6. 22,037.939 22,037.939 ↑ 1.0 49,556,096 1

Seq Scan on instalment i_1 (cost=0.00..1,590,748.25 rows=49,894,325 width=32) (actual time=0.016..22,037.939 rows=49,556,096 loops=1)

7. 349.796 2,024.688 ↓ 1.0 1,846,308 1

Hash (cost=53,262.84..53,262.84 rows=1,816,232 width=8) (actual time=2,024.688..2,024.688 rows=1,846,308 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 13131kB
8. 1,674.892 1,674.892 ↓ 1.0 1,846,308 1

Seq Scan on payment_plan pp (cost=0.00..53,262.84 rows=1,816,232 width=8) (actual time=0.007..1,674.892 rows=1,846,308 loops=1)

  • Filter: active
  • Rows Removed by Filter: 4432
9.          

CTE loan_settlement

10. 2.013 6.645 ↑ 1.0 1 1

Nested Loop (cost=1.71..18.13 rows=1 width=70) (actual time=6.643..6.645 rows=1 loops=1)

11. 4.632 4.632 ↑ 1.0 1 1

Nested Loop (cost=1.28..17.44 rows=1 width=45) (actual time=4.631..4.632 rows=1 loops=1)