explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 59DI

Settings
# exclusive inclusive rows x rows loops node
1. 4.644 4,625.104 ↓ 19,908.0 19,908 1

Subquery Scan on core (cost=80,293.21..80,293.27 rows=1 width=134) (actual time=4,533.469..4,625.104 rows=19,908 loops=1)

  • Filter: (core.rown_per_override_id = 1)
  • Rows Removed by Filter: 19522
  • Buffers: shared hit=1694731, temp read=4678 written=1314
2. 80.523 4,620.460 ↓ 39,430.0 39,430 1

WindowAgg (cost=80,293.21..80,293.26 rows=1 width=134) (actual time=4,533.459..4,620.460 rows=39,430 loops=1)

  • Buffers: shared hit=1694731, temp read=4678 written=1314
3. 53.136 4,539.937 ↓ 39,430.0 39,430 1

Sort (cost=80,293.21..80,293.22 rows=1 width=134) (actual time=4,533.431..4,539.937 rows=39,430 loops=1)

  • Sort Key: ov_1.id, d_1.cpt_day
  • Sort Method: external sort Disk: 5240kB
  • Buffers: shared hit=1694731, temp read=4678 written=1314
4. 25.651 4,486.801 ↓ 39,430.0 39,430 1

Nested Loop Left Join (cost=79,579.20..80,293.20 rows=1 width=134) (actual time=332.697..4,486.801 rows=39,430 loops=1)

  • Buffers: shared hit=1694731, temp read=4023 written=659
5. 23.867 716.388 ↓ 22,974.0 22,974 1

Nested Loop (cost=79,578.78..80,278.61 rows=1 width=148) (actual time=332.617..716.388 rows=22,974 loops=1)

  • Join Filter: (((ov_1.node)::text = (d.origin_node)::text) AND ((ov_1.lane)::text = (d.truck_filter_lane)::text) AND ((ov_1.equipment_type)::text = (d.equipment_type)::text))
  • Buffers: shared hit=129251, temp read=4023 written=659
6. 110.465 485.755 ↓ 5,743.5 22,974 1

Merge Join (cost=79,578.23..80,259.03 rows=4 width=123) (actual time=332.582..485.755 rows=22,974 loops=1)

  • Merge Cond: (((d_1.truck_filter_lane)::text = (ov_1.lane)::text) AND ((d_1.origin_node)::text = (ov_1.node)::text) AND ((d_1.equipment_type)::text = (ov_1.equipment_type)::text))
  • Join Filter: ((ov_1.dow IS NULL) OR (upper((ov_1.dow)::text) = (d_1.dow)::text))
  • Rows Removed by Join Filter: 121654
  • Buffers: shared hit=9794, temp read=4023 written=659
7. 202.954 224.859 ↓ 1.0 39,128 1

Sort (cost=27,972.65..28,067.91 rows=38,107 width=54) (actual time=212.277..224.859 rows=39,128 loops=1)

  • Sort Key: d_1.truck_filter_lane, d_1.origin_node, d_1.equipment_type
  • Sort Method: external merge Disk: 2656kB
  • Buffers: shared hit=9148, temp read=333 written=333
8. 16.305 21.905 ↓ 1.0 39,128 1

Bitmap Heap Scan on truck_fill_defaults d_1 (cost=4,779.03..25,073.13 rows=38,107 width=54) (actual time=6.370..21.905 rows=39,128 loops=1)

  • Recheck Cond: ((cpt_day >= ('now'::cstring)::date) AND (cpt_day <= (('now'::cstring)::date + 7)))
  • Heap Blocks: exact=5135
  • Buffers: shared hit=9148
9. 5.600 5.600 ↓ 1.0 39,128 1

Bitmap Index Scan on truck_fill_defaults_cpt_day_idx (cost=0.00..4,769.51 rows=38,107 width=0) (actual time=5.600..5.600 rows=39,128 loops=1)

  • Index Cond: ((cpt_day >= ('now'::cstring)::date) AND (cpt_day <= (('now'::cstring)::date + 7)))
  • Buffers: shared hit=4013
10. 143.131 150.431 ↓ 5.2 149,203 1

Sort (cost=51,605.55..51,677.61 rows=28,824 width=110) (actual time=120.268..150.431 rows=149,203 loops=1)

  • Sort Key: ov_1.lane, ov_1.node, ov_1.equipment_type
  • Sort Method: external sort Disk: 2608kB
  • Buffers: shared hit=646, temp read=2008 written=326
11. 5.396 7.300 ↑ 1.2 24,977 1

Bitmap Heap Scan on truck_fill_overrides ov_1 (cost=2,991.94..49,470.41 rows=28,824 width=110) (actual time=1.957..7.300 rows=24,977 loops=1)

  • Recheck Cond: ((version)::text = 'e741a9f1-cbf4-455c-add9-3355ca626bd7'::text)
  • Heap Blocks: exact=434
  • Buffers: shared hit=646
12. 1.904 1.904 ↑ 1.2 24,977 1

Bitmap Index Scan on truck_fill_overrides_version_idx (cost=0.00..2,984.73 rows=28,824 width=0) (actual time=1.904..1.904 rows=24,977 loops=1)

  • Index Cond: ((version)::text = 'e741a9f1-cbf4-455c-add9-3355ca626bd7'::text)
  • Buffers: shared hit=212
13. 206.766 206.766 ↑ 1.0 1 22,974

Index Scan using sort_idx on truck_fill_defaults d (cost=0.55..4.88 rows=1 width=70) (actual time=0.008..0.009 rows=1 loops=22,974)

  • Index Cond: (((origin_node)::text = (d_1.origin_node)::text) AND ((truck_filter_lane)::text = (d_1.truck_filter_lane)::text) AND ((equipment_type)::text = (d_1.equipment_type)::text) AND (cpt_day = d_1.cpt_day) AND ((dow)::text = (d_1.dow)::text))
  • Buffers: shared hit=119457
14. 3,744.762 3,744.762 ↓ 2.0 2 22,974

Index Scan using truck_fill_overrides_lane_idx on truck_fill_overrides ov (cost=0.43..14.58 rows=1 width=110) (actual time=0.105..0.163 rows=2 loops=22,974)

  • Index Cond: ((lane)::text = (d.truck_filter_lane)::text)
  • Filter: (is_active AND ((node)::text = (d.origin_node)::text) AND ((equipment_type)::text = (d.equipment_type)::text) AND ((dow IS NULL) OR (upper((dow)::text) = (d.dow)::text)))
  • Rows Removed by Filter: 643
  • Buffers: shared hit=1565480
Planning time : 4.569 ms
Execution time : 4,629.207 ms