explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TgOT

Settings
# exclusive inclusive rows x rows loops node
1. 9,831.683 9,831.683 ↑ 1.0 139 1

CTE Scan on report_loads (cost=58,708.79..58,712.42 rows=145 width=40) (actual time=9,831.066..9,831.683 rows=139 loops=1)

2.          

CTE report_loads

3. 0.402 9,831.592 ↑ 1.0 139 1

GroupAggregate (cost=58,704.08..58,708.79 rows=145 width=72) (actual time=9,831.057..9,831.592 rows=139 loops=1)

  • Group Key: (date_trunc('day'::text, l.pickup_date))
4. 0.452 9,831.190 ↓ 11.2 1,626 1

Sort (cost=58,704.08..58,704.44 rows=145 width=25) (actual time=9,831.043..9,831.190 rows=1,626 loops=1)

  • Sort Key: (date_trunc('day'::text, l.pickup_date))
  • Sort Method: quicksort Memory: 176kB
5. 0.734 9,830.738 ↓ 11.2 1,626 1

Subquery Scan on l (cost=58,683.28..58,698.87 rows=145 width=25) (actual time=9,828.548..9,830.738 rows=1,626 loops=1)

6. 1.146 9,830.004 ↓ 11.2 1,626 1

Unique (cost=58,683.28..58,697.06 rows=145 width=443) (actual time=9,828.532..9,830.004 rows=1,626 loops=1)

7. 4.151 9,828.858 ↓ 11.4 1,657 1

Sort (cost=58,683.28..58,683.65 rows=145 width=443) (actual time=9,828.531..9,828.858 rows=1,657 loops=1)

  • Sort Key: l_1.created_at DESC, l_1.id, l_1.internal_reference_id, l_1.external_reference_id, lf.posting_id, lf.posting_reference_id, (CASE WHEN (lf.shipper_business_id = '2121438735764555001'::bigint) THEN 'Nucor'::character varying ELSE lf.shipper_name END), lel_1.shipper_user_id, lf.shipper_email, lel_1.carrier_business_id, lf.carrier_name, lel_1.carrier_user_id, lf.carrier_email, (CASE WHEN (lf.driver_name IS NULL) THEN (SubPlan 1) ELSE lf.driver_name END), lf.distance, lf.pickup_address, lf.dropoff_address, lf.pickup_date, lf.dropoff_date, lf.stop_count, l_1.priority, ((SubPlan 2)), ((lel_1.carrier_business_id > 0)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), ((SubPlan 7)), ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((SubPlan 12)), ((SubPlan 13)), lf.bid_price, lf.fuel_surcharge, lf.accessorial, ((SubPlan 14))
  • Sort Method: quicksort Memory: 910kB
8. 21.001 9,824.707 ↓ 11.4 1,657 1

Nested Loop (cost=526.12..58,678.08 rows=145 width=443) (actual time=4.360..9,824.707 rows=1,657 loops=1)

  • Join Filter: (lf.load_id = l_1.id)
9. 3.700 19.040 ↓ 11.6 1,720 1

Hash Join (cost=525.84..4,480.96 rows=148 width=265) (actual time=3.616..19.040 rows=1,720 loops=1)

  • Hash Cond: ((lf.load_id = lel_1.load_id) AND (lf.carrier_business_id = lel_1.carrier_business_id))
10. 11.765 11.765 ↓ 1.9 1,762 1

Seq Scan on load_fact lf (cost=0.00..3,932.40 rows=944 width=221) (actual time=0.017..11.765 rows=1,762 loops=1)

  • Filter: ((pickup_date > '2019-10-10 00:00:00+00'::timestamp with time zone) AND (shipper_business_id = '1703858125129385245'::bigint) AND ((shipper_business_id = '1703858125129385245'::bigint) OR (carrier_business_id = '1703858125129385245'::bigint)))
  • Rows Removed by Filter: 12395
11. 0.598 3.575 ↓ 1.0 3,380 1

Hash (cost=476.43..476.43 rows=3,294 width=52) (actual time=3.575..3.575 rows=3,380 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 323kB
12. 2.977 2.977 ↓ 1.0 3,380 1

Seq Scan on load_execution_link lel_1 (cost=0.00..476.43 rows=3,294 width=52) (actual time=0.009..2.977 rows=3,380 loops=1)

  • Filter: ((shipper_business_id = '1703858125129385245'::bigint) AND (status = 11200))
  • Rows Removed by Filter: 10712
13. 10.320 15.480 ↑ 1.0 1 1,720

Index Scan using load_pkey on load l_1 (cost=0.28..8.96 rows=1 width=45) (actual time=0.008..0.009 rows=1 loops=1,720)

  • Index Cond: (id = lel_1.load_id)
  • Filter: ((created_at >= '1970-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= COALESCE(now(), now())) AND ((SubPlan 15) <> ALL ('{CANCELED,DRAFT,RATE_CONFIRMATION_REQUESTED}'::text[])))
  • Rows Removed by Filter: 0
14.          

SubPlan (for Index Scan)

15. 5.160 5.160 ↑ 1.0 1 1,720

Index Scan using idx_code_code on code code_1 (cost=0.28..8.29 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1,720)

  • Index Cond: (l_1.status = code)
16.          

SubPlan (for Nested Loop)

17. 1.576 9.456 ↓ 0.0 0 1,576

Limit (cost=16.62..16.62 rows=1 width=40) (actual time=0.006..0.006 rows=0 loops=1,576)

18. 1.576 7.880 ↓ 0.0 0 1,576

Sort (cost=16.62..16.62 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=1,576)

  • Sort Key: ull.id DESC
  • Sort Method: quicksort Memory: 25kB
19. 1.208 6.304 ↓ 0.0 0 1,576

Nested Loop (cost=0.56..16.61 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=1,576)

20. 3.152 3.152 ↓ 0.0 0 1,576

Index Scan using idx_user_load_link_load_id_deassigned_at_deassigned_by_user_id on user_load_link ull (cost=0.27..8.29 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,576)

  • Index Cond: (load_id = l_1.id)
21. 1.944 1.944 ↑ 1.0 1 243

Index Scan using idx_business_user_id_status on business_user bu (cost=0.29..8.30 rows=1 width=21) (actual time=0.008..0.008 rows=1 loops=243)

  • Index Cond: (id = ull.user_id)
22. 1.657 1.657 ↑ 1.0 1 1,657

Index Scan using idx_code_code on code (cost=0.28..8.29 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1,657)

  • Index Cond: (l_1.status = code)
23. 1.657 3.314 ↓ 0.0 0 1,657

Limit (cost=0.27..4.29 rows=1 width=1) (actual time=0.002..0.002 rows=0 loops=1,657)

24. 1.657 1.657 ↓ 0.0 0 1,657

Index Only Scan using idx_user_load_link_load_id_deassigned_at_deassigned_by_user_id on user_load_link ull_1 (cost=0.27..4.29 rows=1 width=1) (actual time=0.001..0.001 rows=0 loops=1,657)

  • Index Cond: ((load_id = lf.load_id) AND (deassigned_at IS NULL) AND (deassigned_by_user_id IS NULL))
  • Heap Fetches: 0
25. 4.971 36.454 ↑ 1.0 1 1,657

Aggregate (cost=30.99..31.00 rows=1 width=1) (actual time=0.022..0.022 rows=1 loops=1,657)

26. 31.483 31.483 ↓ 1.1 31 1,657

Index Scan using idx_event_load_id on event e (cost=0.29..30.91 rows=29 width=0) (actual time=0.006..0.019 rows=31 loops=1,657)

  • Index Cond: (lf.load_id = load_id)
  • Filter: ((event_code = 4302) AND ((event_type = 4402) OR (event_type = 4403)))
  • Rows Removed by Filter: 4
27. 1.657 9,325.596 ↓ 0.0 0 1,657

Limit (cost=0.00..15.35 rows=1 width=1) (actual time=5.628..5.628 rows=0 loops=1,657)

28. 4,088.702 9,323.939 ↓ 0.0 0 1,657

Nested Loop (cost=0.00..6,786.09 rows=442 width=1) (actual time=5.627..5.627 rows=0 loops=1,657)

  • Join Filter: (usl.user_id = ull_2.user_id)
  • Rows Removed by Join Filter: 25845
29. 84.507 84.507 ↓ 0.0 0 1,657

Seq Scan on user_load_link ull_2 (cost=0.00..13.91 rows=1 width=8) (actual time=0.047..0.051 rows=0 loops=1,657)

  • Filter: ((deassigned_at IS NULL) AND (deassigned_by_user_id IS NULL) AND (load_id = lf.load_id))
  • Rows Removed by Filter: 383
30. 5,150.730 5,150.730 ↑ 1.0 152,403 281

Seq Scan on user_security_logs usl (cost=0.00..4,840.41 rows=154,541 width=8) (actual time=0.004..18.330 rows=152,403 loops=281)

31. 1.657 26.512 ↓ 0.0 0 1,657

Limit (cost=0.56..39.03 rows=1 width=1) (actual time=0.016..0.016 rows=0 loops=1,657)

32. 1.684 24.855 ↓ 0.0 0 1,657

Nested Loop (cost=0.56..39.03 rows=1 width=1) (actual time=0.015..0.015 rows=0 loops=1,657)

  • Join Filter: (e_1.created_by_user_id = ull_3.user_id)
  • Rows Removed by Join Filter: 0
33. 21.541 21.541 ↓ 0.0 0 1,657

Index Scan using idx_event_load_id on event e_1 (cost=0.29..30.72 rows=1 width=16) (actual time=0.012..0.013 rows=0 loops=1,657)

  • Index Cond: (load_id = l_1.id)
  • Filter: (event_code = 250)
  • Rows Removed by Filter: 21
34. 1.630 1.630 ↑ 1.0 1 326

Index Scan using idx_user_load_link_load_id_deassigned_at_deassigned_by_user_id on user_load_link ull_3 (cost=0.27..8.29 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=326)

  • Index Cond: ((load_id = l_1.id) AND (deassigned_at IS NULL) AND (deassigned_by_user_id IS NULL))
35. 1.657 13.256 ↑ 1.0 1 1,657

Aggregate (cost=8.44..8.46 rows=1 width=1) (actual time=0.008..0.008 rows=1 loops=1,657)

36. 11.599 11.599 ↑ 1.0 1 1,657

Index Scan using idx_stop_code_status on stop s (cost=0.42..8.44 rows=1 width=0) (actual time=0.006..0.007 rows=1 loops=1,657)

  • Index Cond: ((parent_type = 1700) AND (lf.load_id = parent_id) AND (status = 2101))
  • Filter: (appointment_needed IS TRUE)
  • Rows Removed by Filter: 1
37. 4.971 24.855 ↑ 1.0 1 1,657

Result (cost=17.02..17.03 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1,657)

38.          

Initplan (for Result)

39. 2.087 19.884 ↓ 3.0 3 1,657

Nested Loop Left Join (cost=0.83..17.02 rows=1 width=12) (actual time=0.009..0.012 rows=3 loops=1,657)

40. 8.285 8.285 ↑ 1.0 1 1,657

Index Scan using idx_stop_parent_index on stop s_1 (cost=0.42..8.44 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=1,657)

  • Index Cond: ((parent_type = 1700) AND (lf.load_id = parent_id))
  • Filter: ((appointment_needed IS TRUE) AND (status = 2101))
  • Rows Removed by Filter: 1
41. 9.512 9.512 ↑ 1.0 1 2,378

Index Scan using idx_appointment_parent_type_parent_id_status on appointment a (cost=0.41..8.56 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=2,378)

  • Index Cond: ((s_1.id = parent_id) AND (status = 9002))
42. 1.657 43.082 ↑ 1.0 1 1,657

Aggregate (cost=22.46..22.48 rows=1 width=1) (actual time=0.026..0.026 rows=1 loops=1,657)

43. 0.676 41.425 ↑ 1.0 1 1,657

Nested Loop (cost=9.63..22.46 rows=1 width=0) (actual time=0.021..0.025 rows=1 loops=1,657)

  • Join Filter: (dl.document_id = a_1.document_id)
44. 2.164 34.797 ↑ 1.0 1 1,657

Nested Loop (cost=9.35..21.91 rows=1 width=16) (actual time=0.017..0.021 rows=1 loops=1,657)

45. 6.308 28.169 ↑ 1.0 1 1,657

Nested Loop (cost=9.07..21.13 rows=1 width=8) (actual time=0.014..0.017 rows=1 loops=1,657)

46. 4.971 4.971 ↓ 2.0 2 1,657

Index Scan using idx_stop_code_status on stop s_2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.003 rows=2 loops=1,657)

  • Index Cond: ((parent_type = 1700) AND (lf.load_id = parent_id) AND (status = 2101))
47. 6.756 16.890 ↓ 0.0 0 3,378

Bitmap Heap Scan on document_link dl (cost=8.65..12.68 rows=1 width=20) (actual time=0.004..0.005 rows=0 loops=3,378)

  • Recheck Cond: (((parent_type = 1728) AND (lel_1.id = parent_id)) OR ((parent_type = 1705) AND (s_2.id = parent_id)))
  • Heap Blocks: exact=1400
48. 0.000 10.134 ↓ 0.0 0 3,378

BitmapOr (cost=8.65..8.65 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=3,378)

49. 6.756 6.756 ↓ 0.0 0 3,378

Bitmap Index Scan on idx_document_link_parent_type_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=3,378)

  • Index Cond: ((parent_type = 1728) AND (lel_1.id = parent_id))
50. 3.378 3.378 ↓ 0.0 0 3,378

Bitmap Index Scan on idx_document_link_parent_type_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=3,378)

  • Index Cond: ((parent_type = 1705) AND (s_2.id = parent_id))
51. 4.464 4.464 ↑ 1.0 1 1,488

Index Only Scan using document_pkey on document d (cost=0.28..0.77 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,488)

  • Index Cond: (id = dl.document_id)
  • Heap Fetches: 1488
52. 5.952 5.952 ↑ 1.0 1 1,488

Index Scan using idx_attachment_document_id on attachment a_1 (cost=0.28..0.54 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,488)

  • Index Cond: (document_id = d.id)
  • Filter: (type = 2601)
  • Rows Removed by Filter: 0
53. 1.657 28.169 ↑ 1.0 1 1,657

Aggregate (cost=22.46..22.48 rows=1 width=1) (actual time=0.017..0.017 rows=1 loops=1,657)

54. 0.338 26.512 ↓ 0.0 0 1,657

Nested Loop (cost=9.63..22.46 rows=1 width=0) (actual time=0.015..0.016 rows=0 loops=1,657)

  • Join Filter: (dl_1.document_id = a_2.document_id)
55. 3.483 23.198 ↑ 1.0 1 1,657

Nested Loop (cost=9.35..21.91 rows=1 width=16) (actual time=0.011..0.014 rows=1 loops=1,657)

56. 4.779 18.227 ↑ 1.0 1 1,657

Nested Loop (cost=9.07..21.13 rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=1,657)

57. 3.314 3.314 ↓ 2.0 2 1,657

Index Scan using idx_stop_code_status on stop s_3 (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=2 loops=1,657)

  • Index Cond: ((parent_type = 1700) AND (lf.load_id = parent_id) AND (status = 2101))
58. 3.378 10.134 ↓ 0.0 0 3,378

Bitmap Heap Scan on document_link dl_1 (cost=8.65..12.68 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=3,378)

  • Recheck Cond: (((parent_type = 1728) AND (lel_1.id = parent_id)) OR ((parent_type = 1705) AND (s_3.id = parent_id)))
  • Heap Blocks: exact=1400
59. 0.000 6.756 ↓ 0.0 0 3,378

BitmapOr (cost=8.65..8.65 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=3,378)

60. 3.378 3.378 ↓ 0.0 0 3,378

Bitmap Index Scan on idx_document_link_parent_type_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=3,378)

  • Index Cond: ((parent_type = 1728) AND (lel_1.id = parent_id))
61. 3.378 3.378 ↓ 0.0 0 3,378

Bitmap Index Scan on idx_document_link_parent_type_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=3,378)

  • Index Cond: ((parent_type = 1705) AND (s_3.id = parent_id))
62. 1.488 1.488 ↑ 1.0 1 1,488

Index Only Scan using document_pkey on document d_1 (cost=0.28..0.77 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,488)

  • Index Cond: (id = dl_1.document_id)
  • Heap Fetches: 1488
63. 2.976 2.976 ↓ 0.0 0 1,488

Index Scan using idx_attachment_document_id on attachment a_2 (cost=0.28..0.54 rows=1 width=8) (actual time=0.001..0.002 rows=0 loops=1,488)

  • Index Cond: (document_id = d_1.id)
  • Filter: (type = 2604)
  • Rows Removed by Filter: 1
64. 1.657 28.169 ↑ 1.0 1 1,657

Aggregate (cost=22.46..22.48 rows=1 width=1) (actual time=0.017..0.017 rows=1 loops=1,657)

65. 1.995 26.512 ↓ 0.0 0 1,657

Nested Loop (cost=9.63..22.46 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1,657)

  • Join Filter: (dl_2.document_id = a_3.document_id)
66. 1.826 21.541 ↑ 1.0 1 1,657

Nested Loop (cost=9.35..21.91 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=1,657)

67. 4.779 18.227 ↑ 1.0 1 1,657

Nested Loop (cost=9.07..21.13 rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=1,657)

68. 3.314 3.314 ↓ 2.0 2 1,657

Index Scan using idx_stop_code_status on stop s_4 (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=2 loops=1,657)

  • Index Cond: ((parent_type = 1700) AND (lf.load_id = parent_id) AND (status = 2101))
69. 3.378 10.134 ↓ 0.0 0 3,378

Bitmap Heap Scan on document_link dl_2 (cost=8.65..12.68 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=3,378)

  • Recheck Cond: (((parent_type = 1728) AND (lel_1.id = parent_id)) OR ((parent_type = 1705) AND (s_4.id = parent_id)))
  • Heap Blocks: exact=1400
70. 0.000 6.756 ↓ 0.0 0 3,378

BitmapOr (cost=8.65..8.65 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=3,378)

71. 3.378 3.378 ↓ 0.0 0 3,378

Bitmap Index Scan on idx_document_link_parent_type_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=3,378)

  • Index Cond: ((parent_type = 1728) AND (lel_1.id = parent_id))
72. 3.378 3.378 ↓ 0.0 0 3,378

Bitmap Index Scan on idx_document_link_parent_type_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=3,378)

  • Index Cond: ((parent_type = 1705) AND (s_4.id = parent_id))
73. 1.488 1.488 ↑ 1.0 1 1,488

Index Only Scan using document_pkey on document d_2 (cost=0.28..0.77 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,488)

  • Index Cond: (id = dl_2.document_id)
  • Heap Fetches: 1488
74. 2.976 2.976 ↓ 0.0 0 1,488

Index Scan using idx_attachment_document_id on attachment a_3 (cost=0.28..0.54 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,488)

  • Index Cond: (document_id = d_2.id)
  • Filter: ((mime_type)::text = ANY ('{image/jpeg,image/png}'::text[]))
  • Rows Removed by Filter: 1
75. 4.971 9.942 ↑ 1.0 1 1,657

Aggregate (cost=8.31..8.32 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,657)

76. 4.971 4.971 ↓ 0.0 0 1,657

Index Scan using idx_load_execution_link_load_status on load_execution_link lel (cost=0.29..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,657)

  • Index Cond: ((load_id = lf.load_id) AND (status = 11202))
77. 1.657 218.724 ↓ 0.0 0 1,657

Limit (cost=148.78..148.78 rows=1 width=40) (actual time=0.132..0.132 rows=0 loops=1,657)

78. 1.657 217.067 ↓ 0.0 0 1,657

Sort (cost=148.78..148.78 rows=1 width=40) (actual time=0.131..0.131 rows=0 loops=1,657)

  • Sort Key: ur.id DESC
  • Sort Method: quicksort Memory: 25kB
79. 1.062 215.410 ↓ 0.0 0 1,657

Nested Loop (cost=0.57..148.77 rows=1 width=40) (actual time=0.121..0.130 rows=0 loops=1,657)

80. 213.753 213.753 ↓ 0.0 0 1,657

Index Scan using idx_user_representative_user_id_parent_id on user_representative ur (cost=0.28..140.45 rows=1 width=16) (actual time=0.120..0.129 rows=0 loops=1,657)

  • Index Cond: (lel_1.id = parent_id)
  • Filter: (parent_type = 1728)
81. 0.595 0.595 ↑ 1.0 1 119

Index Scan using idx_business_user_id_status on business_user bu_1 (cost=0.29..8.30 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=119)

  • Index Cond: (id = ur.user_id)
Planning time : 5.635 ms
Execution time : 9,832.379 ms