explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xiMA

Settings
# exclusive inclusive rows x rows loops node
1. 0.267 1,252.886 ↓ 30.0 240 1

Aggregate (cost=1,562.37..1,562.55 rows=8 width=8) (actual time=1,252.572..1,252.886 rows=240 loops=1)

  • Group Key: "*SELECT* 1".date
  • Buffers: shared hit=15,317 read=7,926 dirtied=118
  • I/O Timings: read=1,160.433
2.          

CTE user_events_in_range

3. 742.475 742.475 ↓ 5.9 3,970 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=15.142..742.475 rows=3,970 loops=1)

  • Index Cond: ((events_7.author_id = 3,614,858) 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: 2,328
  • Buffers: shared hit=443 read=5,857 dirtied=80
  • I/O Timings: read=707.939
4. 0.303 1,252.619 ↓ 85.5 684 1

Sort (cost=311.76..311.78 rows=8 width=12) (actual time=1,252.562..1,252.619 rows=684 loops=1)

  • Sort Key: "*SELECT* 1".date
  • Sort Method: quicksort Memory: 57kB
  • Buffers: shared hit=15,317 read=7,926 dirtied=118
  • I/O Timings: read=1,160.433
5. 0.124 1,252.316 ↓ 85.5 684 1

Append (cost=71.35..311.64 rows=8 width=12) (actual time=787.594..1,252.316 rows=684 loops=1)

  • Buffers: shared hit=15,314 read=7,926 dirtied=118
  • I/O Timings: read=1,160.433
6. 0.044 788.490 ↓ 113.5 227 1

Subquery Scan on *SELECT* 1 (cost=71.35..71.42 rows=2 width=12) (actual time=787.592..788.490 rows=227 loops=1)

  • Buffers: shared hit=498 read=5,874 dirtied=81
  • I/O Timings: read=740.639
7. 0.722 788.446 ↓ 113.5 227 1

Aggregate (cost=71.35..71.40 rows=2 width=50) (actual time=787.591..788.446 rows=227 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, events.date
  • Buffers: shared hit=498 read=5,874 dirtied=81
  • I/O Timings: read=740.639
8. 1.807 787.724 ↓ 949.5 1,899 1

Sort (cost=71.35..71.35 rows=2 width=46) (actual time=787.580..787.724 rows=1,899 loops=1)

  • Sort Key: events.project_id, events.target_type, events.date
  • Sort Method: quicksort Memory: 138kB
  • Buffers: shared hit=498 read=5,874 dirtied=81
  • I/O Timings: read=740.639
9. 2.685 785.917 ↓ 949.5 1,899 1

Nested Loop Semi Join (cost=0.87..71.34 rows=2 width=46) (actual time=53.609..785.917 rows=1,899 loops=1)

  • Buffers: shared hit=492 read=5,874 dirtied=81
  • I/O Timings: read=740.639
10. 747.940 747.940 ↓ 692.0 2,076 1

CTE Scan on user_events_in_range events (cost=0.00..15.19 rows=3 width=46) (actual time=15.148..747.940 rows=2,076 loops=1)

  • Filter: (events.action = 5)
  • Rows Removed by Filter: 1,894
  • Buffers: shared hit=443 read=5,857 dirtied=80
  • I/O Timings: read=707.939
11. 2.231 35.292 ↑ 1.7 6 2,076

Materialize (cost=0.87..55.72 rows=10 width=4) (actual time=0.004..0.017 rows=6 loops=2,076)

  • Buffers: shared hit=49 read=17 dirtied=1
  • I/O Timings: read=32.701
12. 0.046 33.061 ↑ 1.7 6 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=7.774..33.061 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=32.701
13. 17.469 17.469 ↑ 1.7 6 1

Index Only Scan using projects_pkey on public.projects (cost=0.43..21.02 rows=10 width=4) (actual time=4.299..17.469 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=17.260
14. 15.546 15.546 ↑ 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.591..2.591 rows=1 loops=6)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=17 read=7
  • I/O Timings: read=15.441
15. 0.019 1.979 ↓ 90.0 90 1

Subquery Scan on *SELECT* 2 (cost=41.32..41.36 rows=1 width=12) (actual time=1.881..1.979 rows=90 loops=1)

  • Buffers: shared hit=1,116
16. 0.074 1.960 ↓ 90.0 90 1

Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=1.880..1.960 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
17. 0.211 1.886 ↓ 139.0 139 1

Sort (cost=41.32..41.33 rows=1 width=46) (actual time=1.874..1.886 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
18. 0.130 1.675 ↓ 139.0 139 1

Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=0.075..1.675 rows=139 loops=1)

  • Buffers: shared hit=1,113
19. 0.725 0.725 ↓ 164.0 164 1

CTE Scan on user_events_in_range events_1 (cost=0.00..16.88 rows=1 width=46) (actual time=0.008..0.725 rows=164 loops=1)

  • Filter: ((events_1.action = ANY ('{1,3}'::integer[])) AND ((events_1.target_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 3,806
20. 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
21. 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
22. 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
23. 0.001 2.471 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=41.32..41.36 rows=1 width=12) (actual time=2.471..2.471 rows=0 loops=1)

24. 0.002 2.470 ↓ 0.0 0 1

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

  • Group Key: events_2.project_id, events_2.target_type, events_2.action, events_2.date
25. 0.015 2.468 ↓ 0.0 0 1

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

  • Sort Key: events_2.project_id, events_2.action, events_2.date
  • Sort Method: quicksort Memory: 25kB
26. 0.181 2.453 ↓ 0.0 0 1

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

27. 0.870 0.870 ↓ 701.0 701 1

CTE Scan on user_events_in_range events_2 (cost=0.00..16.88 rows=1 width=46) (actual time=0.242..0.870 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: 3,269
28. 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
29. 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
30. 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)
31. 0.019 2.103 ↓ 93.0 93 1

Subquery Scan on *SELECT* 4 (cost=42.16..42.20 rows=1 width=12) (actual time=2.002..2.103 rows=93 loops=1)

  • Buffers: shared hit=1,033
32. 0.079 2.084 ↓ 93.0 93 1

Aggregate (cost=42.16..42.19 rows=1 width=50) (actual time=2.001..2.084 rows=93 loops=1)

  • Group Key: events_3.project_id, events_3.target_type, events_3.action, events_3.date
  • Buffers: shared hit=1,033
33. 0.187 2.005 ↓ 129.0 129 1

Sort (cost=42.16..42.17 rows=1 width=46) (actual time=1.994..2.005 rows=129 loops=1)

  • Sort Key: events_3.project_id, events_3.action, events_3.date
  • Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=1,033
34. 0.038 1.818 ↓ 129.0 129 1

Nested Loop Semi Join (cost=0.87..42.15 rows=1 width=46) (actual time=0.078..1.818 rows=129 loops=1)

  • Buffers: shared hit=1,033
35. 0.652 0.652 ↓ 141.0 141 1

CTE Scan on user_events_in_range events_3 (cost=0.00..17.72 rows=1 width=46) (actual time=0.007..0.652 rows=141 loops=1)

  • Filter: (((events_3.target_type)::text = 'MergeRequest'::text) AND (events_3.action = ANY ('{7,1,3}'::integer[])))
  • Rows Removed by Filter: 3,829
36. 0.447 1.128 ↑ 1.0 1 141

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

  • 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,033
37. 0.423 0.423 ↑ 1.0 1 141

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

  • 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: 129
  • Buffers: shared hit=517
38. 0.258 0.258 ↑ 1.0 1 129

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

  • Index Cond: (projects_3.id = project_features_3.project_id)
  • Buffers: shared hit=516
39. 0.001 0.676 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=41.32..41.36 rows=1 width=12) (actual time=0.676..0.676 rows=0 loops=1)

40. 0.002 0.675 ↓ 0.0 0 1

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

  • Group Key: events_4.project_id, events_4.target_type, events_4.action, events_4.date
41. 0.012 0.673 ↓ 0.0 0 1

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

  • Sort Key: events_4.project_id, events_4.action, events_4.date
  • Sort Method: quicksort Memory: 25kB
42. 0.001 0.661 ↓ 0.0 0 1

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

43. 0.641 0.641 ↑ 1.0 1 1

CTE Scan on user_events_in_range events_4 (cost=0.00..16.88 rows=1 width=46) (actual time=0.357..0.641 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: 3,969
44. 0.002 0.019 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.018..0.019 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
45. 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
46. 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)
47. 0.039 450.515 ↓ 185.0 185 1

Subquery Scan on *SELECT* 6 (cost=44.91..44.95 rows=1 width=12) (actual time=450.001..450.515 rows=185 loops=1)

  • Buffers: shared hit=8,580 read=2,052 dirtied=37
  • I/O Timings: read=419.794
48. 0.427 450.476 ↓ 185.0 185 1

Aggregate (cost=44.91..44.94 rows=1 width=60) (actual time=449.999..450.476 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,580 read=2,052 dirtied=37
  • I/O Timings: read=419.794
49. 1.175 450.049 ↓ 640.0 640 1

Sort (cost=44.91..44.92 rows=1 width=56) (actual time=449.990..450.049 rows=640 loops=1)

  • Sort Key: events_5.project_id, events_5.date
  • Sort Method: quicksort Memory: 75kB
  • Buffers: shared hit=8,580 read=2,052 dirtied=37
  • I/O Timings: read=419.794
50. 0.973 448.874 ↓ 640.0 640 1

Nested Loop (cost=1.44..44.90 rows=1 width=56) (actual time=3.519..448.874 rows=640 loops=1)

  • Buffers: shared hit=8,580 read=2,052 dirtied=37
  • I/O Timings: read=419.794
51. 0.865 15.997 ↓ 818.0 818 1

Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=50) (actual time=0.056..15.997 rows=818 loops=1)

  • Buffers: shared hit=6,545
52. 1.827 1.827 ↓ 887.0 887 1

CTE Scan on user_events_in_range events_5 (cost=0.00..16.88 rows=1 width=50) (actual time=0.003..1.827 rows=887 loops=1)

  • Filter: (((events_5.target_type)::text = 'Note'::text) AND (events_5.action = 6))
  • Rows Removed by Filter: 3,083
53. 4.711 13.305 ↑ 1.0 1 887

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

  • 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,545
54. 5.322 5.322 ↑ 1.0 1 887

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

  • 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: 818
  • Buffers: shared hit=3,273
55. 3.272 3.272 ↑ 1.0 1 818

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.004..0.004 rows=1 loops=818)

  • Index Cond: (projects_5.id = project_features_5.project_id)
  • Buffers: shared hit=3,272
56. 431.904 431.904 ↑ 1.0 1 818

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

  • Index Cond: (notes.id = events_5.target_id)
  • Filter: ((notes.noteable_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,035 read=2,052 dirtied=37
  • I/O Timings: read=419.794
57. 0.017 5.958 ↓ 89.0 89 1

Subquery Scan on *SELECT* 7 (cost=28.92..28.96 rows=1 width=12) (actual time=5.830..5.958 rows=89 loops=1)

  • Buffers: shared hit=4,087
58. 0.106 5.941 ↓ 89.0 89 1

Aggregate (cost=28.92..28.95 rows=1 width=60) (actual time=5.829..5.941 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,087
59. 0.138 5.835 ↓ 175.0 175 1

Sort (cost=28.92..28.92 rows=1 width=56) (actual time=5.821..5.835 rows=175 loops=1)

  • Sort Key: events_6.project_id, events_6.date, notes_1.noteable_type
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=4,087
60. 0.656 5.697 ↓ 175.0 175 1

Nested Loop (cost=0.57..28.91 rows=1 width=56) (actual time=0.042..5.697 rows=175 loops=1)

  • Buffers: shared hit=4,087
61. 0.951 0.951 ↓ 818.0 818 1

CTE Scan on user_events_in_range events_6 (cost=0.00..25.31 rows=1 width=50) (actual time=0.006..0.951 rows=818 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: 3,152
62. 4.090 4.090 ↓ 0.0 0 818

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

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