explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BI1N

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 10,677.535 ↑ 1.0 20 1

Limit (cost=282,417.36..282,417.41 rows=20 width=1,744) (actual time=10,677.523..10,677.535 rows=20 loops=1)

  • Buffers: shared dirtied=118 hit=128793 read=9039
2. 14.383 10,677.528 ↑ 2,007.5 20 1

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

  • Sort Key: snippets.created_at DESC
  • Sort Method: top-N heapsort Memory: 43kB
  • Buffers: shared dirtied=118 hit=128793 read=9039
3. 1,839.193 10,663.145 ↓ 1.2 48,195 1

Unique (cost=279,341.41..280,947.45 rows=40,151 width=1,744) (actual time=8,744.226..10,663.145 rows=48,195 loops=1)

  • Buffers: shared dirtied=118 hit=128790 read=9039
4. 366.176 8,823.952 ↓ 1.2 48,195 1

Sort (cost=279,341.41..279,441.79 rows=40,151 width=1,744) (actual time=8,744.223..8,823.952 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 dirtied=117 hit=123799 read=7445
5. 7.359 8,457.776 ↓ 1.2 48,195 1

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

  • Buffers: shared dirtied=117 hit=123793 read=7445
6. 393.253 393.253 ↓ 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=11.471..393.253 rows=44,280 loops=1)

  • Index Cond: (snippets.visibility_level = 20)
  • Filter: (snippets.project_id IS NULL)
  • Rows Removed by Filter: 6616
  • Buffers: shared dirtied=29 hit=45157 read=299
7. 10.231 8,057.164 ↑ 1.3 3,915 1

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

  • Buffers: shared dirtied=88 hit=78636 read=7146
8. 42.508 4,535.637 ↑ 1.6 4,064 1

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

  • Buffers: shared dirtied=57 hit=65782 read=3721
9. 116.073 116.073 ↓ 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..116.073 rows=50,896 loops=1)

  • Index Cond: (snippets_1.visibility_level = 20)
  • Buffers: shared hit=45456
10. 4,377.056 4,377.056 ↓ 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.086..0.086 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 dirtied=57 hit=20326 read=3721
11. 3,511.296 3,511.296 ↑ 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.860..0.864 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 dirtied=31 hit=12854 read=3425
Planning time : 5.979 ms