explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pqJY : Old contribution calendar query

Settings
# exclusive inclusive rows x rows loops node
1. 0.880 10,451.549 ↓ 240.2 961 1

HashAggregate (cost=331.89..331.93 rows=4 width=50) (actual time=10,451.278..10,451.549 rows=961 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, (date((events.created_at + '00:00:00'::interval))), (count(events.id))
  • Buffers: shared hit=18,129 read=5,876 dirtied=92
  • I/O Timings: read=10,323.560
2. 0.136 10,450.669 ↓ 240.2 961 1

Append (cost=82.90..331.84 rows=4 width=50) (actual time=10,418.765..10,450.669 rows=961 loops=1)

  • Buffers: shared hit=18,129 read=5,876 dirtied=92
  • I/O Timings: read=10,323.560
3. 0.751 10,419.653 ↓ 232.0 232 1

Aggregate (cost=82.90..82.93 rows=1 width=27) (actual time=10,418.764..10,419.653 rows=232 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, (date((events.created_at + '00:00:00'::interval)))
  • Buffers: shared hit=135 read=5,876 dirtied=92
  • I/O Timings: read=10,323.560
4. 7.314 10,418.902 ↓ 1,921.0 1,921 1

Sort (cost=82.90..82.90 rows=1 width=23) (actual time=10,418.751..10,418.902 rows=1,921 loops=1)

  • Sort Key: events.project_id, events.target_type, (date((events.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 139kB
  • Buffers: shared hit=135 read=5,876 dirtied=92
  • I/O Timings: read=10,323.560
5. 5.761 10,411.588 ↓ 1,921.0 1,921 1

Nested Loop (cost=50.86..82.89 rows=1 width=23) (actual time=59.006..10,411.588 rows=1,921 loops=1)

  • Buffers: shared hit=126 read=5,876 dirtied=92
  • I/O Timings: read=10,323.560
6. 0.025 12.051 ↑ 1.5 6 1

HashAggregate (cost=50.28..50.37 rows=9 width=4) (actual time=12.042..12.051 rows=6 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=46 read=17 dirtied=1
  • I/O Timings: read=11.799
7. 0.029 12.026 ↑ 1.5 6 1

Nested Loop Left Join (cost=0.87..50.26 rows=9 width=4) (actual time=3.047..12.026 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=17 dirtied=1
  • I/O Timings: read=11.799
8. 4.491 4.491 ↑ 1.5 6 1

Index Only Scan using projects_pkey on public.projects (cost=0.43..19.07 rows=9 width=4) (actual time=0.371..4.491 rows=6 loops=1)

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

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=17 read=7
  • I/O Timings: read=7.430
10. 10,393.776 10,393.776 ↓ 320.0 320 6

Index Scan using index_events_on_author_id_and_project_id on public.events (cost=0.57..3.60 rows=1 width=27) (actual time=7.828..1,732.296 rows=320 loops=6)

  • Index Cond: ((events.author_id = 3,614,858) AND (events.project_id = projects.id))
  • Filter: ((events.created_at >= '2019-08-27 00:00:00+00'::timestamp with time zone) AND (events.created_at <= '2020-08-27 23:59:59.999999+00'::timestamp with time zone) AND (events.action = 5))
  • Rows Removed by Filter: 669
  • Buffers: shared hit=80 read=5,859 dirtied=91
  • I/O Timings: read=10,311.761
11. 0.080 8.748 ↓ 91.0 91 1

Aggregate (cost=82.92..82.95 rows=1 width=27) (actual time=8.664..8.748 rows=91 loops=1)

  • Group Key: events_1.project_id, events_1.target_type, events_1.action, (date((events_1.created_at + '00:00:00'::interval)))
  • Buffers: shared hit=6,000
12. 0.293 8.668 ↓ 140.0 140 1

Sort (cost=82.92..82.92 rows=1 width=23) (actual time=8.656..8.668 rows=140 loops=1)

  • Sort Key: events_1.project_id, events_1.action, (date((events_1.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=6,000
13. 0.104 8.375 ↓ 140.0 140 1

Nested Loop (cost=50.86..82.91 rows=1 width=23) (actual time=0.414..8.375 rows=140 loops=1)

  • Buffers: shared hit=5,997
14. 0.015 0.255 ↑ 1.5 6 1

HashAggregate (cost=50.28..50.37 rows=9 width=4) (actual time=0.250..0.255 rows=6 loops=1)

  • Group Key: projects_1.id
  • Buffers: shared hit=58
15. 0.118 0.240 ↑ 1.5 6 1

Nested Loop Left Join (cost=0.87..50.26 rows=9 width=4) (actual time=0.061..0.240 rows=6 loops=1)

  • 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=58
16. 0.074 0.074 ↑ 1.5 6 1

Index Only Scan using projects_pkey on public.projects projects_1 (cost=0.43..19.07 rows=9 width=4) (actual time=0.033..0.074 rows=6 loops=1)

  • Index Cond: (projects_1.id = ANY ('{278964,42,41,40,39,38,37,36,35}'::integer[]))
  • Heap Fetches: 2
  • Buffers: shared hit=34
17. 0.048 0.048 ↑ 1.0 1 6

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.008..0.008 rows=1 loops=6)

  • Index Cond: (projects_1.id = project_features_1.project_id)
  • Buffers: shared hit=24
18. 8.016 8.016 ↓ 23.0 23 6

Index Scan using index_events_on_author_id_and_project_id on public.events events_1 (cost=0.57..3.60 rows=1 width=27) (actual time=0.028..1.336 rows=23 loops=6)

  • Index Cond: ((events_1.author_id = 3,614,858) AND (events_1.project_id = projects_1.id))
  • Filter: ((events_1.created_at >= '2019-08-27 00:00:00+00'::timestamp with time zone) AND (events_1.created_at <= '2020-08-27 23:59:59.999999+00'::timestamp with time zone) AND (events_1.action = ANY ('{1,3}'::integer[])) AND ((events_1.target_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 966
  • Buffers: shared hit=5,939
19. 0.087 7.095 ↓ 92.0 92 1

Aggregate (cost=82.93..82.96 rows=1 width=27) (actual time=7.005..7.095 rows=92 loops=1)

  • Group Key: events_2.project_id, events_2.target_type, events_2.action, (date((events_2.created_at + '00:00:00'::interval)))
  • Buffers: shared hit=5,997
20. 0.219 7.008 ↓ 127.0 127 1

Sort (cost=82.93..82.94 rows=1 width=23) (actual time=6.995..7.008 rows=127 loops=1)

  • Sort Key: events_2.project_id, events_2.action, (date((events_2.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=5,997
21. 0.079 6.789 ↓ 127.0 127 1

Nested Loop (cost=50.86..82.92 rows=1 width=23) (actual time=0.196..6.789 rows=127 loops=1)

  • Buffers: shared hit=5,997
22. 0.010 0.092 ↑ 1.5 6 1

HashAggregate (cost=50.28..50.37 rows=9 width=4) (actual time=0.088..0.092 rows=6 loops=1)

  • Group Key: projects_2.id
  • Buffers: shared hit=58
23. 0.005 0.082 ↑ 1.5 6 1

Nested Loop Left Join (cost=0.87..50.26 rows=9 width=4) (actual time=0.043..0.082 rows=6 loops=1)

  • Filter: ((project_features_2.merge_requests_access_level > 0) OR (project_features_2.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=58
24. 0.047 0.047 ↑ 1.5 6 1

Index Only Scan using projects_pkey on public.projects projects_2 (cost=0.43..19.07 rows=9 width=4) (actual time=0.026..0.047 rows=6 loops=1)

  • Index Cond: (projects_2.id = ANY ('{278964,42,41,40,39,38,37,36,35}'::integer[]))
  • Heap Fetches: 2
  • Buffers: shared hit=34
25. 0.030 0.030 ↑ 1.0 1 6

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.005..0.005 rows=1 loops=6)

  • Index Cond: (projects_2.id = project_features_2.project_id)
  • Buffers: shared hit=24
26. 6.618 6.618 ↓ 21.0 21 6

Index Scan using index_events_on_author_id_and_project_id on public.events events_2 (cost=0.57..3.61 rows=1 width=27) (actual time=0.019..1.103 rows=21 loops=6)

  • Index Cond: ((events_2.author_id = 3,614,858) AND (events_2.project_id = projects_2.id))
  • Filter: ((events_2.created_at >= '2019-08-27 00:00:00+00'::timestamp with time zone) AND (events_2.created_at <= '2020-08-27 23:59:59.999999+00'::timestamp with time zone) AND ((events_2.target_type)::text = 'MergeRequest'::text) AND (events_2.action = ANY ('{7,1,3}'::integer[])))
  • Rows Removed by Filter: 968
  • Buffers: shared hit=5,939
27. 1.387 15.037 ↓ 546.0 546 1

Aggregate (cost=82.90..82.93 rows=1 width=27) (actual time=13.421..15.037 rows=546 loops=1)

  • Group Key: events_3.project_id, events_3.target_type, events_3.action, (date((events_3.created_at + '00:00:00'::interval)))
  • Buffers: shared hit=5,997
28. 4.749 13.650 ↓ 3,036.0 3,036 1

Sort (cost=82.90..82.90 rows=1 width=23) (actual time=13.408..13.650 rows=3,036 loops=1)

  • Sort Key: events_3.project_id, events_3.target_type, (date((events_3.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 334kB
  • Buffers: shared hit=5,997
29. 1.405 8.901 ↓ 3,036.0 3,036 1

Nested Loop (cost=50.86..82.89 rows=1 width=23) (actual time=0.199..8.901 rows=3,036 loops=1)

  • Buffers: shared hit=5,997
30. 0.012 0.122 ↑ 1.5 6 1

HashAggregate (cost=50.28..50.37 rows=9 width=4) (actual time=0.117..0.122 rows=6 loops=1)

  • Group Key: projects_3.id
  • Buffers: shared hit=58
31. 0.010 0.110 ↑ 1.5 6 1

Nested Loop Left Join (cost=0.87..50.26 rows=9 width=4) (actual time=0.054..0.110 rows=6 loops=1)

  • 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=58
32. 0.064 0.064 ↑ 1.5 6 1

Index Only Scan using projects_pkey on public.projects projects_3 (cost=0.43..19.07 rows=9 width=4) (actual time=0.034..0.064 rows=6 loops=1)

  • Index Cond: (projects_3.id = ANY ('{278964,42,41,40,39,38,37,36,35}'::integer[]))
  • Heap Fetches: 2
  • Buffers: shared hit=34
33. 0.036 0.036 ↑ 1.0 1 6

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.006..0.006 rows=1 loops=6)

  • Index Cond: (projects_3.id = project_features_3.project_id)
  • Buffers: shared hit=24
34. 7.374 7.374 ↓ 506.0 506 6

Index Scan using index_events_on_author_id_and_project_id on public.events events_3 (cost=0.57..3.60 rows=1 width=27) (actual time=0.014..1.229 rows=506 loops=6)

  • Index Cond: ((events_3.author_id = 3,614,858) AND (events_3.project_id = projects_3.id))
  • Filter: ((events_3.created_at >= '2019-08-27 00:00:00+00'::timestamp with time zone) AND (events_3.created_at <= '2020-08-27 23:59:59.999999+00'::timestamp with time zone) AND (events_3.action = 6))
  • Rows Removed by Filter: 483
  • Buffers: shared hit=5,939