explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fNOI : Optimization for: plan #nans

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 613.248 2,414.302 ↑ 12.5 16 1

GroupAggregate (cost=4,151,061.29..4,344,191.86 rows=200 width=24) (actual time=1,627.172..2,414.302 rows=16 loops=1)

  • Group Key: w.week_start
  • Buffers: shared hit=78, temp read=10557 written=10557
2.          

CTE weeks

3. 0.018 0.073 ↑ 62.5 16 1

Subquery Scan on d (cost=0.00..17.52 rows=1,000 width=16) (actual time=0.010..0.073 rows=16 loops=1)

4. 0.054 0.055 ↑ 62.5 16 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.008..0.055 rows=16 loops=1)

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

6. 1,142.665 1,801.054 ↑ 7.6 2,542,400 1

Sort (cost=4,151,043.77..4,199,325.91 rows=19,312,857 width=21) (actual time=1,574.043..1,801.054 rows=2,542,400 loops=1)

  • Sort Key: w.week_start
  • Sort Method: external sort Disk: 84456kB
  • Buffers: shared hit=78, temp read=10557 written=10557
7. 538.269 658.389 ↑ 7.6 2,542,400 1

Nested Loop Left Join (cost=14.94..1,021,758.75 rows=19,312,857 width=21) (actual time=0.171..658.389 rows=2,542,400 loops=1)

  • Join Filter: ((a.start_date <= w.week_end) AND ((a.end_date IS NULL) OR (a.end_date >= w.week_start)))
  • Buffers: shared hit=78
8. 1.573 2.520 ↑ 22.8 5,600 1

Nested Loop Left Join (cost=0.00..7,773.10 rows=127,618 width=20) (actual time=0.036..2.520 rows=5,600 loops=1)

  • Join Filter: ((t.start_date <= w.week_end) AND ((t.exit_date IS NULL) OR (t.exit_date >= w.week_start)))
  • Rows Removed by Join Filter: 1456
  • Buffers: shared hit=30
9. 0.099 0.099 ↑ 62.5 16 1

CTE Scan on weeks w (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.012..0.099 rows=16 loops=1)

10. 0.598 0.848 ↑ 1.0 441 16

Materialize (cost=0.00..36.70 rows=441 width=12) (actual time=0.001..0.053 rows=441 loops=16)

  • Buffers: shared hit=30
11. 0.250 0.250 ↑ 1.0 441 1

Seq Scan on asset_truck t (cost=0.00..34.50 rows=441 width=12) (actual time=0.013..0.250 rows=441 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=30
12. 117.107 117.600 ↑ 1.0 454 5,600

Materialize (cost=14.94..61.77 rows=454 width=17) (actual time=0.000..0.021 rows=454 loops=5,600)

  • Buffers: shared hit=48
13. 0.140 0.493 ↑ 1.0 454 1

Hash Left Join (cost=14.94..59.50 rows=454 width=17) (actual time=0.134..0.493 rows=454 loops=1)

  • Hash Cond: (a.contract_id = c.id)
  • Buffers: shared hit=48
14. 0.249 0.249 ↑ 1.0 454 1

Seq Scan on activity_allocation a (cost=0.00..43.32 rows=454 width=16) (actual time=0.023..0.249 rows=454 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 378
  • Buffers: shared hit=35
15. 0.017 0.104 ↑ 1.0 75 1

Hash (cost=14.00..14.00 rows=75 width=5) (actual time=0.104..0.104 rows=75 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=13
16. 0.087 0.087 ↑ 1.0 75 1

Seq Scan on customer_contract c (cost=0.00..14.00 rows=75 width=5) (actual time=0.005..0.087 rows=75 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=13
Planning time : 0.590 ms
Execution time : 2,426.815 ms