explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rWr3

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 377.609 ↓ 0.0 0 1

Append (cost=1,321.96..1,562.25 rows=8 width=50) (actual time=377.609..377.609 rows=0 loops=1)

  • Buffers: shared hit=67 read=225 dirtied=8
  • I/O Timings: read=361.879
2.          

CTE user_events_in_range

3. 314.807 314.807 ↑ 3.0 226 1

Index Scan using index_events_on_author_id_and_created_at on public.events events_7 (cost=0.57..1,250.61 rows=675 width=27) (actual time=14.737..314.807 rows=226 loops=1)

  • Index Cond: ((events_7.author_id = 3,614,848) AND (events_7.created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (events_7.created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone))
  • Filter: ((events_7.action = 5) OR ((events_7.action = 1) AND ((events_7.target_type)::text = ANY ('{MergeRequest,Issue,DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_7.action = 3) AND ((events_7.target_type)::text = ANY ('{MergeRequest,Issue}'::text[]))) OR ((events_7.action = 7) AND ((events_7.target_type)::text = 'MergeRequest'::text)) OR ((events_7.action = 2) AND ((events_7.target_type)::text = ANY ('{DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_7.action = 6) AND ((events_7.target_type)::text = 'Note'::text)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=18 read=208 dirtied=7
  • I/O Timings: read=301.287
4. 0.003 377.077 ↓ 0.0 0 1

Aggregate (cost=71.35..71.40 rows=2 width=50) (actual time=377.076..377.077 rows=0 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, events.date
  • Buffers: shared hit=67 read=225 dirtied=8
  • I/O Timings: read=361.879
5. 0.016 377.074 ↓ 0.0 0 1

Sort (cost=71.35..71.35 rows=2 width=46) (actual time=377.074..377.074 rows=0 loops=1)

  • Sort Key: events.project_id, events.target_type, events.date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=67 read=225 dirtied=8
  • I/O Timings: read=361.879
6. 0.294 377.058 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.87..71.34 rows=2 width=46) (actual time=377.058..377.058 rows=0 loops=1)

  • Buffers: shared hit=67 read=225 dirtied=8
  • I/O Timings: read=361.879
7. 315.465 315.465 ↓ 46.3 139 1

CTE Scan on user_events_in_range events (cost=0.00..15.19 rows=3 width=46) (actual time=14.742..315.465 rows=139 loops=1)

  • Filter: (events.action = 5)
  • Rows Removed by Filter: 87
  • Buffers: shared hit=18 read=208 dirtied=7
  • I/O Timings: read=301.287
8. 0.177 61.299 ↑ 1.7 6 139

Materialize (cost=0.87..55.72 rows=10 width=4) (actual time=0.195..0.441 rows=6 loops=139)

  • Buffers: shared hit=49 read=17 dirtied=1
  • I/O Timings: read=60.592
9. 0.043 61.122 ↑ 1.7 6 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=27.066..61.122 rows=6 loops=1)

  • Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=49 read=17 dirtied=1
  • I/O Timings: read=60.592
10. 37.175 37.175 ↑ 1.7 6 1

Index Only Scan using projects_pkey on public.projects (cost=0.43..21.02 rows=10 width=4) (actual time=21.307..37.175 rows=6 loops=1)

  • Index Cond: (projects.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[]))
  • Heap Fetches: 3
  • Buffers: shared hit=32 read=10 dirtied=1
  • I/O Timings: read=36.801
11. 23.904 23.904 ↑ 1.0 1 6

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..3.45 rows=1 width=8) (actual time=3.984..3.984 rows=1 loops=6)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=17 read=7
  • I/O Timings: read=23.791
12. 0.002 0.077 ↓ 0.0 0 1

Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=0.077..0.077 rows=0 loops=1)

  • Group Key: events_1.project_id, events_1.target_type, events_1.action, events_1.date
13. 0.026 0.075 ↓ 0.0 0 1

Sort (cost=41.32..41.33 rows=1 width=46) (actual time=0.075..0.075 rows=0 loops=1)

  • Sort Key: events_1.project_id, events_1.action, events_1.date
  • Sort Method: quicksort Memory: 25kB
14. 0.001 0.049 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=0.048..0.049 rows=0 loops=1)

15. 0.048 0.048 ↓ 0.0 0 1

CTE Scan on user_events_in_range events_1 (cost=0.00..16.88 rows=1 width=46) (actual time=0.048..0.048 rows=0 loops=1)

  • Filter: ((events_1.action = ANY ('{1,3}'::integer[])) AND ((events_1.target_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 226
16. 0.000 0.000 ↓ 0.0 0 0

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Filter: ((project_features_1.issues_access_level > 0) OR (project_features_1.issues_access_level IS NULL))
  • Rows Removed by Filter: 0
17. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using projects_pkey on public.projects projects_1 (cost=0.43..20.96 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((projects_1.id = events_1.project_id) AND (projects_1.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_project_features_on_project_id on public.project_features project_features_1 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_1.id = project_features_1.project_id)
19. 0.001 0.053 ↓ 0.0 0 1

Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=0.053..0.053 rows=0 loops=1)

  • Group Key: events_2.project_id, events_2.target_type, events_2.action, events_2.date
20. 0.014 0.052 ↓ 0.0 0 1

Sort (cost=41.32..41.33 rows=1 width=46) (actual time=0.052..0.052 rows=0 loops=1)

  • Sort Key: events_2.project_id, events_2.action, events_2.date
  • Sort Method: quicksort Memory: 25kB
21. 0.001 0.038 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=0.038..0.038 rows=0 loops=1)

22. 0.037 0.037 ↓ 0.0 0 1

CTE Scan on user_events_in_range events_2 (cost=0.00..16.88 rows=1 width=46) (actual time=0.037..0.037 rows=0 loops=1)

  • Filter: ((events_2.action = ANY ('{1,2}'::integer[])) AND ((events_2.target_type)::text = 'WikiPage::Meta'::text))
  • Rows Removed by Filter: 226
23. 0.000 0.000 ↓ 0.0 0 0

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Filter: ((project_features_2.wiki_access_level > 0) OR (project_features_2.wiki_access_level IS NULL))
  • Rows Removed by Filter: 0
24. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using projects_pkey on public.projects projects_2 (cost=0.43..20.96 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((projects_2.id = events_2.project_id) AND (projects_2.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_project_features_on_project_id on public.project_features project_features_2 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_2.id = project_features_2.project_id)
26. 0.001 0.261 ↓ 0.0 0 1

Aggregate (cost=42.16..42.19 rows=1 width=50) (actual time=0.261..0.261 rows=0 loops=1)

  • Group Key: events_3.project_id, events_3.target_type, events_3.action, events_3.date
27. 0.017 0.260 ↓ 0.0 0 1

Sort (cost=42.16..42.17 rows=1 width=46) (actual time=0.260..0.260 rows=0 loops=1)

  • Sort Key: events_3.project_id, events_3.action, events_3.date
  • Sort Method: quicksort Memory: 25kB
28. 0.018 0.243 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.87..42.15 rows=1 width=46) (actual time=0.243..0.243 rows=0 loops=1)

29. 0.051 0.051 ↓ 87.0 87 1

CTE Scan on user_events_in_range events_3 (cost=0.00..17.72 rows=1 width=46) (actual time=0.003..0.051 rows=87 loops=1)

  • Filter: (((events_3.target_type)::text = 'MergeRequest'::text) AND (events_3.action = ANY ('{7,1,3}'::integer[])))
  • Rows Removed by Filter: 139
30. 0.087 0.174 ↓ 0.0 0 87

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=87)

  • Filter: ((project_features_3.merge_requests_access_level > 0) OR (project_features_3.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
31. 0.087 0.087 ↓ 0.0 0 87

Index Only Scan using projects_pkey on public.projects projects_3 (cost=0.43..20.96 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=87)

  • Index Cond: ((projects_3.id = events_3.project_id) AND (projects_3.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_project_features_on_project_id on public.project_features project_features_3 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_3.id = project_features_3.project_id)
33. 0.001 0.048 ↓ 0.0 0 1

Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=0.048..0.048 rows=0 loops=1)

  • Group Key: events_4.project_id, events_4.target_type, events_4.action, events_4.date
34. 0.010 0.047 ↓ 0.0 0 1

Sort (cost=41.32..41.33 rows=1 width=46) (actual time=0.047..0.047 rows=0 loops=1)

  • Sort Key: events_4.project_id, events_4.action, events_4.date
  • Sort Method: quicksort Memory: 25kB
35. 0.000 0.037 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=0.037..0.037 rows=0 loops=1)

36. 0.037 0.037 ↓ 0.0 0 1

CTE Scan on user_events_in_range events_4 (cost=0.00..16.88 rows=1 width=46) (actual time=0.037..0.037 rows=0 loops=1)

  • Filter: ((events_4.action = ANY ('{1,2}'::integer[])) AND ((events_4.target_type)::text = 'DesignManagement::Design'::text))
  • Rows Removed by Filter: 226
37. 0.000 0.000 ↓ 0.0 0 0

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Filter: ((project_features_4.issues_access_level > 0) OR (project_features_4.issues_access_level IS NULL))
  • Rows Removed by Filter: 0
38. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using projects_pkey on public.projects projects_4 (cost=0.43..20.96 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((projects_4.id = events_4.project_id) AND (projects_4.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
  • Heap Fetches: 0
39. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_project_features_on_project_id on public.project_features project_features_4 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_4.id = project_features_4.project_id)
40. 0.001 0.041 ↓ 0.0 0 1

Subquery Scan on *SELECT* 6 (cost=44.91..44.95 rows=1 width=50) (actual time=0.041..0.041 rows=0 loops=1)

41. 0.001 0.040 ↓ 0.0 0 1

Aggregate (cost=44.91..44.94 rows=1 width=60) (actual time=0.040..0.040 rows=0 loops=1)

  • Group Key: events_5.project_id, events_5.target_type, events_5.action, events_5.date, notes.noteable_type
42. 0.010 0.039 ↓ 0.0 0 1

Sort (cost=44.91..44.92 rows=1 width=56) (actual time=0.039..0.039 rows=0 loops=1)

  • Sort Key: events_5.project_id, events_5.date
  • Sort Method: quicksort Memory: 25kB
43. 0.000 0.029 ↓ 0.0 0 1

Nested Loop (cost=1.44..44.90 rows=1 width=56) (actual time=0.029..0.029 rows=0 loops=1)

44. 0.001 0.029 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=50) (actual time=0.029..0.029 rows=0 loops=1)

45. 0.028 0.028 ↓ 0.0 0 1

CTE Scan on user_events_in_range events_5 (cost=0.00..16.88 rows=1 width=50) (actual time=0.028..0.028 rows=0 loops=1)

  • Filter: (((events_5.target_type)::text = 'Note'::text) AND (events_5.action = 6))
  • Rows Removed by Filter: 226
46. 0.000 0.000 ↓ 0.0 0 0

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Filter: ((project_features_5.merge_requests_access_level > 0) OR (project_features_5.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
47. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using projects_pkey on public.projects projects_5 (cost=0.43..20.96 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((projects_5.id = events_5.project_id) AND (projects_5.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
  • Heap Fetches: 0
48. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_project_features_on_project_id on public.project_features project_features_5 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects_5.id = project_features_5.project_id)
49. 0.000 0.000 ↓ 0.0 0 0

Index Scan using notes_pkey on public.notes (cost=0.57..3.59 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (notes.id = events_5.target_id)
  • Filter: ((notes.noteable_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 0
50. 0.000 0.045 ↓ 0.0 0 1

Subquery Scan on *SELECT* 7 (cost=28.92..28.96 rows=1 width=50) (actual time=0.045..0.045 rows=0 loops=1)

51. 0.001 0.045 ↓ 0.0 0 1

Aggregate (cost=28.92..28.95 rows=1 width=60) (actual time=0.045..0.045 rows=0 loops=1)

  • Group Key: events_6.project_id, events_6.target_type, events_6.action, events_6.date, notes_1.noteable_type
52. 0.015 0.044 ↓ 0.0 0 1

Sort (cost=28.92..28.92 rows=1 width=56) (actual time=0.044..0.044 rows=0 loops=1)

  • Sort Key: events_6.project_id, events_6.date, notes_1.noteable_type
  • Sort Method: quicksort Memory: 25kB
53. 0.001 0.029 ↓ 0.0 0 1

Nested Loop (cost=0.57..28.91 rows=1 width=56) (actual time=0.029..0.029 rows=0 loops=1)

54. 0.028 0.028 ↓ 0.0 0 1

CTE Scan on user_events_in_range events_6 (cost=0.00..25.31 rows=1 width=50) (actual time=0.028..0.028 rows=0 loops=1)

  • Filter: (((events_6.target_type)::text = 'Note'::text) AND (events_6.action = 6) AND (events_6.project_id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
  • Rows Removed by Filter: 226
55. 0.000 0.000 ↓ 0.0 0 0

Index Scan using notes_pkey on public.notes notes_1 (cost=0.57..3.59 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (notes_1.id = events_6.target_id)
  • Filter: ((notes_1.noteable_type)::text <> 'MergeRequest'::text)
  • Rows Removed by Filter: 0