explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1X0 : Optimization for: plan #OHNX

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.056 3.560 ↑ 1.8 54 1

Sort (cost=671.87..672.11 rows=98 width=3,172) (actual time=3.557..3.560 rows=54 loops=1)

  • Sort Key: projects.created_on DESC
  • Sort Method: quicksort Memory: 52kB
2. 0.098 3.504 ↑ 1.8 54 1

Hash Left Join (cost=569.29..668.63 rows=98 width=3,172) (actual time=3.395..3.504 rows=54 loops=1)

  • Hash Cond: (project_members_1.id = membership_request_1.member_id)
3. 0.117 3.405 ↑ 1.8 54 1

Hash Right Join (cost=556.14..653.53 rows=98 width=2,628) (actual time=3.372..3.405 rows=54 loops=1)

  • Hash Cond: (project_settings_1.project_id = projects.id)
4. 0.209 0.209 ↓ 1.1 1,222 1

Seq Scan on project_settings project_settings_1 (cost=0.00..80.54 rows=1,154 width=49) (actual time=0.004..0.209 rows=1,222 loops=1)

5. 0.048 3.079 ↑ 1.8 54 1

Hash (cost=554.92..554.92 rows=98 width=2,579) (actual time=3.079..3.079 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
6. 0.027 3.031 ↑ 1.8 54 1

Hash Right Join (cost=497.23..554.92 rows=98 width=2,579) (actual time=3.013..3.031 rows=54 loops=1)

  • Hash Cond: (project_tags_1.project_id = projects.id)
7. 0.000 0.000 ↓ 0.0 0 1

Hash Join (cost=24.85..75.22 rows=1,700 width=110) (actual time=0.000..0.000 rows=0 loops=1)

  • Hash Cond: (project_tags_1.tag_id = tags_1.id)
8. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on project_tags project_tags_1 (cost=0.00..27.00 rows=1,700 width=20) (actual time=0.000..0.000 rows=0 loops=1)

9. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.60..16.60 rows=660 width=94) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on tags tags_1 (cost=0.00..16.60 rows=660 width=94) (never executed)

11. 0.068 3.004 ↓ 4.9 54 1

Hash (cost=472.24..472.24 rows=11 width=2,485) (actual time=3.004..3.004 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
12. 0.002 2.936 ↓ 4.9 54 1

Nested Loop Left Join (cost=346.51..472.24 rows=11 width=2,485) (actual time=2.814..2.936 rows=54 loops=1)

13. 0.017 2.880 ↓ 4.9 54 1

Nested Loop Left Join (cost=346.37..470.08 rows=11 width=1,891) (actual time=2.810..2.880 rows=54 loops=1)

14. 0.035 2.863 ↓ 4.9 54 1

Hash Left Join (cost=346.22..464.87 rows=11 width=1,199) (actual time=2.808..2.863 rows=54 loops=1)

  • Hash Cond: (((projects.project_type)::text = (project_types_1.name)::text) AND ((projects.parent_type)::text = (project_types_1.vault_type)::text))
15. 0.121 2.815 ↓ 4.9 54 1

Hash Right Join (cost=344.92..463.48 rows=11 width=499) (actual time=2.781..2.815 rows=54 loops=1)

  • Hash Cond: (project_members_1.project_id = projects.id)
16. 0.193 0.193 ↓ 1.1 1,270 1

Seq Scan on project_members project_members_1 (cost=0.00..113.96 rows=1,196 width=36) (actual time=0.002..0.193 rows=1,270 loops=1)

17. 0.029 2.501 ↓ 4.7 52 1

Hash (cost=344.79..344.79 rows=11 width=463) (actual time=2.501..2.501 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
18. 0.055 2.472 ↓ 4.7 52 1

Hash Right Join (cost=339.50..344.79 rows=11 width=463) (actual time=2.461..2.472 rows=52 loops=1)

  • Hash Cond: (project_attributes_1.project_id = projects.id)
  • -> Seq Scan on project_attributes project_attributes_1 (cost=0.00..4.58 rows=158 width=60) (actual time=0.002..0.018 rows=168 loops=
19. 0.016 2.417 ↓ 4.7 52 1

Hash (cost=339.37..339.37 rows=11 width=403) (actual time=2.417..2.417 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
20. 0.002 2.401 ↓ 4.7 52 1

Limit (cost=339.23..339.26 rows=11 width=403) (actual time=2.396..2.401 rows=52 loops=1)

21. 0.044 2.399 ↓ 4.7 52 1

Sort (cost=339.23..339.26 rows=11 width=403) (actual time=2.395..2.399 rows=52 loops=1)

  • Sort Key: projects.created_on DESC
  • Sort Method: quicksort Memory: 38kB
22. 0.087 2.355 ↓ 4.7 52 1

Nested Loop (cost=189.22..339.04 rows=11 width=403) (actual time=2.205..2.355 rows=52 loops=1)

  • -> Index Only Scan using uq_name_vault_type on project_types (cost=0.14..0.16 rows=1 width=90) (actual time=
23. 0.919 2.268 ↓ 4.7 52 1

Hash Left Join (cost=189.08..337.12 rows=11 width=403) (actual time=2.191..2.268 rows=52 loops=1)

  • Hash Cond: (project_members.user_id = users.id)
  • Filter: (((users.user_id = ANY ('{b385f641-f95a-4fa2-a3eb-c5a032eb15cd,3a5a0287-7bee-45dd-8081-1a6bf775a
  • Rows Removed by Filter: 1056
  • -> Hash Right Join (cost=180.01..307.93 rows=968 width=411) (actual time=1.301..1.959 rows=1108 loops=
  • Hash Cond: (project_members.project_id = projects.id)
  • -> Seq Scan on project_members (cost=0.00..113.96 rows=1196 width=24) (actual time=0.003..0.218
  • Index Cond: (name = (projects.project_type)::text)
  • Heap Fetches: 52
24. 1.286 1.286 ↓ 1.1 1,067 1

Hash (cost=167.91..167.91 rows=968 width=403) (actual time=1.286..1.286 rows=1,067 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 422kB
  • -> Seq Scan on projects (cost=0.00..167.91 rows=968 width=403) (actual time=0.010..0.584 r
  • Filter: (((parent_type)::text = 'PROJECTS'::text) AND (state = 'READY'::project_states
  • Rows Removed by Filter: 155
25. 0.063 0.063 ↓ 1.1 200 1

Hash (cost=6.81..6.81 rows=181 width=20) (actual time=0.063..0.063 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • -> Seq Scan on users (cost=0.00..6.81 rows=181 width=20) (actual time=0.004..0.032 rows=200 loop
26. 0.007 0.013 ↓ 1.1 13 1

Hash (cost=1.12..1.12 rows=12 width=700) (actual time=0.013..0.013 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.006 0.006 ↓ 1.6 19 1

Seq Scan on project_types project_types_1 (cost=0.00..1.12 rows=12 width=700) (actual time=0.003..0.006 rows=19 loops=1)

28. 0.000 0.000 ↓ 0.0 0 54

Index Scan using pk_attributes on attributes attributes_1 (cost=0.14..0.46 rows=1 width=692) (actual time=0.000..0.000 rows=0 loops=54)

  • Index Cond: (id = project_attributes_1.attribute_id)
29. 0.054 0.054 ↑ 1.0 1 54

Index Scan using pk_users on users users_1 (cost=0.14..0.19 rows=1 width=594) (actual time=0.001..0.001 rows=1 loops=54)

  • Index Cond: (id = project_members_1.user_id)
30. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=11.40..11.40 rows=140 width=544) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
31. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on membership_request membership_request_1 (cost=0.00..11.40 rows=140 width=544) (actual time=0.000..0.000 rows=0 loops=1)

Planning time : 1.585 ms
Execution time : 3.906 ms