explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ioTk

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 1,849.445 ↑ 34.2 6 1

HashAggregate (cost=408,983.81..408,985.86 rows=205 width=8) (actual time=1,849.442..1,849.445 rows=6 loops=1)

  • Group Key: shipper_transactions.created_by, shipper_transactions.order_id
2.          

CTE withdraw_request_orders

3. 0.143 473.634 ↓ 1.7 71 1

Nested Loop (cost=0.43..39,663.52 rows=42 width=1,053) (actual time=401.243..473.634 rows=71 loops=1)

4. 471.316 471.316 ↓ 1.6 75 1

Seq Scan on shipper_transactions shipper_transactions_1 (cost=0.00..39,274.47 rows=46 width=4) (actual time=401.188..471.316 rows=75 loops=1)

  • Filter: (withdraw_request_id = 25711)
  • Rows Removed by Filter: 796478
5. 2.175 2.175 ↑ 1.0 1 75

Index Scan using orders_pkey on orders (cost=0.43..8.45 rows=1 width=1,053) (actual time=0.028..0.029 rows=1 loops=75)

  • Index Cond: (id = shipper_transactions_1.order_id)
6.          

CTE raw_order_shipping_transactions_done

7. 0.450 476.636 ↑ 2.9 71 1

HashAggregate (cost=666.92..668.98 rows=206 width=16) (actual time=476.323..476.636 rows=71 loops=1)

  • Group Key: shipping_transactions.order_id
8. 0.153 476.186 ↑ 2.9 71 1

Nested Loop (cost=0.56..665.37 rows=206 width=16) (actual time=402.106..476.186 rows=71 loops=1)

9. 474.045 474.045 ↓ 1.7 71 1

CTE Scan on withdraw_request_orders (cost=0.00..0.84 rows=42 width=8) (actual time=401.270..474.045 rows=71 loops=1)

10. 1.988 1.988 ↑ 5.0 1 71

Index Scan using shipping_transactions_order_id_action_idx on shipping_transactions (cost=0.56..15.77 rows=5 width=12) (actual time=0.028..0.028 rows=1 loops=71)

  • Index Cond: ((order_id = withdraw_request_orders.id) AND (action = 'done'::order_status_enum))
11.          

CTE shipper_geolocations

12. 2.221 1,847.124 ↑ 2.8 71 1

HashAggregate (cost=365,230.12..365,232.12 rows=200 width=8) (actual time=1,847.074..1,847.124 rows=71 loops=1)

  • Group Key: raw_order_shipping_transactions_done.order_id
13. 1.456 1,844.903 ↑ 28.4 3,648 1

Nested Loop (cost=0.43..364,711.63 rows=103,698 width=8) (actual time=485.704..1,844.903 rows=3,648 loops=1)

14. 477.052 477.052 ↑ 2.9 71 1

CTE Scan on raw_order_shipping_transactions_done (cost=0.00..4.12 rows=206 width=16) (actual time=476.326..477.052 rows=71 loops=1)

15. 1,366.395 1,366.395 ↑ 9.9 51 71

Index Scan using shipper_geolocation_trackers_shipper_id_idx on shipper_geolocation_trackers shipper_geolocation_trackers_1 (cost=0.43..1,765.39 rows=503 width=16) (actual time=7.932..19.245 rows=51 loops=71)

  • Index Cond: (shipper_id = raw_order_shipping_transactions_done.shipper_id)
  • Filter: ((created_at >= (raw_order_shipping_transactions_done.finish_at - '00:00:05'::interval)) AND (created_at < (raw_order_shipping_transactions_done.finish_at + '01:00:00'::interval)))
  • Rows Removed by Filter: 8649
16. 0.039 1,849.396 ↑ 34.2 6 1

Nested Loop (cost=1.29..3,418.16 rows=205 width=8) (actual time=1,847.612..1,849.396 rows=6 loops=1)

  • Join Filter: (shipper_geolocations.order_id = shipper_transactions.order_id)
17. 0.360 1,849.267 ↑ 11.2 6 1

Nested Loop (cost=0.86..3,375.50 rows=67 width=8) (actual time=1,847.556..1,849.267 rows=6 loops=1)

  • Join Filter: (((sqrt((power((111.120000000000005::double precision * (shipper_geolocation_trackers.latitude - order_routes.dest_lat)), 2::double precision) + power(((111.120000000000005::double precision * (shipper_geolocation_trackers.longitude - order_routes.dest_lng)) * cos((order_routes.dest_lat / 92.2150000000000034::double precision))), 2::double precision))) * 1000::double precision) / 1000::double precision) > 1::double precision)
  • Rows Removed by Join Filter: 65
18. 0.079 1,847.984 ↑ 2.8 71 1

Nested Loop (cost=0.43..1,697.00 rows=200 width=20) (actual time=1,847.134..1,847.984 rows=71 loops=1)

19. 1,847.195 1,847.195 ↑ 2.8 71 1

CTE Scan on shipper_geolocations (cost=0.00..4.00 rows=200 width=8) (actual time=1,847.087..1,847.195 rows=71 loops=1)

20. 0.710 0.710 ↑ 1.0 1 71

Index Scan using shipper_geolocation_trackers_pkey on shipper_geolocation_trackers (cost=0.43..8.46 rows=1 width=20) (actual time=0.009..0.010 rows=1 loops=71)

  • Index Cond: (id = shipper_geolocations.id)
21. 0.923 0.923 ↑ 1.0 1 71

Index Scan using order_routes_order_id_idx on order_routes (cost=0.43..8.35 rows=1 width=20) (actual time=0.012..0.013 rows=1 loops=71)

  • Index Cond: (order_id = shipper_geolocations.order_id)
22. 0.090 0.090 ↑ 3.0 1 6

Index Scan using shipper_transactions_order_id_idx on shipper_transactions (cost=0.42..0.60 rows=3 width=8) (actual time=0.014..0.015 rows=1 loops=6)

  • Index Cond: (order_id = order_routes.order_id)
Planning time : 3.871 ms
Execution time : 1,850.098 ms