explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FeeB : Optimization for: Optimization for: plan #BI1N; plan #mHI

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.005 629.208 ↑ 1.0 20 1

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

  • Buffers: shared hit=137812
2. 13.315 629.203 ↑ 2,007.5 20 1

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

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

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

  • Buffers: shared hit=137809
4. 295.375 546.883 ↓ 1.2 48,195 1

Sort (cost=279,341.41..279,441.79 rows=40,151 width=1,744) (actual time=483.558..546.883 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.869 251.508 ↓ 1.2 48,195 1

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

  • Buffers: shared hit=131218
6. 96.099 96.099 ↓ 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.035..96.099 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. 2.170 149.540 ↑ 1.3 3,915 1

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

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

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

  • Buffers: shared hit=69487
9. 73.712 73.712 ↓ 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.029..73.712 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. 24.384 24.384 ↑ 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.006..0.006 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 : 5.859 ms
Execution time : 657.755 ms