explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nans

Settings

Optimization(s) for this plan:

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

CTE Scan on weekly_allocation_truck_targets (cost=4,344,191.86..4,344,195.86 rows=200 width=24) (actual time=1,616.966..2,390.570 rows=16 loops=1)

  • Buffers: shared hit=78, temp read=10557 written=10557
2.          

CTE weeks

3. 0.018 0.072 ↑ 62.5 16 1

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

4. 0.053 0.054 ↑ 62.5 16 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.005..0.054 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.          

CTE weekly_allocation_truck_targets

7. 605.461 2,390.537 ↑ 12.5 16 1

GroupAggregate (cost=4,151,043.77..4,344,174.34 rows=200 width=24) (actual time=1,616.963..2,390.537 rows=16 loops=1)

  • Group Key: w.week_start
  • Buffers: shared hit=78, temp read=10557 written=10557
8. 1,128.376 1,785.076 ↑ 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,564.880..1,785.076 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
9. 536.463 656.700 ↑ 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.119..656.700 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
10. 1.643 2.637 ↑ 22.8 5,600 1

Nested Loop Left Join (cost=0.00..7,773.10 rows=127,618 width=20) (actual time=0.026..2.637 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
11. 0.098 0.098 ↑ 62.5 16 1

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

12. 0.650 0.896 ↑ 1.0 441 16

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

  • Buffers: shared hit=30
13. 0.246 0.246 ↑ 1.0 441 1

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=30
14. 117.282 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
15. 0.087 0.318 ↑ 1.0 454 1

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

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

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 378
  • Buffers: shared hit=35
17. 0.011 0.071 ↑ 1.0 75 1

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

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

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

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