explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rX9O

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.138 ↑ 1.0 1 1

Limit (cost=138.18..157.85 rows=1 width=268) (actual time=0.137..0.138 rows=1 loops=1)

2. 0.014 0.136 ↑ 2.0 1 1

Result (cost=138.18..177.52 rows=2 width=268) (actual time=0.136..0.136 rows=1 loops=1)

3. 0.008 0.094 ↑ 2.0 1 1

Sort (cost=138.18..138.19 rows=2 width=215) (actual time=0.094..0.094 rows=1 loops=1)

  • Sort Key: os.inserted_at DESC
  • Sort Method: quicksort Memory: 25kB
4. 0.009 0.086 ↑ 2.0 1 1

Nested Loop Left Join (cost=38.47..138.17 rows=2 width=215) (actual time=0.082..0.086 rows=1 loops=1)

  • Join Filter: (routes.courier_id = os.assigned_courier_id)
5. 0.002 0.075 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.18..115.75 rows=2 width=268) (actual time=0.072..0.075 rows=1 loops=1)

6. 0.001 0.071 ↑ 2.0 1 1

Nested Loop Left Join (cost=33.90..99.15 rows=2 width=249) (actual time=0.069..0.071 rows=1 loops=1)

7. 0.002 0.055 ↑ 2.0 1 1

Nested Loop (cost=9.40..50.09 rows=2 width=233) (actual time=0.052..0.055 rows=1 loops=1)

8. 0.005 0.047 ↑ 2.0 1 1

Nested Loop (cost=9.12..33.47 rows=2 width=166) (actual time=0.045..0.047 rows=1 loops=1)

9. 0.004 0.033 ↑ 2.0 1 1

Bitmap Heap Scan on orders_stores os (cost=8.84..16.85 rows=2 width=166) (actual time=0.033..0.033 rows=1 loops=1)

  • Recheck Cond: ((tracking_url = '6236739733'::text) OR (receipt_id = '6236739733'::text))
  • Heap Blocks: exact=1
10. 0.001 0.029 ↓ 0.0 0 1

BitmapOr (cost=8.84..8.84 rows=2 width=0) (actual time=0.029..0.029 rows=0 loops=1)

11. 0.015 0.015 ↓ 0.0 0 1

Bitmap Index Scan on idx_orders_stores_tracking_url (cost=0.00..4.42 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (tracking_url = '6236739733'::text)
12. 0.013 0.013 ↑ 1.0 1 1

Bitmap Index Scan on deliveries_receipt_id_index (cost=0.00..4.42 rows=1 width=0) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (receipt_id = '6236739733'::text)
13. 0.009 0.009 ↑ 1.0 1 1

Index Only Scan using products_delivery_id_idx on products pr (cost=0.29..8.30 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (delivery_id = os.id)
  • Heap Fetches: 1
14. 0.006 0.006 ↑ 1.0 1 1

Index Scan using admin_vendor_store_idx on vendor_stores vs (cost=0.28..8.29 rows=1 width=99) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = os.sender_store_id)
15. 0.001 0.015 ↓ 0.0 0 1

Limit (cost=24.50..24.51 rows=1 width=20) (actual time=0.015..0.015 rows=0 loops=1)

16. 0.004 0.014 ↓ 0.0 0 1

Sort (cost=24.50..24.51 rows=1 width=20) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: mis.mission_index DESC
  • Sort Method: quicksort Memory: 25kB
17. 0.010 0.010 ↓ 0.0 0 1

Index Scan using missions_delivery_index on missions mis (cost=0.41..24.49 rows=1 width=20) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (delivery_id = os.id)
  • Filter: (courier_id = os.assigned_courier_id)
  • Rows Removed by Filter: 2
18. 0.002 0.002 ↓ 0.0 0 1

Index Scan using admin_couriers_id on couriers c (cost=0.28..8.29 rows=1 width=35) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (id = os.assigned_courier_id)
19. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on fixed_routes routes (cost=4.29..11.15 rows=2 width=37) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (order_id = mis.id)
20. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on fixed_routes_order_id_idx (cost=0.00..4.29 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (order_id = mis.id)
21.          

SubPlan (for Result)

22. 0.007 0.014 ↑ 1.0 1 1

Seq Scan on regions (cost=10.12..11.18 rows=1 width=32) (actual time=0.012..0.014 rows=1 loops=1)

  • Filter: (id = $5)
  • Rows Removed by Filter: 5
23.          

Initplan (for Seq Scan)

24. 0.001 0.007 ↑ 1.0 1 1

Limit (cost=8.42..10.12 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)

25. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on areas_to_regions_mapping map (cost=8.42..10.12 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)

  • Filter: (((COALESCE(os.order_metadata, ''::text) <> 'return'::text) AND (area_id = os.area_id)) OR ((os.order_metadata = 'return'::text) AND (hashed SubPlan 1)))
  • Rows Removed by Filter: 21
26.          

SubPlan (for Seq Scan)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using area_geom_idx on areas (cost=0.14..8.41 rows=1 width=4) (never executed)

  • Index Cond: (geom ~ st_setsrid(st_makepoint(vs.longitude, vs.latitude), 4326))
  • Filter: _st_contains(geom, st_setsrid(st_makepoint(vs.longitude, vs.latitude), 4326))
28. 0.010 0.014 ↑ 1.0 1 1

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

29. 0.004 0.004 ↑ 1.0 1 1

Index Scan using products_delivery_id_idx on products pr2 (cost=0.29..8.30 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (delivery_id = os.id)
Planning time : 1.335 ms
Execution time : 0.377 ms