explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pnTQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 5,951.495 ↑ 1.0 20 1

Limit (cost=3,310,340.99..3,310,341.04 rows=20 width=1,751) (actual time=5,951.482..5,951.495 rows=20 loops=1)

2. 63.995 5,951.486 ↑ 102.8 20 1

Sort (cost=3,310,340.99..3,310,346.13 rows=2,057 width=1,751) (actual time=5,951.481..5,951.486 rows=20 loops=1)

  • Sort Key: o.status_updated_at DESC, o.id
  • Sort Method: top-N heapsort Memory: 185kB
3. 138.246 5,887.491 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=1,177.04..3,310,286.26 rows=2,057 width=1,751) (actual time=24.606..5,887.491 rows=16,905 loops=1)

4. 27.807 677.745 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=1,176.32..80,661.00 rows=2,057 width=1,507) (actual time=24.218..677.745 rows=16,905 loops=1)

5. 19.564 649.938 ↓ 8.2 16,905 1

Hash Left Join (cost=1,176.03..79,235.69 rows=2,057 width=1,496) (actual time=24.214..649.938 rows=16,905 loops=1)

  • Hash Cond: (da.country_id = dac.id)
6. 21.945 619.188 ↓ 8.2 16,905 1

Hash Left Join (cost=589.68..78,621.05 rows=2,057 width=1,485) (actual time=12.903..619.188 rows=16,905 loops=1)

  • Hash Cond: (pa.country_id = pac.id)
7. 28.591 584.688 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=3.33..78,006.42 rows=2,057 width=1,474) (actual time=0.233..584.688 rows=16,905 loops=1)

8. 25.436 556.097 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=2.89..60,911.05 rows=2,057 width=1,241) (actual time=0.230..556.097 rows=16,905 loops=1)

9. 35.491 446.136 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=2.46..43,815.69 rows=2,057 width=1,008) (actual time=0.211..446.136 rows=16,905 loops=1)

10. 32.893 359.930 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=2.02..26,720.33 rows=2,057 width=775) (actual time=0.188..359.930 rows=16,905 loops=1)

11. 21.558 293.227 ↓ 8.2 16,905 1

Nested Loop Left Join (cost=1.73..23,030.09 rows=2,057 width=757) (actual time=0.172..293.227 rows=16,905 loops=1)

12. 30.141 187.144 ↓ 8.2 16,905 1

Nested Loop (cost=1.29..5,721.58 rows=2,057 width=675) (actual time=0.148..187.144 rows=16,905 loops=1)

13. 18.161 123.193 ↓ 8.2 16,905 1

Nested Loop (cost=1.15..5,350.75 rows=2,057 width=675) (actual time=0.127..123.193 rows=16,905 loops=1)

14. 0.003 0.050 ↑ 1.0 1 1

Nested Loop (cost=0.58..16.63 rows=1 width=58) (actual time=0.047..0.050 rows=1 loops=1)

15. 0.029 0.029 ↑ 1.0 1 1

Index Scan using organizations_pkey on organizations org (cost=0.29..8.31 rows=1 width=22) (actual time=0.028..0.029 rows=1 loops=1)

  • Index Cond: (party_id = 58197)
16. 0.018 0.018 ↑ 1.0 1 1

Index Scan using parties_pkey on parties org_party (cost=0.29..8.31 rows=1 width=40) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (id = 58197)
17. 104.982 104.982 ↓ 8.2 16,905 1

Index Scan using orders_party_id_idx on orders o (cost=0.56..5,313.55 rows=2,057 width=617) (actual time=0.079..104.982 rows=16,905 loops=1)

  • Index Cond: (party_id = 58197)
  • Filter: (status = ANY ('{for_pickup,picked_up,failed_pickup,failed_delivery,in_transit,claimed,delivered,return_in_transit,returned,failed_return,out_fo
  • Rows Removed by Filter: 1248
18. 33.810 33.810 ↑ 1.0 1 16,905

Index Scan using currencies_pkey on currencies c (cost=0.14..0.17 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=16,905)

  • Index Cond: (id = o.currency_id)
19. 84.525 84.525 ↑ 1.0 1 16,905

Index Scan using charges_pkey on charges charge (cost=0.44..8.40 rows=1 width=82) (actual time=0.004..0.005 rows=1 loops=16,905)

  • Index Cond: (order_id = o.id)
20. 33.810 33.810 ↑ 1.0 1 16,905

Index Scan using organizations_pkey on organizations service (cost=0.29..1.78 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=16,905)

  • Index Cond: (party_id = o.service_id)
21. 50.715 50.715 ↑ 1.0 1 16,905

Index Scan using addresses_pkey on addresses pa (cost=0.43..8.30 rows=1 width=237) (actual time=0.003..0.003 rows=1 loops=16,905)

  • Index Cond: (id = o.pickup_address_id)
22. 84.525 84.525 ↑ 1.0 1 16,905

Index Scan using addresses_pkey on addresses da (cost=0.43..8.30 rows=1 width=237) (actual time=0.004..0.005 rows=1 loops=16,905)

  • Index Cond: (id = o.delivery_address_id)
23. 0.000 0.000 ↓ 0.0 0 16,905

Index Scan using addresses_pkey on addresses ra (cost=0.43..8.30 rows=1 width=237) (actual time=0.000..0.000 rows=0 loops=16,905)

  • Index Cond: (id = o.return_address_id)
24. 6.304 12.555 ↑ 1.0 18,149 1

Hash (cost=359.49..359.49 rows=18,149 width=19) (actual time=12.555..12.555 rows=18,149 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1136kB
25. 6.251 6.251 ↑ 1.0 18,149 1

Seq Scan on locations pac (cost=0.00..359.49 rows=18,149 width=19) (actual time=0.003..6.251 rows=18,149 loops=1)

26. 6.171 11.186 ↑ 1.0 18,149 1

Hash (cost=359.49..359.49 rows=18,149 width=19) (actual time=11.186..11.186 rows=18,149 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1136kB
27. 5.015 5.015 ↑ 1.0 18,149 1

Seq Scan on locations dac (cost=0.00..359.49 rows=18,149 width=19) (actual time=0.002..5.015 rows=18,149 loops=1)

28. 0.000 0.000 ↓ 0.0 0 16,905

Index Scan using locations_pkey on locations rac (cost=0.29..0.68 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=16,905)

  • Index Cond: (id = ra.country_id)
29. 16.905 16.905 ↓ 0.0 0 16,905

Nested Loop Left Join (cost=0.72..8.99 rows=1 width=248) (actual time=0.001..0.001 rows=0 loops=16,905)

30. 0.000 0.000 ↓ 0.0 0 16,905

Index Scan using addresses_pkey on addresses "do" (cost=0.43..8.30 rows=1 width=237) (actual time=0.000..0.000 rows=0 loops=16,905)

  • Index Cond: (id = o.dropoff_address_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using locations_pkey on locations doc (cost=0.29..0.68 rows=1 width=19) (never executed)

  • Index Cond: (id = "do".country_id)
32.          

SubPlan (forNested Loop Left Join)

33. 152.145 219.765 ↑ 1.0 1 16,905

Aggregate (cost=35.26..35.27 rows=1 width=88) (actual time=0.013..0.013 rows=1 loops=16,905)

34. 67.620 67.620 ↑ 153.0 1 16,905

Index Scan using order_items_order_id_idx on order_items i (cost=0.57..34.49 rows=153 width=88) (actual time=0.004..0.004 rows=1 loops=16,905)

  • Index Cond: (order_id = o.id)
35. 287.385 287.385 ↑ 1.0 1 16,905

Index Scan using order_segments_pkey on order_segments active_segment (cost=0.56..8.58 rows=1 width=66) (actual time=0.017..0.017 rows=1 loops=16,905)

  • Index Cond: (id = o.active_segment_id)
36. 16.905 67.620 ↑ 1.0 1 16,905

Aggregate (cost=339.11..339.12 rows=1 width=99) (actual time=0.004..0.004 rows=1 loops=16,905)

37. 50.715 50.715 ↓ 0.0 0 16,905

Index Scan using orders_parent_id_idx on orders child (cost=0.56..8.58 rows=1 width=99) (actual time=0.003..0.003 rows=0 loops=16,905)

  • Index Cond: (parent_id = o.id)
38.          

SubPlan (forAggregate)

39. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=264.10..264.11 rows=1 width=51) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.14..263.59 rows=100 width=51) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Index Scan using order_segments_order_id_idx on order_segments child_segments (cost=0.56..31.01 rows=26 width=4) (never executed)

  • Index Cond: (order_id = child.id)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using order_events_order_segment_id_idx on order_events child_events (cost=0.57..8.87 rows=8 width=55) (never executed)

  • Index Cond: (order_segment_id = child_segments.id)
43. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=35.26..35.27 rows=1 width=96) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using order_items_order_id_idx on order_items child_items (cost=0.57..34.49 rows=153 width=96) (never executed)

  • Index Cond: (order_id = child.id)
45. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=31.14..31.15 rows=1 width=22) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using order_segments_order_id_idx on order_segments child_segments_1 (cost=0.56..31.01 rows=26 width=22) (never executed)

  • Index Cond: (order_id = child.id)
47. 1,521.450 2,434.320 ↑ 1.0 1 16,905

Aggregate (cost=912.61..912.62 rows=1 width=1,459) (actual time=0.144..0.144 rows=1 loops=16,905)

48. 67.620 912.870 ↑ 13.0 2 16,905

Nested Loop Left Join (cost=2.60..912.09 rows=26 width=1,459) (actual time=0.023..0.054 rows=2 loops=16,905)

49. 50.715 777.630 ↑ 13.0 2 16,905

Nested Loop Left Join (cost=2.31..903.89 rows=26 width=1,448) (actual time=0.021..0.046 rows=2 loops=16,905)

50. 84.525 659.295 ↑ 13.0 2 16,905

Nested Loop Left Join (cost=2.02..895.70 rows=26 width=1,437) (actual time=0.018..0.039 rows=2 loops=16,905)

51. 67.620 507.150 ↑ 13.0 2 16,905

Nested Loop Left Join (cost=1.58..675.67 rows=26 width=1,248) (actual time=0.014..0.030 rows=2 loops=16,905)

52. 50.715 338.100 ↑ 13.0 2 16,905

Nested Loop Left Join (cost=1.15..455.65 rows=26 width=1,059) (actual time=0.010..0.020 rows=2 loops=16,905)

53. 84.525 219.765 ↑ 13.0 2 16,905

Nested Loop (cost=0.86..243.33 rows=26 width=117) (actual time=0.007..0.013 rows=2 loops=16,905)

54. 67.620 67.620 ↑ 13.0 2 16,905

Index Scan using order_segments_order_id_idx on order_segments os (cost=0.56..31.01 rows=26 width=99) (actual time=0.003..0.004 rows=2 loops=16,905)

  • Index Cond: (order_id = o.id)
55. 67.620 67.620 ↑ 1.0 1 33,810

Index Scan using organizations_pkey on organizations os_org (cost=0.29..8.16 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=33,810)

  • Index Cond: (party_id = os.courier_party_id)
56. 67.620 67.620 ↑ 1.0 1 33,810

Index Scan using parties_pkey on parties p (cost=0.29..8.16 rows=1 width=950) (actual time=0.002..0.002 rows=1 loops=33,810)

  • Index Cond: (id = os.courier_party_id)
57. 101.430 101.430 ↑ 1.0 1 33,810

Index Scan using addresses_pkey on addresses os_pa (cost=0.43..8.45 rows=1 width=197) (actual time=0.003..0.003 rows=1 loops=33,810)

  • Index Cond: (id = os.pickup_address_id)
58. 67.620 67.620 ↑ 1.0 1 33,810

Index Scan using addresses_pkey on addresses os_da (cost=0.43..8.45 rows=1 width=197) (actual time=0.002..0.002 rows=1 loops=33,810)

  • Index Cond: (id = os.delivery_address_id)
59. 67.620 67.620 ↑ 1.0 1 33,810

Index Scan using locations_pkey on locations os_pac (cost=0.29..0.31 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=33,810)

  • Index Cond: (id = os_pa.country_id)
60. 67.620 67.620 ↑ 1.0 1 33,810

Index Scan using locations_pkey on locations os_dac (cost=0.29..0.31 rows=1 width=19) (actual time=0.001..0.002 rows=1 loops=33,810)

  • Index Cond: (id = os_da.country_id)
61. 1,115.730 2,045.505 ↑ 1.0 1 16,905

Aggregate (cost=265.46..265.47 rows=1 width=51) (actual time=0.120..0.121 rows=1 loops=16,905)

62.          

CTE ordersegments

63. 67.620 67.620 ↑ 13.0 2 16,905

Index Scan using order_segments_order_id_idx on order_segments os_1 (cost=0.56..31.01 rows=26 width=4) (actual time=0.003..0.004 rows=2 loops=16,905)

  • Index Cond: (order_id = o.id)
64. 169.050 929.775 ↑ 22.6 9 16,905

Nested Loop (cost=1.16..233.43 rows=203 width=51) (actual time=0.017..0.055 rows=9 loops=16,905)

65. 50.715 152.145 ↑ 13.0 2 16,905

HashAggregate (cost=0.58..0.84 rows=26 width=4) (actual time=0.008..0.009 rows=2 loops=16,905)

  • Group Key: ordersegments.id
66. 101.430 101.430 ↑ 13.0 2 16,905

CTE Scan on ordersegments (cost=0.00..0.52 rows=26 width=4) (actual time=0.004..0.006 rows=2 loops=16,905)

67. 608.580 608.580 ↑ 1.6 5 33,810

Index Scan using order_events_order_segment_id_idx on order_events oe (cost=0.57..8.87 rows=8 width=55) (actual time=0.006..0.018 rows=5 loops=33,810)

  • Index Cond: (order_segment_id = ordersegments.id)