explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5mH7

Settings
# exclusive inclusive rows x rows loops node
1. 0.099 15,639.790 ↓ 85.6 685 1

Append (cost=1,306.85..1,585.95 rows=8 width=50) (actual time=10,957.262..15,639.790 rows=685 loops=1)

  • Buffers: shared hit=15,330 read=7,952 dirtied=115
  • I/O Timings: read=15,440.655
2.          

CTE user_events_in_range

3. 10,854.367 10,854.367 ↓ 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=13.463..10,854.367 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=443 read=5,877 dirtied=92
  • I/O Timings: read=10,780.335
4. 0.755 10,958.150 ↓ 114.0 228 1

Aggregate (cost=76.98..77.03 rows=2 width=50) (actual time=10,957.261..10,958.150 rows=228 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, events.date
  • Buffers: shared hit=498 read=5,896 dirtied=93
  • I/O Timings: read=10,847.271
5. 4.088 10,957.395 ↓ 948.5 1,897 1

Sort (cost=76.98..76.98 rows=2 width=46) (actual time=10,957.239..10,957.395 rows=1,897 loops=1)

  • Sort Key: events.project_id, events.target_type, events.date
  • Sort Method: quicksort Memory: 137kB
  • Buffers: shared hit=498 read=5,896 dirtied=93
  • I/O Timings: read=10,847.271
6. 8.364 10,953.307 ↓ 948.5 1,897 1

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

  • Buffers: shared hit=489 read=5,896 dirtied=93
  • I/O Timings: read=10,847.271
7. 10,876.237 10,876.237 ↓ 416.4 2,082 1

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

  • Filter: (events.action = 5)
  • Rows Removed by Filter: 4,236
  • Buffers: shared hit=443 read=5,877 dirtied=92
  • I/O Timings: read=10,780.335
8. 1.351 68.706 ↑ 1.7 6 2,082

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

  • Buffers: shared hit=46 read=19 dirtied=1
  • I/O Timings: read=66.936
9. 0.079 67.355 ↑ 1.7 6 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=10.408..67.355 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=46 read=19 dirtied=1
  • I/O Timings: read=66.936
10. 50.062 50.062 ↑ 1.7 6 1

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

  • Index Cond: (projects.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[]))
  • Heap Fetches: 2
  • Buffers: shared hit=29 read=12 dirtied=1
  • I/O Timings: read=49.836
11. 17.214 17.214 ↑ 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=2.869..2.869 rows=1 loops=6)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=17 read=7
  • I/O Timings: read=17.100
12. 0.071 2.407 ↓ 90.0 90 1

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

  • Group Key: events_1.project_id, events_1.target_type, events_1.action, events_1.date
  • Buffers: shared hit=1,116
13. 0.209 2.336 ↓ 139.0 139 1

Sort (cost=47.24..47.25 rows=1 width=46) (actual time=2.324..2.336 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,116
14. 0.164 2.127 ↓ 139.0 139 1

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

  • Buffers: shared hit=1,113
15. 1.143 1.143 ↓ 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.017..1.143 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.002 2.635 ↓ 0.0 0 1

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

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

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

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

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

22. 1.177 1.177 ↓ 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.387..1.177 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.050 ↓ 92.0 92 1

Aggregate (cost=48.38..48.41 rows=1 width=50) (actual time=1.978..2.050 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.182 1.981 ↓ 127.0 127 1

Sort (cost=48.38..48.39 rows=1 width=46) (actual time=1.971..1.981 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.058 1.799 ↓ 127.0 127 1

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

  • Buffers: shared hit=1,017
29. 0.907 0.907 ↓ 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.907 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.834 ↑ 1.0 1 139

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.006..0.006 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.278 0.278 ↑ 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.002..0.002 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.002 1.066 ↓ 0.0 0 1

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

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

Sort (cost=47.24..47.25 rows=1 width=46) (actual time=1.064..1.064 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.049 ↓ 0.0 0 1

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

36. 1.028 1.028 ↑ 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.693..1.028 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.001 0.018 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.018..0.018 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.017 0.017 ↓ 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.017..0.017 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.047 4,667.345 ↓ 185.0 185 1

Subquery Scan on *SELECT* 6 (cost=50.84..50.88 rows=1 width=50) (actual time=4,666.811..4,667.345 rows=185 loops=1)

  • Buffers: shared hit=8,602 read=2,056 dirtied=22
  • I/O Timings: read=4,593.384
41. 0.440 4,667.298 ↓ 185.0 185 1

Aggregate (cost=50.84..50.87 rows=1 width=60) (actual time=4,666.809..4,667.298 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=8,602 read=2,056 dirtied=22
  • I/O Timings: read=4,593.384
42. 2.352 4,666.858 ↓ 639.0 639 1

Sort (cost=50.84..50.84 rows=1 width=56) (actual time=4,666.800..4,666.858 rows=639 loops=1)

  • Sort Key: events_5.project_id, events_5.date
  • Sort Method: quicksort Memory: 74kB
  • Buffers: shared hit=8,602 read=2,056 dirtied=22
  • I/O Timings: read=4,593.384
43. 3.373 4,664.506 ↓ 639.0 639 1

Nested Loop (cost=1.44..50.83 rows=1 width=56) (actual time=21.095..4,664.506 rows=639 loops=1)

  • Buffers: shared hit=8,602 read=2,056 dirtied=22
  • I/O Timings: read=4,593.384
44. 2.661 37.153 ↓ 820.0 820 1

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

  • Buffers: shared hit=6,561
45. 4.266 4.266 ↓ 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..4.266 rows=889 loops=1)

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

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.034..0.034 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. 12.446 12.446 ↑ 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.014..0.014 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. 6.560 6.560 ↑ 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.008..0.008 rows=1 loops=820)

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

Index Scan using notes_pkey on public.notes (cost=0.57..3.59 rows=1 width=14) (actual time=5.639..5.639 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=2,041 read=2,056 dirtied=22
  • I/O Timings: read=4,593.384
50. 0.022 6.038 ↓ 90.0 90 1

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

  • Buffers: shared hit=4,097
51. 0.107 6.016 ↓ 90.0 90 1

Aggregate (cost=37.80..37.83 rows=1 width=60) (actual time=5.904..6.016 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.133 5.909 ↓ 178.0 178 1

Sort (cost=37.80..37.81 rows=1 width=56) (actual time=5.895..5.909 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.364 5.776 ↓ 178.0 178 1

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

  • Buffers: shared hit=4,097
54. 1.312 1.312 ↓ 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.312 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.100 4.100 ↓ 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.005..0.005 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