explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k6b9

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 21,499.309 ↑ 1.0 10 1

Limit (cost=347,676.65..838,653.68 rows=10 width=775) (actual time=20,460.271..21,499.309 rows=10 loops=1)

2. 0.683 21,499.302 ↑ 40,209.2 10 1

Result (cost=347,676.65..19,742,141,200.67 rows=402,092 width=775) (actual time=20,460.270..21,499.302 rows=10 loops=1)

3. 211.060 20,437.109 ↑ 40,209.2 10 1

Sort (cost=347,676.65..348,681.88 rows=402,092 width=131) (actual time=20,437.100..20,437.109 rows=10 loops=1)

  • Sort Key: org.id, pipe.id
  • Sort Method: top-N heapsort Memory: 26kB
4. 1,808.868 20,226.049 ↓ 1.0 417,090 1

Nested Loop Left Join (cost=0.85..338,987.59 rows=402,092 width=131) (actual time=6.794..20,226.049 rows=417,090 loops=1)

5. 75.085 15,914.641 ↓ 1.0 417,090 1

Nested Loop (cost=0.42..132,844.88 rows=402,092 width=66) (actual time=6.770..15,914.641 rows=417,090 loops=1)

6. 558.819 558.819 ↓ 1.0 164,309 1

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

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 5219
7. 15,280.737 15,280.737 ↑ 2.3 3 164,309

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.054..0.093 rows=3 loops=164,309)

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

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

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

SubPlan (forResult)

10. 0.190 6.300 ↑ 1.0 1 10

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

11. 0.020 6.110 ↓ 14.0 14 10

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

12. 0.063 0.420 ↓ 7.0 14 10

Nested Loop (cost=0.85..10.59 rows=2 width=4) (actual time=0.020..0.042 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.247 0.247 ↑ 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.006..0.013 rows=7 loops=19)

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

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

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

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

17. 0.060 0.220 ↑ 1.0 1 10

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

  • Sort Key: s.created_at DESC
  • Sort Method: quicksort Memory: 25kB
18. 0.040 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.008..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.030 0.030 ↑ 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.003..0.003 rows=1 loops=10)

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

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

22. 1.680 1.680 ↓ 7.4 52 10

Index Only Scan using idx_r_on_organization_id_deleted_at on repos (cost=0.42..5.13 rows=7 width=0) (actual time=0.142..0.168 rows=52 loops=10)

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

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

24. 0.018 1.780 ↑ 1.0 1 10

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

25. 0.030 1.720 ↑ 2.0 1 10

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

26. 1.650 1.650 ↑ 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.165..0.165 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. 0.040 0.040 ↑ 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.005..0.005 rows=1 loops=8)

  • Index Cond: (role_id = r_1.id)
28. 0.042 0.042 ↑ 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.006..0.006 rows=1 loops=7)

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

Aggregate (cost=6,817.53..6,817.54 rows=1 width=8) (actual time=14.876..14.876 rows=1 loops=10)

30. 0.394 148.430 ↓ 4.0 235 10

Nested Loop (cost=0.99..6,817.39 rows=59 width=8) (actual time=1.501..14.843 rows=235 loops=10)

31. 10.580 10.580 ↑ 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.456..1.058 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
32. 137.456 137.456 ↑ 52.7 27 88

Index Only Scan using idx_c_on_current_phase_id_created_at_deleted_at on cards c (cost=0.56..471.83 rows=1,422 width=12) (actual time=0.500..1.562 rows=27 loops=88)

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

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

34. 0.040 0.350 ↓ 2.0 2 10

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

35. 0.035 0.220 ↑ 1.0 2 10

Nested Loop (cost=0.85..10.59 rows=2 width=4) (actual time=0.017..0.022 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.007..0.009 rows=2 loops=10)

  • Index Cond: ((resource_id = pipe.id) AND ((resource_type)::text = 'Repo'::text))
37. 0.095 0.095 ↑ 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.005 rows=1 loops=19)

  • Index Cond: (role_id = r_2.id)
38. 0.090 0.090 ↑ 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.006..0.006 rows=1 loops=15)

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

Aggregate (cost=6,761.53..6,761.54 rows=1 width=8) (actual time=3.916..3.916 rows=1 loops=10)

40. 0.450 38.850 ↓ 4.0 235 10

Nested Loop (cost=0.99..6,761.39 rows=59 width=0) (actual time=0.599..3.885 rows=235 loops=10)

41. 0.120 0.120 ↑ 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.006..0.012 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
42. 38.280 38.280 ↑ 52.7 27 88

Index Only Scan using idx_c_on_current_phase_id_due_date_deleted_at on cards c_1 (cost=0.56..467.83 rows=1,422 width=4) (actual time=0.266..0.435 rows=27 loops=88)

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

Aggregate (cost=11.27..11.28 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=10)

44. 0.020 0.160 ↓ 0.0 0 10

HashAggregate (cost=11.14..11.20 rows=6 width=4) (actual time=0.016..0.016 rows=0 loops=10)

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

Append (cost=0.41..11.12 rows=6 width=4) (actual time=0.014..0.014 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. 0.050 0.050 ↓ 0.0 0 10

Index Scan using idx_dba_tmp_automations on automations automations_1 (cost=0.29..4.00 rows=3 width=4) (actual time=0.005..0.005 rows=0 loops=10)

  • Index Cond: (action_repo_id = pipe.id)
48. 0.030 4.880 ↑ 1.0 1 10

Aggregate (cost=1,218.39..1,218.40 rows=1 width=8) (actual time=0.488..0.488 rows=1 loops=10)

49. 4.850 4.850 ↓ 0.0 0 10

Index Scan using index_inbox_emails_on_repo_id on inbox_emails (cost=0.43..1,215.14 rows=1,303 width=0) (actual time=0.485..0.485 rows=0 loops=10)

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

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

51. 17.320 17.320 ↑ 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=1.271..1.732 rows=2 loops=10)

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

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

53. 0.264 615.500 ↓ 3.0 115 10

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

54. 0.046 30.740 ↑ 5.0 1 10

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

55. 15.700 15.700 ↑ 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.430..1.570 rows=2 loops=10)

  • Index Cond: ((repo_id = pipe.id) AND (deleted_at IS NULL))
56. 14.994 14.994 ↑ 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.881..0.882 rows=1 loops=17)

  • Index Cond: ((email_template_id = t.id) AND (deleted_at IS NULL))
57. 584.496 584.496 ↑ 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=7.413..48.708 rows=96 loops=12)

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

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

59. 0.010 1.700 ↓ 0.0 0 10

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

60. 1.690 1.690 ↓ 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.169..0.169 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.020 159.560 ↑ 1.0 1 10

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

67. 0.036 159.540 ↓ 0.0 0 10

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

68. 159.440 159.440 ↑ 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=14.404..15.944 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.040 0.500 ↑ 1.0 1 10

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

71. 0.018 0.460 ↑ 1.0 1 10

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

72. 0.370 0.370 ↑ 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.034..0.037 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.020 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.160 0.160 ↑ 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.012..0.016 rows=2 loops=10)

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

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

77. 7.350 7.350 ↓ 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.418..0.735 rows=0 loops=10)

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

Aggregate (cost=16,912.41..16,912.42 rows=1 width=8) (actual time=5.204..5.204 rows=1 loops=10)

79. 0.116 52.020 ↓ 0.0 0 10

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

80. 0.160 0.160 ↑ 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.005..0.016 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
81. 51.744 51.744 ↓ 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,204.06 rows=306 width=4) (actual time=0.588..0.588 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 3.350 ↑ 1.0 1 10

Aggregate (cost=17,025.90..17,025.91 rows=1 width=8) (actual time=0.335..0.335 rows=1 loops=10)

83. 0.398 3.090 ↓ 5.0 233 10

Nested Loop (cost=0.99..17,025.78 rows=47 width=0) (actual time=0.029..0.309 rows=233 loops=10)

84. 0.140 0.140 ↑ 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.010..0.014 rows=9 loops=10)

  • Index Cond: (repo_id = pipe.id)
85. 2.552 2.552 ↑ 42.9 26 88

Index Scan using idx_c_on_current_phase_id_due_date_deleted_at on cards c_3 (cost=0.56..1,204.06 rows=1,116 width=4) (actual time=0.005..0.029 rows=26 loops=88)

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