explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pXL3

Settings
# exclusive inclusive rows x rows loops node
1. 11.341 794.859 ↓ 4,873.0 4,873 1

GroupAggregate (cost=11,816.66..11,816.72 rows=1 width=91) (actual time=780.890..794.859 rows=4,873 loops=1)

  • Group Key: dequip.bus_pool_number, bsm.state, dequip.bus_number, dequip.bus_owner, dequip.leg_owner, dsec.schedule_number, cal.calendar_year, cal.calendar_month, dseg.envelope_number_in, dsec.schedule_date, ddrv.actual_operator_number
2. 77.707 783.518 ↓ 19,892.0 19,892 1

Sort (cost=11,816.66..11,816.67 rows=1 width=55) (actual time=780.865..783.518 rows=19,892 loops=1)

  • Sort Key: dequip.bus_pool_number, bsm.state, dequip.bus_number, dequip.bus_owner, dsec.schedule_number, dseg.envelope_number_in, dsec.schedule_date, ddrv.actual_operator_number
  • Sort Method: quicksort Memory: 3382kB
3. 9.867 705.811 ↓ 19,892.0 19,892 1

Nested Loop (cost=11,059.68..11,816.65 rows=1 width=55) (actual time=641.289..705.811 rows=19,892 loops=1)

4. 10.770 656.160 ↓ 19,892.0 19,892 1

Hash Join (cost=11,059.26..11,814.74 rows=1 width=56) (actual time=641.257..656.160 rows=19,892 loops=1)

  • Hash Cond: (bsm.border_mileage_id = bm.border_mileage_id)
5. 4.212 4.212 ↑ 1.0 27,847 1

Seq Scan on border_state_mileage bsm (cost=0.00..650.89 rows=27,889 width=15) (actual time=0.021..4.212 rows=27,847 loops=1)

6. 9.747 641.178 ↓ 18,735.0 18,735 1

Hash (cost=11,059.25..11,059.25 rows=1 width=57) (actual time=641.178..641.178 rows=18,735 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2154kB
7. 27.068 631.431 ↓ 18,735.0 18,735 1

Hash Join (cost=10,007.01..11,059.25 rows=1 width=57) (actual time=592.639..631.431 rows=18,735 loops=1)

  • Hash Cond: (((bm.carrier_code)::text = (dsec.section_owner)::text) AND ((bm.origin_code)::text = (mds.origin_location_code)::text) AND ((bm.destination_code)::text = (mds.destination_location_code)::text))
  • Join Filter: ((bm.effective_date <= (dseg.plan_depart_datetime)::date) AND ((bm.inactive_date IS NULL) OR (bm.inactive_date > (dseg.plan_depart_datetime)::date)))
  • Rows Removed by Join Filter: 34601
8. 11.829 11.829 ↑ 1.0 24,481 1

Seq Scan on border_mileage bm (cost=0.00..776.81 rows=24,481 width=33) (actual time=0.007..11.829 rows=24,481 loops=1)

9. 25.928 592.534 ↓ 18,898.0 18,898 1

Hash (cost=10,006.99..10,006.99 rows=1 width=77) (actual time=592.534..592.534 rows=18,898 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2503kB
10. 53.724 566.606 ↓ 18,898.0 18,898 1

Gather (cost=1,027.21..10,006.99 rows=1 width=77) (actual time=2.184..566.606 rows=18,898 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 26.318 512.882 ↓ 6,299.0 6,299 3 / 3

Nested Loop (cost=27.21..9,006.89 rows=1 width=77) (actual time=3.729..512.882 rows=6,299 loops=3)

  • Join Filter: ((mds.depart_date_time >= dseg.plan_depart_datetime) AND (mds.arrive_date_time <= dseg.plan_arrive_datetime) AND (mds.effective_date <= (dseg.plan_depart_datetime)::date) AND ((mds.expiration_date IS NULL) OR (mds.expiration_date > (dseg.plan_depart_datetime)::date)) AND (mds.catalog_date <= (dseg.plan_depart_datetime)::date) AND ((mds.inactive_date IS NULL) OR (mds.inactive_date > (dseg.plan_depart_datetime)::date)))
  • Rows Removed by Join Filter: 14869
12. 4.822 291.859 ↓ 414.2 2,071 3 / 3

Nested Loop (cost=26.78..8,985.28 rows=5 width=68) (actual time=3.639..291.859 rows=2,071 loops=3)

13. 3.069 276.680 ↓ 414.2 2,071 3 / 3

Nested Loop (cost=26.48..8,983.57 rows=5 width=64) (actual time=3.605..276.680 rows=2,071 loops=3)

14. 20.137 143.971 ↓ 324.1 25,928 3 / 3

Hash Join (cost=26.06..8,940.68 rows=80 width=48) (actual time=3.501..143.971 rows=25,928 loops=3)

  • Hash Cond: (dseg.calendar_period_id = cal.calendar_period_id)
15. 123.805 123.805 ↑ 1.3 68,230 3 / 3

Parallel Seq Scan on dispatch_segment dseg (cost=0.00..8,689.42 rows=85,448 width=48) (actual time=0.030..123.805 rows=68,230 loops=3)

  • Filter: (active AND (segment_type = 'R'::bpchar))
  • Rows Removed by Filter: 8056
16. 0.008 0.029 ↑ 1.0 1 3 / 3

Hash (cost=26.05..26.05 rows=1 width=16) (actual time=0.028..0.029 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.021 0.021 ↑ 1.0 1 3 / 3

Seq Scan on calendar_period cal (cost=0.00..26.05 rows=1 width=16) (actual time=0.020..0.021 rows=1 loops=3)

  • Filter: ((calendar_month = 2) AND (calendar_year = 2020))
  • Rows Removed by Filter: 29
18. 129.640 129.640 ↓ 0.0 0 77,784 / 3

Index Scan using dispatch_equipment_dispatch_section_id_bus_number_uindex on dispatch_equipment dequip (cost=0.42..0.53 rows=1 width=32) (actual time=0.004..0.005 rows=0 loops=77,784)

  • Index Cond: (dispatch_segment_id = dseg.dispatch_segment_id)
  • Filter: ((planned_bus_code IS NOT NULL) AND ((leg_owner)::text = 'GLC'::text))
  • Rows Removed by Filter: 1
19. 10.357 10.357 ↑ 1.0 1 6,214 / 3

Index Scan using dispatch_section_pk on dispatch_section dsec (cost=0.29..0.34 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=6,214)

  • Index Cond: (dispatch_section_id = dseg.dispatch_section_id)
20. 194.705 194.705 ↓ 10.0 10 6,214 / 3

Index Scan using mv_dispatch_schedule_carrier_code_schedule_number_schedule__idx on mv_dispatch_schedule mds (cost=0.43..4.29 rows=1 width=58) (actual time=0.024..0.094 rows=10 loops=6,214)

  • Index Cond: (((carrier_code)::text = (dsec.section_owner)::text) AND ((schedule_number)::text = (dsec.schedule_number)::text) AND (schedule_date = dsec.schedule_date))
21. 39.784 39.784 ↑ 1.0 1 19,892

Index Scan using dispatch_driver_dispatch_equipment_id_uindex on dispatch_driver ddrv (cost=0.42..1.91 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=19,892)

  • Index Cond: (dispatch_equipment_id = dequip.dispatch_equipment_id)
Planning time : 9.954 ms
Execution time : 795.760 ms