explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2lQN

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,794.843 ↑ 1.0 31 1

Limit (cost=186.07..400,867.79 rows=31 width=81) (actual time=9.344..3,794.843 rows=31 loops=1)

2. 0.041 3,794.840 ↑ 2.4 31 1

Result (cost=186.07..956,652.11 rows=74 width=81) (actual time=9.343..3,794.840 rows=31 loops=1)

3. 0.144 4.336 ↑ 2.4 31 1

Sort (cost=186.07..186.26 rows=74 width=73) (actual time=4.322..4.336 rows=31 loops=1)

  • Sort Key: (lower((repos.name)::text)), repos.id
  • Sort Method: top-N heapsort Memory: 29kB
4. 4.068 4.192 ↓ 1.1 81 1

Index Scan using idx_r_on_organization_id_deleted_at on repos (cost=31.70..183.87 rows=74 width=73) (actual time=0.162..4.192 rows=81 loops=1)

  • Index Cond: ((organization_id = 64103) AND (deleted_at IS NULL))
  • Filter: ((public OR anyone_can_create_card) AND (NOT (hashed SubPlan 2)) AND ((type)::text = 'Pipe'::text))
  • Rows Removed by Filter: 174
5.          

SubPlan (forIndex Scan)

6. 0.000 0.124 ↓ 7.0 7 1

Unique (cost=31.27..31.27 rows=1 width=4) (actual time=0.123..0.124 rows=7 loops=1)

7. 0.009 0.124 ↓ 7.0 7 1

Sort (cost=31.27..31.27 rows=1 width=4) (actual time=0.123..0.124 rows=7 loops=1)

  • Sort Key: repos_1.id
  • Sort Method: quicksort Memory: 25kB
8. 0.004 0.115 ↓ 7.0 7 1

Nested Loop (cost=1.27..31.26 rows=1 width=4) (actual time=0.029..0.115 rows=7 loops=1)

9. 0.002 0.057 ↓ 1.5 9 1

Nested Loop (cost=0.85..28.19 rows=6 width=4) (actual time=0.022..0.057 rows=9 loops=1)

10. 0.011 0.011 ↓ 1.4 11 1

Index Only Scan using index_users_roles_on_user_id_and_role_id on users_roles (cost=0.42..6.95 rows=8 width=4) (actual time=0.007..0.011 rows=11 loops=1)

  • Index Cond: (user_id = 108659)
  • Heap Fetches: 10
11. 0.044 0.044 ↑ 1.0 1 11

Index Scan using roles_pkey on roles (cost=0.42..2.65 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=11)

  • Index Cond: (id = users_roles.role_id)
  • Filter: ((resource_type)::text = 'Repo'::text)
  • Rows Removed by Filter: 0
12. 0.054 0.054 ↑ 1.0 1 9

Index Scan using repos_pkey on repos repos_1 (cost=0.42..0.50 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=9)

  • Index Cond: (id = roles.resource_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Pipe'::text) AND (organization_id = 64103))
  • Rows Removed by Filter: 0
13.          

SubPlan (forResult)

14. 400.334 3,790.463 ↑ 1.0 1 31

Aggregate (cost=12,925.19..12,925.20 rows=1 width=8) (actual time=122.273..122.273 rows=1 loops=31)

15. 485.287 3,390.129 ↓ 3,650.0 146,001 31

Nested Loop (cost=0.99..12,925.09 rows=40 width=162) (actual time=0.041..109.359 rows=146,001 loops=31)

16. 0.434 0.434 ↑ 1.3 7 31

Index Scan using index_phases_on_repo_id_and_index on phases p (cost=0.43..14.02 rows=9 width=4) (actual time=0.006..0.014 rows=7 loops=31)

  • Index Cond: (repo_id = repos.id)
  • Filter: ((done IS FALSE) AND (index <> '0'::numeric))
  • Rows Removed by Filter: 3
17. 2,904.408 2,904.408 ↓ 12.7 19,509 232

Index Scan using idx_c_on_current_phase_id_due_date_deleted_at on cards c (cost=0.56..1,419.23 rows=1,533 width=166) (actual time=0.009..12.519 rows=19,509 loops=232)

  • Index Cond: (current_phase_id = p.id)