explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ngv8

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 4,965.916 ↑ 1.0 1 1

Limit (cost=318,256.12..318,256.13 rows=1 width=40) (actual time=4,965.882..4,965.916 rows=1 loops=1)

2. 38.330 4,965.880 ↑ 1.0 1 1

Aggregate (cost=318,256.12..318,256.13 rows=1 width=40) (actual time=4,965.880..4,965.880 rows=1 loops=1)

3. 296.218 4,927.550 ↓ 1.2 65,487 1

Unique (cost=313,426.59..316,267.49 rows=56,818 width=3,293) (actual time=4,589.587..4,927.550 rows=65,487 loops=1)

4. 610.485 4,631.332 ↓ 1.2 65,866 1

Sort (cost=313,426.59..313,568.64 rows=56,818 width=3,293) (actual time=4,589.573..4,631.332 rows=65,866 loops=1)

  • Sort Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html, snippets.encrypted_secret_token, snippets.encrypted_secret_token_iv, snippets.secret, snippets.repository_storage
  • Sort Method: external merge Disk: 69560kB
5. 6.493 4,020.847 ↓ 1.2 65,866 1

Append (cost=0.42..153,507.41 rows=56,818 width=3,293) (actual time=105.879..4,020.847 rows=65,866 loops=1)

6. 2,089.007 2,089.007 ↓ 1.2 58,244 1

Index Scan using index_snippets_on_project_id_and_visibility_level on snippets (cost=0.42..70,109.36 rows=48,667 width=2,105) (actual time=105.877..2,089.007 rows=58,244 loops=1)

  • Index Cond: (project_id IS NULL)
  • Filter: ((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 1562869))
  • Rows Removed by Filter: 104528
7. 5.421 1,876.931 ↑ 1.2 6,937 1

Nested Loop (cost=1.69..82,256.94 rows=8,148 width=2,105) (actual time=1.101..1,876.931 rows=6,937 loops=1)

8. 115.571 1,777.546 ↑ 1.4 7,228 1

Merge Join (cost=1.26..76,084.21 rows=9,909 width=2,109) (actual time=0.935..1,777.546 rows=7,228 loops=1)

  • Merge Cond: (snippets_1.project_id = projects.id)
9. 403.543 403.543 ↑ 5.3 13,402 1

Index Scan using index_snippets_on_project_id_and_visibility_level on snippets snippets_1 (cost=0.42..72,251.15 rows=71,577 width=2,105) (actual time=0.158..403.543 rows=13,402 loops=1)

  • Filter: ((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 1562869))
  • Rows Removed by Filter: 66120
10. 1,258.432 1,258.432 ↓ 1.0 1,670,353 1

Index Only Scan using index_projects_on_id_partial_for_visibility on projects (cost=0.43..48,694.92 rows=1,669,691 width=4) (actual time=0.227..1,258.432 rows=1,670,353 loops=1)

  • Heap Fetches: 144267
11. 93.964 93.964 ↑ 1.0 1 7,228

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.61 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=7,228)

  • Index Cond: (project_id = projects.id)
  • Filter: (snippets_access_level = ANY ('{20,30}'::integer[]))
  • Rows Removed by Filter: 0
12. 0.695 48.416 ↓ 228.3 685 1

Nested Loop (cost=72.25..572.93 rows=3 width=2,105) (actual time=6.999..48.416 rows=685 loops=1)

13. 0.000 44.977 ↓ 171.5 686 1

Nested Loop (cost=71.81..570.98 rows=4 width=2,113) (actual time=6.760..44.977 rows=686 loops=1)

14. 0.392 33.890 ↓ 11.0 2,227 1

Nested Loop (cost=71.39..473.90 rows=202 width=8) (actual time=6.063..33.890 rows=2,227 loops=1)

15. 1.639 6.774 ↓ 11.0 2,227 1

HashAggregate (cost=70.96..72.98 rows=202 width=4) (actual time=5.883..6.774 rows=2,227 loops=1)

  • Group Key: project_authorizations.project_id
16. 5.135 5.135 ↓ 11.0 2,227 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..70.45 rows=202 width=4) (actual time=0.167..5.135 rows=2,227 loops=1)

  • Index Cond: (user_id = 1562869)
  • Heap Fetches: 559
17. 26.724 26.724 ↑ 1.0 1 2,227

Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..1.97 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=2,227)

  • Index Cond: (id = project_authorizations.project_id)
  • Heap Fetches: 344
18. 11.135 11.135 ↓ 0.0 0 2,227

Index Scan using index_snippets_on_project_id_and_visibility_level on snippets snippets_2 (cost=0.42..0.46 rows=2 width=2,105) (actual time=0.004..0.005 rows=0 loops=2,227)

  • Index Cond: (project_id = projects_1.id)
19. 2.744 2.744 ↑ 1.0 1 686

Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.43..0.48 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=686)

  • Index Cond: (project_id = projects_1.id)
  • Filter: (snippets_access_level = ANY ('{20,30,10}'::integer[]))
  • Rows Removed by Filter: 0
Planning time : 24.516 ms
Execution time : 4,985.644 ms