explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EOY9

Settings
# exclusive inclusive rows x rows loops node
1. 0.112 63.803 ↓ 85.6 685 1

Append (cost=1,306.85..1,585.95 rows=8 width=50) (actual time=27.662..63.803 rows=685 loops=1)

  • Buffers: shared hit=23,271
2.          

CTE user_events_in_range

3. 18.667 18.667 ↓ 6.9 6,318 1

Index Scan using index_events_on_author_id_and_created_at on public.events events_7 (cost=0.57..1,229.87 rows=912 width=27) (actual time=0.053..18.667 rows=6,318 loops=1)

  • Index Cond: ((events_7.author_id = 3,614,858) AND (events_7.created_at >= '2019-09-02 00:00:00+00'::timestamp with time zone) AND (events_7.created_at <= '2020-09-02 23:59:59.999999+00'::timestamp with time zone))
  • Buffers: shared hit=6,320
4. 0.955 28.812 ↓ 114.0 228 1

Aggregate (cost=76.98..77.03 rows=2 width=50) (actual time=27.661..28.812 rows=228 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, events.date
  • Buffers: shared hit=6,386
5. 1.155 27.857 ↓ 948.5 1,897 1

Sort (cost=76.98..76.98 rows=2 width=46) (actual time=27.651..27.857 rows=1,897 loops=1)

  • Sort Key: events.project_id, events.target_type, events.date
  • Sort Method: quicksort Memory: 137kB
  • Buffers: shared hit=6,386
6. 1.833 26.702 ↓ 948.5 1,897 1

Nested Loop Semi Join (cost=0.87..76.97 rows=2 width=46) (actual time=0.254..26.702 rows=1,897 loops=1)

  • Buffers: shared hit=6,383
7. 22.787 22.787 ↓ 416.4 2,082 1

CTE Scan on user_events_in_range events (cost=0.00..20.52 rows=5 width=46) (actual time=0.084..22.787 rows=2,082 loops=1)

  • Filter: (events.action = 5)
  • Rows Removed by Filter: 4,236
  • Buffers: shared hit=6,320
8. 1.923 2.082 ↑ 1.7 6 2,082

Materialize (cost=0.87..55.72 rows=10 width=4) (actual time=0.000..0.001 rows=6 loops=2,082)

  • Buffers: shared hit=63
9. 0.015 0.159 ↑ 1.7 6 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.072..0.159 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=63
10. 0.102 0.102 ↑ 1.7 6 1

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

  • Index Cond: (projects.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[]))
  • Heap Fetches: 1
  • Buffers: shared hit=39
11. 0.042 0.042 ↑ 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=0.007..0.007 rows=1 loops=6)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=24
12. 0.073 2.299 ↓ 90.0 90 1

Aggregate (cost=47.24..47.27 rows=1 width=50) (actual time=2.220..2.299 rows=90 loops=1)

  • Group Key: events_1.project_id, events_1.target_type, events_1.action, events_1.date
  • Buffers: shared hit=1,113
13. 0.166 2.226 ↓ 139.0 139 1

Sort (cost=47.24..47.25 rows=1 width=46) (actual time=2.214..2.226 rows=139 loops=1)

  • Sort Key: events_1.project_id, events_1.action, events_1.date
  • Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=1,113
14. 0.127 2.060 ↓ 139.0 139 1

Nested Loop Semi Join (cost=0.87..47.23 rows=1 width=46) (actual time=0.100..2.060 rows=139 loops=1)

  • Buffers: shared hit=1,113
15. 1.113 1.113 ↓ 164.0 164 1

CTE Scan on user_events_in_range events_1 (cost=0.00..22.80 rows=1 width=46) (actual time=0.016..1.113 rows=164 loops=1)

  • Filter: ((events_1.action = ANY ('{1,3}'::integer[])) AND ((events_1.target_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 6,154
16. 0.214 0.820 ↑ 1.0 1 164

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

  • Filter: ((project_features_1.issues_access_level > 0) OR (project_features_1.issues_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,113
17. 0.328 0.328 ↑ 1.0 1 164

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

  • 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: 139
  • Buffers: shared hit=557
18. 0.278 0.278 ↑ 1.0 1 139

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.002..0.002 rows=1 loops=139)

  • Index Cond: (projects_1.id = project_features_1.project_id)
  • Buffers: shared hit=556
19. 0.001 2.853 ↓ 0.0 0 1

Aggregate (cost=47.24..47.27 rows=1 width=50) (actual time=2.853..2.853 rows=0 loops=1)

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

Sort (cost=47.24..47.25 rows=1 width=46) (actual time=2.852..2.852 rows=0 loops=1)

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

Nested Loop Semi Join (cost=0.87..47.23 rows=1 width=46) (actual time=2.837..2.838 rows=0 loops=1)

22. 1.188 1.188 ↓ 701.0 701 1

CTE Scan on user_events_in_range events_2 (cost=0.00..22.80 rows=1 width=46) (actual time=0.350..1.188 rows=701 loops=1)

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

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

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

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

  • 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.069 2.254 ↓ 92.0 92 1

Aggregate (cost=48.38..48.41 rows=1 width=50) (actual time=2.179..2.254 rows=92 loops=1)

  • Group Key: events_3.project_id, events_3.target_type, events_3.action, events_3.date
  • Buffers: shared hit=1,017
27. 0.190 2.185 ↓ 127.0 127 1

Sort (cost=48.38..48.39 rows=1 width=46) (actual time=2.173..2.185 rows=127 loops=1)

  • Sort Key: events_3.project_id, events_3.action, events_3.date
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=1,017
28. 0.033 1.995 ↓ 127.0 127 1

Nested Loop Semi Join (cost=0.87..48.38 rows=1 width=46) (actual time=0.046..1.995 rows=127 loops=1)

  • Buffers: shared hit=1,017
29. 0.989 0.989 ↓ 139.0 139 1

CTE Scan on user_events_in_range events_3 (cost=0.00..23.94 rows=1 width=46) (actual time=0.009..0.989 rows=139 loops=1)

  • Filter: (((events_3.target_type)::text = 'MergeRequest'::text) AND (events_3.action = ANY ('{7,1,3}'::integer[])))
  • Rows Removed by Filter: 6,179
30. 0.302 0.973 ↑ 1.0 1 139

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

  • Filter: ((project_features_3.merge_requests_access_level > 0) OR (project_features_3.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,017
31. 0.417 0.417 ↑ 1.0 1 139

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

  • 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: 127
  • Buffers: shared hit=509
32. 0.254 0.254 ↑ 1.0 1 127

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.002..0.002 rows=1 loops=127)

  • Index Cond: (projects_3.id = project_features_3.project_id)
  • Buffers: shared hit=508
33. 0.001 1.265 ↓ 0.0 0 1

Aggregate (cost=47.24..47.27 rows=1 width=50) (actual time=1.265..1.265 rows=0 loops=1)

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

Sort (cost=47.24..47.25 rows=1 width=46) (actual time=1.264..1.264 rows=0 loops=1)

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

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

36. 1.220 1.220 ↑ 1.0 1 1

CTE Scan on user_events_in_range events_4 (cost=0.00..22.80 rows=1 width=46) (actual time=0.838..1.220 rows=1 loops=1)

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

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

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

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

  • 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.050 19.430 ↓ 185.0 185 1

Subquery Scan on *SELECT* 6 (cost=50.84..50.88 rows=1 width=50) (actual time=18.935..19.430 rows=185 loops=1)

  • Buffers: shared hit=10,658
41. 0.401 19.380 ↓ 185.0 185 1

Aggregate (cost=50.84..50.87 rows=1 width=60) (actual time=18.933..19.380 rows=185 loops=1)

  • Group Key: events_5.project_id, events_5.target_type, events_5.action, events_5.date, notes.noteable_type
  • Buffers: shared hit=10,658
42. 0.527 18.979 ↓ 639.0 639 1

Sort (cost=50.84..50.84 rows=1 width=56) (actual time=18.921..18.979 rows=639 loops=1)

  • Sort Key: events_5.project_id, events_5.date
  • Sort Method: quicksort Memory: 74kB
  • Buffers: shared hit=10,658
43. 0.294 18.452 ↓ 639.0 639 1

Nested Loop (cost=1.44..50.83 rows=1 width=56) (actual time=0.083..18.452 rows=639 loops=1)

  • Buffers: shared hit=10,658
44. 0.596 8.318 ↓ 820.0 820 1

Nested Loop Semi Join (cost=0.87..47.23 rows=1 width=50) (actual time=0.038..8.318 rows=820 loops=1)

  • Buffers: shared hit=6,561
45. 1.499 1.499 ↓ 889.0 889 1

CTE Scan on user_events_in_range events_5 (cost=0.00..22.80 rows=1 width=50) (actual time=0.002..1.499 rows=889 loops=1)

  • Filter: (((events_5.target_type)::text = 'Note'::text) AND (events_5.action = 6))
  • Rows Removed by Filter: 5,429
46. 1.916 6.223 ↑ 1.0 1 889

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

  • Filter: ((project_features_5.merge_requests_access_level > 0) OR (project_features_5.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=6,561
47. 2.667 2.667 ↑ 1.0 1 889

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

  • 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: 820
  • Buffers: shared hit=3,281
48. 1.640 1.640 ↑ 1.0 1 820

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.002..0.002 rows=1 loops=820)

  • Index Cond: (projects_5.id = project_features_5.project_id)
  • Buffers: shared hit=3,280
49. 9.840 9.840 ↑ 1.0 1 820

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

  • Index Cond: (notes.id = events_5.target_id)
  • Filter: ((notes.noteable_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4,097
50. 0.022 6.778 ↓ 90.0 90 1

Subquery Scan on *SELECT* 7 (cost=37.80..37.84 rows=1 width=50) (actual time=6.636..6.778 rows=90 loops=1)

  • Buffers: shared hit=4,097
51. 0.117 6.756 ↓ 90.0 90 1

Aggregate (cost=37.80..37.83 rows=1 width=60) (actual time=6.635..6.756 rows=90 loops=1)

  • Group Key: events_6.project_id, events_6.target_type, events_6.action, events_6.date, notes_1.noteable_type
  • Buffers: shared hit=4,097
52. 0.151 6.639 ↓ 178.0 178 1

Sort (cost=37.80..37.81 rows=1 width=56) (actual time=6.625..6.639 rows=178 loops=1)

  • Sort Key: events_6.project_id, events_6.date, notes_1.noteable_type
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=4,097
53. 0.062 6.488 ↓ 178.0 178 1

Nested Loop (cost=0.57..37.79 rows=1 width=56) (actual time=0.049..6.488 rows=178 loops=1)

  • Buffers: shared hit=4,097
54. 1.506 1.506 ↓ 820.0 820 1

CTE Scan on user_events_in_range events_6 (cost=0.00..34.20 rows=1 width=50) (actual time=0.006..1.506 rows=820 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: 5,498
55. 4.920 4.920 ↓ 0.0 0 820

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

  • Index Cond: (notes_1.id = events_6.target_id)
  • Filter: ((notes_1.noteable_type)::text <> 'MergeRequest'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4,097