explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mHI : Optimization for: plan #BI1N

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 559.641 ↑ 1.0 20 1

Limit (cost=282,417.36..282,417.41 rows=20 width=1,744) (actual time=559.635..559.641 rows=20 loops=1)

  • Buffers: shared hit=137812
2. 11.942 559.637 ↑ 2,007.5 20 1

Sort (cost=282,417.36..282,517.74 rows=40,151 width=1,744) (actual time=559.633..559.637 rows=20 loops=1)

  • Sort Key: snippets.created_at DESC
  • Sort Method: top-N heapsort Memory: 43kB
  • Buffers: shared hit=137812
3. 64.071 547.695 ↓ 1.2 48,195 1

Unique (cost=279,341.41..280,947.45 rows=40,151 width=1,744) (actual time=432.688..547.695 rows=48,195 loops=1)

  • Buffers: shared hit=137809
4. 273.945 483.624 ↓ 1.2 48,195 1

Sort (cost=279,341.41..279,441.79 rows=40,151 width=1,744) (actual time=432.687..483.624 rows=48,195 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
  • Sort Method: external merge Disk: 52528kB
  • Buffers: shared hit=131224
5. 5.838 209.679 ↓ 1.2 48,195 1

Append (cost=0.42..215,609.23 rows=40,151 width=1,744) (actual time=0.039..209.679 rows=48,195 loops=1)

  • Buffers: shared hit=131218
6. 85.965 85.965 ↓ 1.3 44,280 1

Index Scan using index_snippets_on_visibility_level on public.snippets (cost=0.42..41,234.71 rows=35,200 width=1,083) (actual time=0.038..85.965 rows=44,280 loops=1)

  • Index Cond: (snippets.visibility_level = 20)
  • Filter: (snippets.project_id IS NULL)
  • Rows Removed by Filter: 6616
  • Buffers: shared hit=45456
7. 0.028 117.876 ↑ 1.3 3,915 1

Nested Loop (cost=1.28..173,973.01 rows=4,951 width=1,083) (actual time=0.181..117.876 rows=3,915 loops=1)

  • Buffers: shared hit=85762
8. 0.000 97.528 ↑ 1.6 4,064 1

Nested Loop (cost=0.85..169,520.82 rows=6,324 width=1,087) (actual time=0.160..97.528 rows=4,064 loops=1)

  • Buffers: shared hit=69487
9. 56.809 56.809 ↓ 1.0 50,896 1

Index Scan using index_snippets_on_visibility_level on public.snippets snippets_1 (cost=0.42..41,234.71 rows=50,868 width=1,083) (actual time=0.027..56.809 rows=50,896 loops=1)

  • Index Cond: (snippets_1.visibility_level = 20)
  • Buffers: shared hit=45456
10. 50.896 50.896 ↓ 0.0 0 50,896

Index Scan using index_projects_on_id_partial_for_visibility on public.projects (cost=0.43..2.51 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=50,896)

  • Index Cond: (projects.id = snippets_1.project_id)
  • Filter: (projects.visibility_level = 20)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=24031
11. 20.320 20.320 ↑ 1.0 1 4,064

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.69 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=4,064)

  • Index Cond: (project_features.project_id = projects.id)
  • Filter: (project_features.snippets_access_level = ANY ('{20,30}'::integer[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=16275
Planning time : 6.101 ms
Execution time : 585.874 ms