explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rikt : opt1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 183.271 1,748.846 ↓ 189.2 444,166 1

Subquery Scan on final (cost=126,646.72..141,916.75 rows=2,347 width=414) (actual time=979.542..1,748.846 rows=444,166 loops=1)

  • Filter: (final.maintenance_scheme = final.max_maintenance_scheme)
  • Rows Removed by Filter: 25,320
  • Functions: 25
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 2.507 ms, Inlining 0.000 ms, Optimization 0.797 ms, Emission 17.536 ms, Total 20.841 ms
2.          

CTE stage

3. 42.155 42.155 ↑ 1.0 469,486 1

Seq Scan on eib_contract_object_maintenance_stg (cost=0.00..18,670.86 rows=469,486 width=199) (actual time=0.010..42.155 rows=469,486 loops=1)

4. 453.834 1,565.575 ↑ 1.0 469,486 1

WindowAgg (cost=107,975.86..117,365.58 rows=469,486 width=354) (actual time=979.533..1,565.575 rows=469,486 loops=1)

5. 517.429 1,111.741 ↑ 1.0 469,486 1

Sort (cost=107,975.86..109,149.57 rows=469,486 width=322) (actual time=979.508..1,111.741 rows=469,486 loops=1)

  • Sort Key: stage.contract_object_sk, sort.cnt DESC
  • Sort Method: external merge Disk: 106,848kB
6. 161.910 594.312 ↑ 1.0 469,486 1

Hash Left Join (cost=11,743.65..22,391.88 rows=469,486 width=322) (actual time=340.466..594.312 rows=469,486 loops=1)

  • Hash Cond: (stage.maintenance_scheme = sort.maintenance_scheme)
7. 91.956 91.956 ↑ 1.0 469,486 1

CTE Scan on stage (cost=0.00..9,389.72 rows=469,486 width=314) (actual time=0.011..91.956 rows=469,486 loops=1)

8. 0.013 340.446 ↑ 5.4 37 1

Hash (cost=11,741.15..11,741.15 rows=200 width=40) (actual time=340.444..340.446 rows=37 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
9. 0.005 340.433 ↑ 5.3 38 1

Subquery Scan on sort (cost=11,737.15..11,741.15 rows=200 width=40) (actual time=340.423..340.433 rows=38 loops=1)

10. 105.316 340.428 ↑ 5.3 38 1

HashAggregate (cost=11,737.15..11,739.15 rows=200 width=40) (actual time=340.421..340.428 rows=38 loops=1)

  • Group Key: stage_1.maintenance_scheme
  • Batches: 1 Memory Usage: 40kB
11. 235.112 235.112 ↑ 1.0 469,486 1

CTE Scan on stage stage_1 (cost=0.00..9,389.72 rows=469,486 width=32) (actual time=0.000..235.112 rows=469,486 loops=1)

Planning time : 0.167 ms
Execution time : 1,787.941 ms