explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PBIK

Settings
# exclusive inclusive rows x rows loops node
1. 0.121 134.277 ↓ 85.0 680 1

Append (cost=1,293.51..1,570.97 rows=8 width=50) (actual time=76.055..134.277 rows=680 loops=1)

  • Buffers: shared hit=23,099 read=12 dirtied=1
  • I/O Timings: read=19.465
2.          

CTE user_events_in_range

3. 47.073 47.073 ↓ 6.9 6,265 1

Index Scan using index_events_on_author_id_and_created_at on public.events events_7 (cost=0.57..1,215.47 rows=902 width=27) (actual time=0.198..47.073 rows=6,265 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,267
4. 0.945 77.190 ↓ 113.5 227 1

Aggregate (cost=78.04..78.09 rows=2 width=50) (actual time=76.054..77.190 rows=227 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, events.date
  • Buffers: shared hit=6,320 read=12 dirtied=1
  • I/O Timings: read=19.465
5. 1.092 76.245 ↓ 938.5 1,877 1

Sort (cost=78.04..78.05 rows=2 width=46) (actual time=76.044..76.245 rows=1,877 loops=1)

  • Sort Key: events.project_id, events.target_type, events.date
  • Sort Method: quicksort Memory: 136kB
  • Buffers: shared hit=6,320 read=12 dirtied=1
  • I/O Timings: read=19.465
6. 2.952 75.153 ↓ 938.5 1,877 1

Nested Loop Semi Join (cost=0.87..78.03 rows=2 width=46) (actual time=16.711..75.153 rows=1,877 loops=1)

  • Buffers: shared hit=6,320 read=12 dirtied=1
  • I/O Timings: read=19.465
7. 51.581 51.581 ↓ 412.4 2,062 1

CTE Scan on user_events_in_range events (cost=0.00..20.29 rows=5 width=46) (actual time=0.281..51.581 rows=2,062 loops=1)

  • Filter: (events.action = 5)
  • Rows Removed by Filter: 4,203
  • Buffers: shared hit=6,267
8. 0.732 20.620 ↑ 1.7 6 2,062

Materialize (cost=0.87..57.01 rows=10 width=4) (actual time=0.002..0.010 rows=6 loops=2,062)

  • Buffers: shared hit=53 read=12 dirtied=1
  • I/O Timings: read=19.465
9. 0.023 19.888 ↑ 1.7 6 1

Nested Loop Left Join (cost=0.87..56.96 rows=10 width=4) (actual time=4.659..19.888 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=53 read=12 dirtied=1
  • I/O Timings: read=19.465
10. 19.643 19.643 ↑ 1.7 6 1

Index Only Scan using projects_pkey on public.projects (cost=0.43..22.31 rows=10 width=4) (actual time=4.642..19.643 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=19.465
11. 0.222 0.222 ↑ 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.037..0.037 rows=1 loops=6)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=24
12. 0.110 3.194 ↓ 89.0 89 1

Aggregate (cost=46.99..47.02 rows=1 width=50) (actual time=3.076..3.194 rows=89 loops=1)

  • Group Key: events_1.project_id, events_1.target_type, events_1.action, events_1.date
  • Buffers: shared hit=1,105
13. 0.272 3.084 ↓ 138.0 138 1

Sort (cost=46.99..47.00 rows=1 width=46) (actual time=3.067..3.084 rows=138 loops=1)

  • Sort Key: events_1.project_id, events_1.action, events_1.date
  • Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=1,105
14. 0.168 2.812 ↓ 138.0 138 1

Nested Loop Semi Join (cost=0.87..46.98 rows=1 width=46) (actual time=0.095..2.812 rows=138 loops=1)

  • Buffers: shared hit=1,105
15. 1.503 1.503 ↓ 163.0 163 1

CTE Scan on user_events_in_range events_1 (cost=0.00..22.55 rows=1 width=46) (actual time=0.016..1.503 rows=163 loops=1)

  • Filter: ((events_1.action = ANY ('{1,3}'::integer[])) AND ((events_1.target_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 6,102
16. 0.376 1.141 ↑ 1.0 1 163

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

  • 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,105
17. 0.489 0.489 ↑ 1.0 1 163

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

  • 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: 138
  • Buffers: shared hit=553
18. 0.276 0.276 ↑ 1.0 1 138

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=138)

  • Index Cond: (projects_1.id = project_features_1.project_id)
  • Buffers: shared hit=552
19. 0.002 4.247 ↓ 0.0 0 1

Aggregate (cost=46.99..47.02 rows=1 width=50) (actual time=4.247..4.247 rows=0 loops=1)

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

Sort (cost=46.99..47.00 rows=1 width=46) (actual time=4.245..4.245 rows=0 loops=1)

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

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

22. 1.927 1.927 ↓ 701.0 701 1

CTE Scan on user_events_in_range events_2 (cost=0.00..22.55 rows=1 width=46) (actual time=0.663..1.927 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,564
23. 1.402 2.103 ↓ 0.0 0 701

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.003..0.003 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.105 3.354 ↓ 91.0 91 1

Aggregate (cost=48.12..48.15 rows=1 width=50) (actual time=3.243..3.354 rows=91 loops=1)

  • Group Key: events_3.project_id, events_3.target_type, events_3.action, events_3.date
  • Buffers: shared hit=1,009
27. 0.306 3.249 ↓ 126.0 126 1

Sort (cost=48.12..48.13 rows=1 width=46) (actual time=3.232..3.249 rows=126 loops=1)

  • Sort Key: events_3.project_id, events_3.action, events_3.date
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=1,009
28. 0.063 2.943 ↓ 126.0 126 1

Nested Loop Semi Join (cost=0.87..48.11 rows=1 width=46) (actual time=0.074..2.943 rows=126 loops=1)

  • Buffers: shared hit=1,009
29. 1.500 1.500 ↓ 138.0 138 1

CTE Scan on user_events_in_range events_3 (cost=0.00..23.68 rows=1 width=46) (actual time=0.016..1.500 rows=138 loops=1)

  • Filter: (((events_3.target_type)::text = 'MergeRequest'::text) AND (events_3.action = ANY ('{7,1,3}'::integer[])))
  • Rows Removed by Filter: 6,127
30. 0.450 1.380 ↑ 1.0 1 138

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

  • 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,009
31. 0.552 0.552 ↑ 1.0 1 138

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

  • 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: 126
  • Buffers: shared hit=505
32. 0.378 0.378 ↑ 1.0 1 126

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.003..0.003 rows=1 loops=126)

  • Index Cond: (projects_3.id = project_features_3.project_id)
  • Buffers: shared hit=504
33. 0.002 1.761 ↓ 0.0 0 1

Aggregate (cost=46.99..47.02 rows=1 width=50) (actual time=1.761..1.761 rows=0 loops=1)

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

Sort (cost=46.99..47.00 rows=1 width=46) (actual time=1.759..1.759 rows=0 loops=1)

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

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

36. 1.698 1.698 ↑ 1.0 1 1

CTE Scan on user_events_in_range events_4 (cost=0.00..22.55 rows=1 width=46) (actual time=1.137..1.698 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,264
37. 0.002 0.026 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.026..0.026 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.024 0.024 ↓ 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.024..0.024 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 37.877 ↓ 184.0 184 1

Subquery Scan on *SELECT* 6 (cost=50.59..50.63 rows=1 width=50) (actual time=37.394..37.877 rows=184 loops=1)

  • Buffers: shared hit=10,593
41. 0.386 37.830 ↓ 184.0 184 1

Aggregate (cost=50.59..50.62 rows=1 width=60) (actual time=37.392..37.830 rows=184 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,593
42. 0.667 37.444 ↓ 636.0 636 1

Sort (cost=50.59..50.59 rows=1 width=56) (actual time=37.382..37.444 rows=636 loops=1)

  • Sort Key: events_5.project_id, events_5.date
  • Sort Method: quicksort Memory: 74kB
  • Buffers: shared hit=10,593
43. 0.814 36.777 ↓ 636.0 636 1

Nested Loop (cost=1.44..50.58 rows=1 width=56) (actual time=0.178..36.777 rows=636 loops=1)

  • Buffers: shared hit=10,593
44. 0.691 11.513 ↓ 815.0 815 1

Nested Loop Semi Join (cost=0.87..46.98 rows=1 width=50) (actual time=0.066..11.513 rows=815 loops=1)

  • Buffers: shared hit=6,521
45. 1.992 1.992 ↓ 883.0 883 1

CTE Scan on user_events_in_range events_5 (cost=0.00..22.55 rows=1 width=50) (actual time=0.004..1.992 rows=883 loops=1)

  • Filter: (((events_5.target_type)::text = 'Note'::text) AND (events_5.action = 6))
  • Rows Removed by Filter: 5,382
46. 2.853 8.830 ↑ 1.0 1 883

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

  • 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,521
47. 3.532 3.532 ↑ 1.0 1 883

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

  • 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: 815
  • Buffers: shared hit=3,261
48. 2.445 2.445 ↑ 1.0 1 815

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.003..0.003 rows=1 loops=815)

  • Index Cond: (projects_5.id = project_features_5.project_id)
  • Buffers: shared hit=3,260
49. 24.450 24.450 ↑ 1.0 1 815

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

  • Index Cond: (notes.id = events_5.target_id)
  • Filter: ((notes.noteable_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4,072
50. 0.022 6.533 ↓ 89.0 89 1

Subquery Scan on *SELECT* 7 (cost=37.43..37.47 rows=1 width=50) (actual time=6.399..6.533 rows=89 loops=1)

  • Buffers: shared hit=4,072
51. 0.110 6.511 ↓ 89.0 89 1

Aggregate (cost=37.43..37.46 rows=1 width=60) (actual time=6.397..6.511 rows=89 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,072
52. 0.142 6.401 ↓ 176.0 176 1

Sort (cost=37.43..37.43 rows=1 width=56) (actual time=6.386..6.401 rows=176 loops=1)

  • Sort Key: events_6.project_id, events_6.date, notes_1.noteable_type
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=4,072
53. 0.000 6.259 ↓ 176.0 176 1

Nested Loop (cost=0.57..37.42 rows=1 width=56) (actual time=0.042..6.259 rows=176 loops=1)

  • Buffers: shared hit=4,072
54. 1.398 1.398 ↓ 815.0 815 1

CTE Scan on user_events_in_range events_6 (cost=0.00..33.83 rows=1 width=50) (actual time=0.005..1.398 rows=815 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,450
55. 4.890 4.890 ↓ 0.0 0 815

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=815)

  • 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,072