explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IwSf

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 75,675.708 ↑ 1.0 1 1

Aggregate (cost=5,556,175,040.90..5,556,175,040.91 rows=1 width=8) (actual time=75,675.708..75,675.708 rows=1 loops=1)

2. 0.001 75,675.704 ↓ 0.0 0 1

Nested Loop (cost=1.87..5,556,175,040.90 rows=1 width=0) (actual time=75,675.704..75,675.704 rows=0 loops=1)

3. 2,702.995 75,675.703 ↓ 0.0 0 1

Nested Loop (cost=1.44..5,556,146,666.10 rows=59,817 width=16) (actual time=75,675.703..75,675.703 rows=0 loops=1)

  • Join Filter: (r.orderid = p.orderid)
4. 1,051.146 1,051.146 ↓ 1.0 11,986,927 1

Index Only Scan using packages_idx_orderid on packages p (cost=0.43..346,040.66 rows=11,963,482 width=8) (actual time=0.037..1,051.146 rows=11,986,927 loops=1)

  • Heap Fetches: 407883
5. 0.000 71,921.562 ↓ 0.0 0 11,986,927

Materialize (cost=1.00..5,555,621,173.20 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=11,986,927)

6. 0.000 73,043.583 ↓ 0.0 0 1

Nested Loop (cost=1.00..5,555,621,173.20 rows=1 width=8) (actual time=73,043.583..73,043.583 rows=0 loops=1)

  • Join Filter: (rl.routeid = r.id)
7. 45,865.740 73,043.583 ↓ 0.0 0 1

Nested Loop (cost=1.00..5,555,275,421.85 rows=1 width=8) (actual time=73,043.583..73,043.583 rows=0 loops=1)

  • Join Filter: (rl.id = rla.legid)
  • Rows Removed by Join Filter: 454612316
8. 3,250.879 3,250.879 ↓ 99.8 11,963,482 1

Index Scan using route_legs_action_type_idx on route_leg_actions rla (cost=0.44..398,642.51 rows=119,890 width=8) (actual time=0.032..3,250.879 rows=11,963,482 loops=1)

  • Index Cond: (actiontype = 2)
9. 23,926.639 23,926.964 ↓ 2.2 38 11,963,482

Materialize (cost=0.56..5,554,846,207.43 rows=17 width=16) (actual time=0.000..0.002 rows=38 loops=11,963,482)

10. 0.211 0.325 ↓ 2.2 38 1

Index Scan using route_legs_created_idx on route_legs rl (cost=0.56..5,554,846,207.35 rows=17 width=16) (actual time=0.057..0.325 rows=38 loops=1)

  • Index Cond: ((createdtime > (CURRENT_DATE - 1)) AND ((hubcode)::text = 'GAR'::text))
  • Filter: ((NOT frozen) AND (deliverystatus IS NULL) AND (drivers_needed_count > (SubPlan 1)))
11.          

SubPlan (for Index Scan)

12. 0.038 0.114 ↑ 1.0 1 38

Aggregate (cost=278,583.05..278,583.06 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=38)

13. 0.076 0.076 ↓ 0.0 0 38

Index Scan using route_leg_drivers_pkey on route_leg_drivers (cost=0.56..278,583.05 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=38)

  • Index Cond: (route_leg_id = rl.id)
  • Filter: ((offer_canceled_time IS NULL) AND (leg_failed_time IS NULL))
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on routes r (cost=0.00..196,207.82 rows=11,963,482 width=16) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using orders_pkey on orders (cost=0.43..0.47 rows=1 width=8) (never executed)

  • Index Cond: (id = r.orderid)
  • Heap Fetches: 0
Planning time : 0.765 ms
Execution time : 75,675.801 ms