explain.depesz.com

PostgreSQL's explain analyze made readable

Result: puBK

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 2,194.343 ↑ 1.0 10 1

Limit (cost=87,215.22..874,624.25 rows=10 width=775) (actual time=2,112.092..2,194.343 rows=10 loops=1)

2. 0.402 2,194.287 ↑ 35,431.6 10 1

Result (cost=87,215.22..27,899,249,022.06 rows=354,316 width=775) (actual time=2,112.090..2,194.287 rows=10 loops=1)

3. 157.602 2,101.505 ↑ 35,431.6 10 1

Sort (cost=87,215.22..88,101.01 rows=354,316 width=131) (actual time=2,101.495..2,101.505 rows=10 loops=1)

  • Sort Key: org.id, pipe.id
  • Sort Method: top-N heapsort Memory: 26kB
4. 1,051.953 1,943.903 ↓ 1.0 363,267 1

Hash Join (cost=32,874.69..79,558.58 rows=354,316 width=131) (actual time=419.201..1,943.903 rows=363,267 loops=1)

  • Hash Cond: (pipe.organization_id = org.id)
5. 243.875 784.831 ↑ 1.0 363,267 1

Hash Left Join (cost=24,659.37..55,980.98 rows=365,212 width=63) (actual time=311.675..784.831 rows=363,267 loops=1)

  • Hash Cond: (pipe.clone_from_id = parent.id)
6. 229.602 229.602 ↑ 1.0 363,267 1

Seq Scan on repos pipe (cost=0.00..18,438.51 rows=365,212 width=46) (actual time=0.022..229.602 rows=363,267 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Pipe'::text))
  • Rows Removed by Filter: 129494
7. 155.664 311.354 ↑ 1.0 382,243 1

Hash (cost=17,206.61..17,206.61 rows=385,421 width=25) (actual time=311.354..311.354 rows=382,243 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3273kB
8. 155.690 155.690 ↑ 1.0 382,243 1

Seq Scan on repos parent (cost=0.00..17,206.61 rows=385,421 width=25) (actual time=0.004..155.690 rows=382,243 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 110518
9. 57.098 107.119 ↓ 1.0 141,637 1

Hash (cost=5,615.99..5,615.99 rows=141,546 width=24) (actual time=107.118..107.119 rows=141,637 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2578kB
10. 50.021 50.021 ↓ 1.0 141,637 1

Seq Scan on organizations org (cost=0.00..5,615.99 rows=141,546 width=24) (actual time=0.007..50.021 rows=141,637 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 4262
11.          

SubPlan (forResult)

12. 0.150 1.150 ↑ 1.0 1 10

Aggregate (cost=33.44..33.45 rows=1 width=8) (actual time=0.114..0.115 rows=1 loops=10)

13. 0.126 1.000 ↓ 13.0 13 10

Nested Loop (cost=1.27..33.43 rows=1 width=4) (actual time=0.023..0.100 rows=13 loops=10)

14. 0.099 0.370 ↓ 6.5 13 10

Nested Loop (cost=0.85..32.34 rows=2 width=4) (actual time=0.016..0.037 rows=13 loops=10)

15. 0.100 0.100 ↓ 2.0 2 10

Index Scan using index_roles_on_resource_id_and_resource_type on roles r (cost=0.42..8.45 rows=1 width=4) (actual time=0.008..0.010 rows=2 loops=10)

  • Index Cond: ((resource_id = org.id) AND ((resource_type)::text = 'Organization'::text))
  • Filter: ((name)::text <> 'guest'::text)
16. 0.171 0.171 ↓ 1.2 7 19

Index Scan using index_users_roles_on_role_id on users_roles ur (cost=0.42..23.83 rows=6 width=8) (actual time=0.004..0.009 rows=7 loops=19)

  • Index Cond: (role_id = r.id)
17. 0.504 0.504 ↑ 1.0 1 126

Index Scan using users_pkey on users u (cost=0.42..0.54 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=126)

  • Index Cond: (id = ur.user_id)
  • Filter: ((deleted_at IS NULL) AND chargeable)
18. 0.040 0.510 ↑ 1.0 1 10

Limit (cost=12.05..12.06 rows=1 width=26) (actual time=0.048..0.051 rows=1 loops=10)

19. 0.070 0.470 ↑ 1.0 1 10

Sort (cost=12.05..12.06 rows=1 width=26) (actual time=0.046..0.047 rows=1 loops=10)

  • Sort Key: s.created_at DESC
  • Sort Method: quicksort Memory: 25kB
20. 0.160 0.400 ↑ 1.0 1 10

Hash Join (cost=8.46..12.04 rows=1 width=26) (actual time=0.021..0.040 rows=1 loops=10)

  • Hash Cond: (plan.id = s.plan_id)
21. 0.140 0.140 ↑ 1.0 42 10

Seq Scan on subscription_plans plan (cost=0.00..3.42 rows=42 width=22) (actual time=0.002..0.014 rows=42 loops=10)

22. 0.020 0.100 ↑ 1.0 1 10

Hash (cost=8.45..8.45 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.080 0.080 ↑ 1.0 1 10

Index Scan using index_subscriptions_on_organization_id on subscriptions s (cost=0.42..8.45 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=10)

  • Index Cond: (organization_id = org.id)
  • Filter: (((state)::text = 'active'::text) AND ((active_until IS NULL) OR (active_until > now())))
24. 0.130 0.340 ↑ 1.0 1 10

Aggregate (cost=4.58..4.59 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=10)

25. 0.210 0.210 ↓ 7.4 52 10

Index Only Scan using idx_r_on_organization_id_deleted_at on repos (cost=0.42..4.56 rows=7 width=0) (actual time=0.007..0.021 rows=52 loops=10)

  • Index Cond: ((organization_id = org.id) AND (deleted_at IS NULL))
  • Heap Fetches: 0
26. 0.030 0.160 ↑ 1.0 1 10

Limit (cost=1.27..33.43 rows=1 width=23) (actual time=0.014..0.016 rows=1 loops=10)

27. 0.009 0.130 ↑ 1.0 1 10

Nested Loop (cost=1.27..33.43 rows=1 width=23) (actual time=0.013..0.013 rows=1 loops=10)

28. 0.036 0.100 ↑ 2.0 1 10

Nested Loop (cost=0.85..32.34 rows=2 width=4) (actual time=0.010..0.010 rows=1 loops=10)

29. 0.040 0.040 ↑ 1.0 1 10

Index Scan using index_roles_on_resource_id_and_resource_type on roles r_1 (cost=0.42..8.45 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: ((resource_id = pipe.id) AND ((resource_type)::text = 'Repo'::text))
  • Filter: ((name)::text = 'admin'::text)
  • Rows Removed by Filter: 0
30. 0.024 0.024 ↑ 6.0 1 8

Index Scan using index_users_roles_on_role_id on users_roles ur_1 (cost=0.42..23.83 rows=6 width=8) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (role_id = r_1.id)
31. 0.021 0.021 ↑ 1.0 1 7

Index Scan using users_pkey on users u_1 (cost=0.42..0.54 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (id = ur_1.user_id)
  • Filter: ((deleted_at IS NULL) AND chargeable)
32. 0.580 2.600 ↑ 1.0 1 10

Aggregate (cost=494.55..494.56 rows=1 width=8) (actual time=0.259..0.260 rows=1 loops=10)

33. 1.050 2.020 ↓ 7.1 235 10

Nested Loop (cost=0.99..494.47 rows=33 width=8) (actual time=0.018..0.202 rows=235 loops=10)

34. 0.090 0.090 ↑ 1.0 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p (cost=0.43..35.69 rows=9 width=4) (actual time=0.004..0.009 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
35. 0.880 0.880 ↑ 41.0 27 88

Index Only Scan using idx_c_on_current_phase_id_created_at_deleted_at on cards c (cost=0.56..39.92 rows=1,106 width=12) (actual time=0.003..0.010 rows=27 loops=88)

  • Index Cond: (current_phase_id = p.id)
  • Heap Fetches: 0
36. 0.050 0.260 ↑ 1.0 1 10

Aggregate (cost=33.43..33.44 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=10)

37. 0.035 0.210 ↓ 2.0 2 10

Nested Loop (cost=1.27..33.43 rows=1 width=4) (actual time=0.014..0.021 rows=2 loops=10)

38. 0.033 0.130 ↑ 1.0 2 10

Nested Loop (cost=0.85..32.33 rows=2 width=4) (actual time=0.009..0.013 rows=2 loops=10)

39. 0.040 0.040 ↓ 2.0 2 10

Index Scan using index_roles_on_resource_id_and_resource_type on roles r_2 (cost=0.42..8.45 rows=1 width=4) (actual time=0.003..0.004 rows=2 loops=10)

  • Index Cond: ((resource_id = pipe.id) AND ((resource_type)::text = 'Repo'::text))
40. 0.057 0.057 ↑ 6.0 1 19

Index Scan using index_users_roles_on_role_id on users_roles ur_2 (cost=0.42..23.83 rows=6 width=8) (actual time=0.003..0.003 rows=1 loops=19)

  • Index Cond: (role_id = r_2.id)
41. 0.045 0.045 ↑ 1.0 1 15

Index Scan using users_pkey on users u_2 (cost=0.42..0.54 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=15)

  • Index Cond: (id = ur_2.user_id)
  • Filter: ((deleted_at IS NULL) AND chargeable)
42. 0.520 2.400 ↑ 1.0 1 10

Aggregate (cost=494.55..494.56 rows=1 width=8) (actual time=0.240..0.240 rows=1 loops=10)

43. 0.940 1.880 ↓ 7.1 235 10

Nested Loop (cost=0.99..494.47 rows=33 width=0) (actual time=0.014..0.188 rows=235 loops=10)

44. 0.060 0.060 ↑ 1.0 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p_1 (cost=0.43..35.69 rows=9 width=4) (actual time=0.003..0.006 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
45. 0.880 0.880 ↑ 41.0 27 88

Index Only Scan using idx_c_on_current_phase_id_updated_at_deleted_at on cards c_1 (cost=0.56..39.92 rows=1,106 width=4) (actual time=0.003..0.010 rows=27 loops=88)

  • Index Cond: (current_phase_id = p_1.id)
  • Heap Fetches: 0
46. 0.040 76.810 ↑ 1.0 1 10

Aggregate (cost=2,197.27..2,197.28 rows=1 width=8) (actual time=7.681..7.681 rows=1 loops=10)

47. 0.010 76.770 ↓ 0.0 0 10

HashAggregate (cost=2,197.13..2,197.19 rows=6 width=4) (actual time=7.677..7.677 rows=0 loops=10)

  • Group Key: automations.id
48. 0.040 76.760 ↓ 0.0 0 10

Append (cost=0.41..2,197.12 rows=6 width=4) (actual time=7.676..7.676 rows=0 loops=10)

49. 0.040 0.040 ↓ 0.0 0 10

Index Scan using index_automations_on_event_repo_id_and_event_id on automations (cost=0.41..23.78 rows=3 width=4) (actual time=0.004..0.004 rows=0 loops=10)

  • Index Cond: (event_repo_id = pipe.id)
  • Filter: (deleted_at IS NULL)
50. 76.680 76.680 ↓ 0.0 0 10

Seq Scan on automations automations_1 (cost=0.00..2,173.28 rows=3 width=4) (actual time=7.668..7.668 rows=0 loops=10)

  • Filter: ((deleted_at IS NULL) AND (action_repo_id = pipe.id))
  • Rows Removed by Filter: 52902
51. 0.030 0.080 ↑ 1.0 1 10

Aggregate (cost=3,537.10..3,537.11 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=10)

52. 0.050 0.050 ↓ 0.0 0 10

Index Scan using index_inbox_emails_on_repo_id on inbox_emails (cost=0.43..3,534.55 rows=1,023 width=0) (actual time=0.005..0.005 rows=0 loops=10)

  • Index Cond: (repo_id = pipe.id)
  • Filter: (deleted_at IS NULL)
53. 0.020 0.070 ↑ 1.0 1 10

Aggregate (cost=4.54..4.54 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10)

54. 0.050 0.050 ↑ 2.5 2 10

Index Only Scan using idx_et_on_repo_id_deleted_at on email_templates (cost=0.42..4.52 rows=5 width=0) (actual time=0.005..0.005 rows=2 loops=10)

  • Index Cond: ((repo_id = pipe.id) AND (deleted_at IS NULL))
  • Heap Fetches: 0
55. 0.280 1.840 ↑ 1.0 1 10

Aggregate (cost=134.89..134.90 rows=1 width=8) (actual time=0.183..0.184 rows=1 loops=10)

56. 0.496 1.560 ↓ 3.8 115 10

Nested Loop (cost=1.28..134.82 rows=30 width=64) (actual time=0.014..0.156 rows=115 loops=10)

57. 0.032 0.140 ↑ 4.0 1 10

Nested Loop (cost=0.84..65.87 rows=4 width=4) (actual time=0.009..0.014 rows=1 loops=10)

58. 0.040 0.040 ↑ 2.5 2 10

Index Scan using idx_et_on_repo_id_deleted_at on email_templates t (cost=0.42..23.61 rows=5 width=4) (actual time=0.003..0.004 rows=2 loops=10)

  • Index Cond: ((repo_id = pipe.id) AND (deleted_at IS NULL))
59. 0.068 0.068 ↑ 1.0 1 17

Index Scan using idx_ams_on_email_template_id_deleted_at on automated_message_setups ams (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: ((email_template_id = t.id) AND (deleted_at IS NULL))
60. 0.924 0.924 ↑ 5.6 96 12

Index Scan using idx_am_on_automated_message_setup_id_deleted_at on automated_messages am (cost=0.43..11.85 rows=539 width=68) (actual time=0.007..0.077 rows=96 loops=12)

  • Index Cond: ((automated_message_setup_id = ams.id) AND (deleted_at IS NULL))
61. 0.020 0.060 ↑ 1.0 1 10

Aggregate (cost=16.76..16.77 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=10)

62. 0.010 0.040 ↓ 0.0 0 10

Nested Loop (cost=0.71..16.76 rows=1 width=79) (actual time=0.004..0.004 rows=0 loops=10)

63. 0.030 0.030 ↓ 0.0 0 10

Index Scan using index_pipe_relations_on_parent_id_and_child_id on pipe_relations (cost=0.29..8.31 rows=1 width=83) (actual time=0.003..0.003 rows=0 loops=10)

  • Index Cond: (parent_id = pipe.id)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using repos_pkey on repos child (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (id = pipe_relations.child_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Table'::text))
65. 0.010 0.030 ↑ 1.0 1 10

Aggregate (cost=16.76..16.77 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)

66. 0.010 0.020 ↓ 0.0 0 10

Nested Loop (cost=0.71..16.76 rows=1 width=79) (actual time=0.002..0.002 rows=0 loops=10)

67. 0.010 0.010 ↓ 0.0 0 10

Index Scan using index_pipe_relations_on_parent_id_and_child_id on pipe_relations pipe_relations_1 (cost=0.29..8.31 rows=1 width=83) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (parent_id = pipe.id)
68. 0.000 0.000 ↓ 0.0 0

Index Scan using repos_pkey on repos child_1 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (id = pipe_relations_1.child_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Pipe'::text))
69. 0.020 0.360 ↑ 1.0 1 10

Aggregate (cost=143.60..143.61 rows=1 width=8) (actual time=0.035..0.036 rows=1 loops=10)

70. 0.022 0.340 ↓ 0.0 0 10

Nested Loop (cost=0.85..143.59 rows=1 width=248) (actual time=0.034..0.034 rows=0 loops=10)

71. 0.270 0.270 ↑ 1.0 1 10

Index Scan using idx_f_on_repo_id_deleted_at on fields f (cost=0.43..135.14 rows=1 width=252) (actual time=0.025..0.027 rows=1 loops=10)

  • Index Cond: ((repo_id = pipe.id) AND (deleted_at IS NULL))
  • Filter: ((type_id)::text = 'connector'::text)
  • Rows Removed by Filter: 24
72. 0.048 0.048 ↓ 0.0 0 8

Index Scan using repos_pkey on repos r_3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=8)

  • Index Cond: (id = f.connected_pipe_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Table'::text))
  • Rows Removed by Filter: 1
73. 0.020 0.200 ↑ 1.0 1 10

Aggregate (cost=143.60..143.61 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=10)

74. 0.026 0.180 ↑ 1.0 1 10

Nested Loop (cost=0.85..143.59 rows=1 width=248) (actual time=0.015..0.018 rows=1 loops=10)

75. 0.130 0.130 ↑ 1.0 1 10

Index Scan using idx_f_on_repo_id_deleted_at on fields f_1 (cost=0.43..135.14 rows=1 width=252) (actual time=0.012..0.013 rows=1 loops=10)

  • Index Cond: ((repo_id = pipe.id) AND (deleted_at IS NULL))
  • Filter: ((type_id)::text = 'connector'::text)
  • Rows Removed by Filter: 24
76. 0.024 0.024 ↑ 1.0 1 8

Index Scan using repos_pkey on repos r_4 (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (id = f_1.connected_pipe_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Pipe'::text))
77. 0.030 0.110 ↑ 1.0 1 10

Aggregate (cost=35.72..35.73 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=10)

78. 0.080 0.080 ↑ 1.0 2 10

Index Scan using index_phases_on_repo_id_and_index on phases (cost=0.43..35.72 rows=2 width=0) (actual time=0.006..0.008 rows=2 loops=10)

  • Index Cond: (repo_id = pipe.id)
  • Filter: (lateness_time > 0)
  • Rows Removed by Filter: 6
79. 0.020 0.050 ↑ 1.0 1 10

Aggregate (cost=4.35..4.36 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=10)

80. 0.030 0.030 ↓ 0.0 0 10

Index Only Scan using index_reports_on_repo_id on reports (cost=0.29..4.34 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=10)

  • Index Cond: (repo_id = pipe.id)
  • Heap Fetches: 0
81. 0.020 2.140 ↑ 1.0 1 10

Aggregate (cost=35,672.37..35,672.38 rows=1 width=8) (actual time=0.214..0.214 rows=1 loops=10)

82. 0.056 2.120 ↓ 0.0 0 10

Nested Loop (cost=0.99..35,672.35 rows=7 width=0) (actual time=0.212..0.212 rows=0 loops=10)

83. 0.040 0.040 ↑ 1.0 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p_2 (cost=0.43..35.69 rows=9 width=4) (actual time=0.002..0.004 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
84. 2.024 2.024 ↓ 0.0 0 88

Index Scan using idx_c_on_current_phase_id_updated_at_deleted_at on cards c_2 (cost=0.56..3,957.17 rows=246 width=4) (actual time=0.023..0.023 rows=0 loops=88)

  • Index Cond: (current_phase_id = p_2.id)
  • Filter: (created_by_id = 5870)
  • Rows Removed by Filter: 27
85. 0.540 3.210 ↑ 1.0 1 10

Aggregate (cost=35,727.67..35,727.68 rows=1 width=8) (actual time=0.321..0.321 rows=1 loops=10)

86. 1.026 2.670 ↓ 9.0 233 10

Nested Loop (cost=0.99..35,727.61 rows=26 width=0) (actual time=0.014..0.267 rows=233 loops=10)

87. 0.060 0.060 ↑ 1.0 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p_3 (cost=0.43..35.69 rows=9 width=4) (actual time=0.002..0.006 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
88. 1.584 1.584 ↑ 33.1 26 88

Index Scan using idx_c_on_current_phase_id_updated_at_deleted_at on cards c_3 (cost=0.56..3,957.17 rows=860 width=4) (actual time=0.002..0.018 rows=26 loops=88)

  • Index Cond: (current_phase_id = p_3.id)
  • Filter: (created_by_id <> 5870)
  • Rows Removed by Filter: 0
Planning time : 5.104 ms
Execution time : 2,195.143 ms