explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bnDe

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 21,051.251 ↑ 1.0 10 1

Limit (cost=347,654.54..760,900.23 rows=10 width=775) (actual time=20,032.797..21,051.251 rows=10 loops=1)

2. 0.667 21,051.245 ↑ 40,205.0 10 1

Result (cost=347,654.54..16,614,890,545.67 rows=402,050 width=775) (actual time=20,032.796..21,051.245 rows=10 loops=1)

3. 177.901 19,746.848 ↑ 40,205.0 10 1

Sort (cost=347,654.54..348,659.67 rows=402,050 width=131) (actual time=19,746.837..19,746.848 rows=10 loops=1)

  • Sort Key: org.id, pipe.id
  • Sort Method: top-N heapsort Memory: 26kB
4. 1,341.038 19,568.947 ↓ 1.0 417,043 1

Nested Loop Left Join (cost=0.85..338,966.39 rows=402,050 width=131) (actual time=8.018..19,568.947 rows=417,043 loops=1)

5. 197.830 15,725.651 ↓ 1.0 417,043 1

Nested Loop (cost=0.42..132,843.88 rows=402,050 width=66) (actual time=7.923..15,725.651 rows=417,043 loops=1)

6. 414.429 414.429 ↓ 1.0 164,276 1

Seq Scan on organizations org (cost=0.00..6,374.50 rows=160,891 width=24) (actual time=6.274..414.429 rows=164,276 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 5212
7. 15,113.392 15,113.392 ↑ 2.3 3 164,276

Index Scan using idx_r_on_organization_id_deleted_at on repos pipe (cost=0.42..0.72 rows=7 width=46) (actual time=0.058..0.092 rows=3 loops=164,276)

  • Index Cond: ((organization_id = org.id) AND (deleted_at IS NULL))
  • Filter: ((type)::text = 'Pipe'::text)
  • Rows Removed by Filter: 0
8. 2,502.258 2,502.258 ↑ 1.0 1 417,043

Index Scan using repos_pkey on repos parent (cost=0.42..0.50 rows=1 width=25) (actual time=0.005..0.006 rows=1 loops=417,043)

  • Index Cond: (id = pipe.clone_from_id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
9.          

SubPlan (forResult)

10. 0.170 1.460 ↑ 1.0 1 10

Aggregate (cost=11.58..11.59 rows=1 width=8) (actual time=0.146..0.146 rows=1 loops=10)

11. 0.090 1.290 ↓ 14.0 14 10

Nested Loop (cost=1.27..11.57 rows=1 width=4) (actual time=0.031..0.129 rows=14 loops=10)

12. 0.071 0.390 ↓ 7.0 14 10

Nested Loop (cost=0.85..10.59 rows=2 width=4) (actual time=0.020..0.039 rows=14 loops=10)

13. 0.110 0.110 ↓ 2.0 2 10

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

  • Index Cond: ((resource_id = org.id) AND ((resource_type)::text = 'Organization'::text))
  • Filter: ((name)::text <> 'guest'::text)
14. 0.209 0.209 ↑ 1.0 7 19

Index Scan using index_users_roles_on_role_id on users_roles ur (cost=0.42..8.07 rows=7 width=8) (actual time=0.005..0.011 rows=7 loops=19)

  • Index Cond: (role_id = r.id)
15. 0.810 0.810 ↑ 1.0 1 135

Index Scan using users_pkey on users u (cost=0.42..0.48 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=135)

  • Index Cond: (id = ur.user_id)
  • Filter: ((deleted_at IS NULL) AND chargeable)
16. 0.020 0.260 ↑ 1.0 1 10

Limit (cost=4.62..4.63 rows=1 width=25) (actual time=0.026..0.026 rows=1 loops=10)

17. 0.080 0.240 ↑ 1.0 1 10

Sort (cost=4.62..4.63 rows=1 width=25) (actual time=0.024..0.024 rows=1 loops=10)

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

Nested Loop (cost=0.56..4.61 rows=1 width=25) (actual time=0.015..0.016 rows=1 loops=10)

19. 0.090 0.090 ↑ 1.0 1 10

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

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

Index Scan using subscription_plans_pkey on subscription_plans plan (cost=0.14..2.16 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (id = s.plan_id)
21. 0.070 0.500 ↑ 1.0 1 10

Aggregate (cost=5.14..5.15 rows=1 width=8) (actual time=0.050..0.050 rows=1 loops=10)

22. 0.430 0.430 ↓ 7.4 52 10

Index Only Scan using idx_r_on_organization_id_deleted_at on repos (cost=0.42..5.12 rows=7 width=0) (actual time=0.018..0.043 rows=52 loops=10)

  • Index Cond: ((organization_id = org.id) AND (deleted_at IS NULL))
  • Heap Fetches: 244
23. 0.010 1.890 ↑ 1.0 1 10

Limit (cost=1.27..11.57 rows=1 width=23) (actual time=0.189..0.189 rows=1 loops=10)

24. 0.025 1.880 ↑ 1.0 1 10

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

25. 0.014 1.820 ↑ 2.0 1 10

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

26. 0.070 0.070 ↑ 1.0 1 10

Index Scan using index_roles_on_resource_id_and_resource_type on roles r_1 (cost=0.42..2.45 rows=1 width=4) (actual time=0.007..0.007 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
27. 1.736 1.736 ↑ 7.0 1 8

Index Scan using index_users_roles_on_role_id on users_roles ur_1 (cost=0.42..8.07 rows=7 width=8) (actual time=0.217..0.217 rows=1 loops=8)

  • Index Cond: (role_id = r_1.id)
28. 0.035 0.035 ↑ 1.0 1 7

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

  • Index Cond: (id = ur_1.user_id)
  • Filter: ((deleted_at IS NULL) AND chargeable)
29. 0.300 64.750 ↑ 1.0 1 10

Aggregate (cost=857.21..857.22 rows=1 width=8) (actual time=6.475..6.475 rows=1 loops=10)

30. 0.384 64.450 ↓ 4.1 235 10

Nested Loop (cost=0.99..857.07 rows=58 width=8) (actual time=0.842..6.445 rows=235 loops=10)

31. 1.850 1.850 ↑ 1.6 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p (cost=0.43..12.73 rows=14 width=4) (actual time=0.005..0.185 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
32. 62.216 62.216 ↑ 51.9 27 88

Index Only Scan using idx_c_on_current_phase_id_created_at_deleted_at on cards c (cost=0.56..46.29 rows=1,402 width=12) (actual time=0.494..0.707 rows=27 loops=88)

  • Index Cond: (current_phase_id = p.id)
  • Heap Fetches: 461
33. 0.090 0.390 ↑ 1.0 1 10

Aggregate (cost=11.57..11.58 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=10)

34. 0.025 0.300 ↓ 2.0 2 10

Nested Loop (cost=1.27..11.57 rows=1 width=4) (actual time=0.023..0.030 rows=2 loops=10)

35. 0.034 0.200 ↑ 1.0 2 10

Nested Loop (cost=0.85..10.59 rows=2 width=4) (actual time=0.016..0.020 rows=2 loops=10)

36. 0.090 0.090 ↓ 2.0 2 10

Index Scan using index_roles_on_resource_id_and_resource_type on roles r_2 (cost=0.42..2.44 rows=1 width=4) (actual time=0.008..0.009 rows=2 loops=10)

  • Index Cond: ((resource_id = pipe.id) AND ((resource_type)::text = 'Repo'::text))
37. 0.076 0.076 ↑ 7.0 1 19

Index Scan using index_users_roles_on_role_id on users_roles ur_2 (cost=0.42..8.07 rows=7 width=8) (actual time=0.004..0.004 rows=1 loops=19)

  • Index Cond: (role_id = r_2.id)
38. 0.075 0.075 ↑ 1.0 1 15

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

  • Index Cond: (id = ur_2.user_id)
  • Filter: ((deleted_at IS NULL) AND chargeable)
39. 0.270 24.780 ↑ 1.0 1 10

Aggregate (cost=801.21..801.22 rows=1 width=8) (actual time=2.478..2.478 rows=1 loops=10)

40. 0.386 24.510 ↓ 4.1 235 10

Nested Loop (cost=0.99..801.07 rows=58 width=0) (actual time=0.182..2.451 rows=235 loops=10)

41. 0.100 0.100 ↑ 1.6 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p_1 (cost=0.43..12.73 rows=14 width=4) (actual time=0.005..0.010 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
42. 24.024 24.024 ↑ 51.9 27 88

Index Only Scan using idx_c_on_current_phase_id_due_date_deleted_at on cards c_1 (cost=0.56..42.29 rows=1,402 width=4) (actual time=0.132..0.273 rows=27 loops=88)

  • Index Cond: (current_phase_id = p_1.id)
  • Heap Fetches: 461
43. 0.070 444.440 ↑ 1.0 1 10

Aggregate (cost=4,500.04..4,500.05 rows=1 width=8) (actual time=44.443..44.444 rows=1 loops=10)

44. 0.020 444.370 ↓ 0.0 0 10

HashAggregate (cost=4,499.91..4,499.97 rows=6 width=4) (actual time=44.437..44.437 rows=0 loops=10)

  • Group Key: automations.id
45. 0.030 444.350 ↓ 0.0 0 10

Append (cost=0.41..4,499.89 rows=6 width=4) (actual time=44.435..44.435 rows=0 loops=10)

46. 0.060 0.060 ↓ 0.0 0 10

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

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

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

  • Filter: ((deleted_at IS NULL) AND (action_repo_id = pipe.id))
  • Rows Removed by Filter: 65351
48. 0.020 5.130 ↑ 1.0 1 10

Aggregate (cost=1,205.15..1,205.16 rows=1 width=8) (actual time=0.513..0.513 rows=1 loops=10)

49. 5.110 5.110 ↓ 0.0 0 10

Index Scan using index_inbox_emails_on_repo_id on inbox_emails (cost=0.43..1,201.92 rows=1,292 width=0) (actual time=0.511..0.511 rows=0 loops=10)

  • Index Cond: (repo_id = pipe.id)
  • Filter: (deleted_at IS NULL)
50. 0.030 15.580 ↑ 1.0 1 10

Aggregate (cost=2.56..2.57 rows=1 width=8) (actual time=1.558..1.558 rows=1 loops=10)

51. 15.550 15.550 ↑ 3.0 2 10

Index Only Scan using idx_et_on_repo_id_deleted_at on email_templates (cost=0.42..2.54 rows=6 width=0) (actual time=0.883..1.555 rows=2 loops=10)

  • Index Cond: ((repo_id = pipe.id) AND (deleted_at IS NULL))
  • Heap Fetches: 5
52. 0.320 552.100 ↑ 1.0 1 10

Aggregate (cost=124.08..124.09 rows=1 width=8) (actual time=55.210..55.210 rows=1 loops=10)

53. 0.300 551.780 ↓ 3.0 115 10

Nested Loop (cost=1.28..123.99 rows=38 width=64) (actual time=2.812..55.178 rows=115 loops=10)

54. 0.054 21.080 ↑ 5.0 1 10

Nested Loop (cost=0.85..22.25 rows=5 width=4) (actual time=1.118..2.108 rows=1 loops=10)

55. 8.820 8.820 ↑ 3.0 2 10

Index Scan using idx_et_on_repo_id_deleted_at on email_templates t (cost=0.42..7.52 rows=6 width=4) (actual time=0.262..0.882 rows=2 loops=10)

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

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

  • Index Cond: ((email_template_id = t.id) AND (deleted_at IS NULL))
57. 530.400 530.400 ↑ 6.8 96 12

Index Scan using idx_am_on_automated_message_setup_id_deleted_at on automated_messages am (cost=0.43..13.78 rows=657 width=68) (actual time=2.316..44.200 rows=96 loops=12)

  • Index Cond: ((automated_message_setup_id = ams.id) AND (deleted_at IS NULL))
58. 0.010 3.190 ↑ 1.0 1 10

Aggregate (cost=4.77..4.78 rows=1 width=8) (actual time=0.319..0.319 rows=1 loops=10)

59. 0.020 3.180 ↓ 0.0 0 10

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

60. 3.160 3.160 ↓ 0.0 0 10

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

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

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

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

Aggregate (cost=4.77..4.78 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=10)

63. 0.010 0.030 ↓ 0.0 0 10

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

64. 0.020 0.020 ↓ 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..2.31 rows=1 width=83) (actual time=0.002..0.002 rows=0 loops=10)

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

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

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

Aggregate (cost=76.48..76.49 rows=1 width=8) (actual time=13.794..13.795 rows=1 loops=10)

67. 0.026 137.920 ↓ 0.0 0 10

Nested Loop (cost=0.86..76.48 rows=1 width=243) (actual time=13.792..13.792 rows=0 loops=10)

68. 137.830 137.830 ↑ 1.0 1 10

Index Scan using idx_f_on_repo_id_deleted_at on fields f (cost=0.43..74.03 rows=1 width=247) (actual time=12.429..13.783 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
69. 0.064 0.064 ↓ 0.0 0 8

Index Scan using repos_pkey on repos r_3 (cost=0.42..2.45 rows=1 width=4) (actual time=0.008..0.008 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
70. 0.030 0.420 ↑ 1.0 1 10

Aggregate (cost=76.48..76.49 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=10)

71. 0.018 0.390 ↑ 1.0 1 10

Nested Loop (cost=0.86..76.48 rows=1 width=243) (actual time=0.036..0.039 rows=1 loops=10)

72. 0.300 0.300 ↑ 1.0 1 10

Index Scan using idx_f_on_repo_id_deleted_at on fields f_1 (cost=0.43..74.03 rows=1 width=247) (actual time=0.029..0.030 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
73. 0.072 0.072 ↑ 1.0 1 8

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

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

Aggregate (cost=12.78..12.79 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=10)

75. 0.150 0.150 ↑ 1.5 2 10

Index Scan using index_phases_on_repo_id_and_index on phases (cost=0.43..12.77 rows=3 width=0) (actual time=0.011..0.015 rows=2 loops=10)

  • Index Cond: (repo_id = pipe.id)
  • Filter: (lateness_time > 0)
  • Rows Removed by Filter: 6
76. 0.030 8.470 ↑ 1.0 1 10

Aggregate (cost=4.02..4.03 rows=1 width=8) (actual time=0.847..0.847 rows=1 loops=10)

77. 8.440 8.440 ↓ 0.0 0 10

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

  • Index Cond: (repo_id = pipe.id)
  • Heap Fetches: 3
78. 0.020 39.380 ↑ 1.0 1 10

Aggregate (cost=16,749.53..16,749.54 rows=1 width=8) (actual time=3.938..3.938 rows=1 loops=10)

79. 0.032 39.360 ↓ 0.0 0 10

Nested Loop (cost=0.99..16,749.50 rows=13 width=0) (actual time=3.936..3.936 rows=0 loops=10)

80. 0.080 0.080 ↑ 1.6 9 10

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

  • Index Cond: (repo_id = pipe.id)
81. 39.248 39.248 ↓ 0.0 0 88

Index Scan using idx_c_on_current_phase_id_due_date_deleted_at on cards c_2 (cost=0.56..1,192.44 rows=304 width=4) (actual time=0.446..0.446 rows=0 loops=88)

  • Index Cond: (current_phase_id = p_2.id)
  • Filter: (created_by_id = 5870)
  • Rows Removed by Filter: 27
82. 0.260 2.810 ↑ 1.0 1 10

Aggregate (cost=16,860.77..16,860.78 rows=1 width=8) (actual time=0.281..0.281 rows=1 loops=10)

83. 0.348 2.550 ↓ 5.2 233 10

Nested Loop (cost=0.99..16,860.66 rows=45 width=0) (actual time=0.021..0.255 rows=233 loops=10)

84. 0.090 0.090 ↑ 1.6 9 10

Index Scan using index_phases_on_repo_id_and_index on phases p_3 (cost=0.43..12.73 rows=14 width=4) (actual time=0.005..0.009 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
85. 2.112 2.112 ↑ 42.2 26 88

Index Scan using idx_c_on_current_phase_id_due_date_deleted_at on cards c_3 (cost=0.56..1,192.44 rows=1,098 width=4) (actual time=0.004..0.024 rows=26 loops=88)

  • Index Cond: (current_phase_id = p_3.id)
  • Filter: (created_by_id <> 5870)
  • Rows Removed by Filter: 0