explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YG6c

Settings
# exclusive inclusive rows x rows loops node
1. 0.624 5,156.065 ↓ 52.1 365 1

GroupAggregate (cost=745.40..745.56 rows=7 width=8) (actual time=5,155.145..5,156.065 rows=365 loops=1)

  • Group Key: "*SELECT* 1".date
  • Buffers: shared hit=212,732 read=12,737
  • I/O Timings: read=4,305.022
2. 1.430 5,155.441 ↓ 447.3 3,131 1

Sort (cost=745.40..745.42 rows=7 width=12) (actual time=5,155.032..5,155.441 rows=3,131 loops=1)

  • Sort Key: "*SELECT* 1".date
  • Sort Method: quicksort Memory: 243kB
  • Buffers: shared hit=212,732 read=12,737
  • I/O Timings: read=4,305.022
3. 4,624.041 5,154.011 ↓ 447.3 3,131 1

Append (cost=91.94..745.30 rows=7 width=12) (actual time=400.565..5,154.011 rows=3,131 loops=1)

  • Buffers: shared hit=212,729 read=12,737
  • I/O Timings: read=4,305.022
4. 0.079 401.773 ↓ 656.0 656 1

Subquery Scan on *SELECT* 1 (cost=91.94..91.98 rows=1 width=12) (actual time=400.564..401.773 rows=656 loops=1)

  • Buffers: shared hit=26,269 read=5,755
  • I/O Timings: read=59.713
5. 0.890 401.694 ↓ 656.0 656 1

GroupAggregate (cost=91.94..91.97 rows=1 width=27) (actual time=400.562..401.694 rows=656 loops=1)

  • Group Key: events.project_id, events.target_type, events.action, (date((events.created_at + '00:00:00'::interval)))
  • Buffers: shared hit=26,269 read=5,755
  • I/O Timings: read=59.713
6. 4.178 400.804 ↓ 4,263.0 4,263 1

Sort (cost=91.94..91.94 rows=1 width=23) (actual time=400.553..400.804 rows=4,263 loops=1)

  • Sort Key: events.project_id, events.target_type, (date((events.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 392kB
  • Buffers: shared hit=26,269 read=5,755
  • I/O Timings: read=59.713
7. 1.589 396.626 ↓ 4,263.0 4,263 1

Nested Loop (cost=56.27..91.93 rows=1 width=23) (actual time=2.602..396.626 rows=4,263 loops=1)

  • Buffers: shared hit=26,263 read=5,755
  • I/O Timings: read=59.713
8. 0.036 2.077 ↑ 1.0 10 1

HashAggregate (cost=55.70..55.80 rows=10 width=4) (actual time=2.052..2.077 rows=10 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=101
9. 0.033 2.041 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.220..2.041 rows=10 loops=1)

  • Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL))
  • Buffers: shared hit=101
10. 1.238 1.238 ↑ 1.0 10 1

Index Only Scan using projects_pkey on projects (cost=0.43..21.02 rows=10 width=4) (actual time=0.144..1.238 rows=10 loops=1)

  • Index Cond: (id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[]))
  • Heap Fetches: 24
  • Buffers: shared hit=61
11. 0.770 0.770 ↑ 1.0 1 10

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.45 rows=1 width=8) (actual time=0.077..0.077 rows=1 loops=10)

  • Index Cond: (projects.id = project_id)
  • Buffers: shared hit=40
12. 392.960 392.960 ↓ 426.0 426 10

Index Scan using index_events_on_author_id_and_project_id on events (cost=0.57..3.60 rows=1 width=27) (actual time=1.300..39.296 rows=426 loops=10)

  • Index Cond: ((author_id = 64,248) AND (project_id = projects.id))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND (action = 5))
  • Rows Removed by Filter: 2,781
  • Buffers: shared hit=26,162 read=5,755
  • I/O Timings: read=59.713
13. 0.000 64.963 ↓ 621.0 621 1

Subquery Scan on *SELECT* 2 (cost=91.96..92.01 rows=1 width=12) (actual time=64.543..64.963 rows=621 loops=1)

  • Buffers: shared hit=32,018
14. 0.295 64.890 ↓ 621.0 621 1

GroupAggregate (cost=91.96..92.00 rows=1 width=27) (actual time=64.542..64.890 rows=621 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=32,018
15. 1.389 64.595 ↓ 996.0 996 1

Sort (cost=91.96..91.97 rows=1 width=23) (actual time=64.532..64.595 rows=996 loops=1)

  • Sort Key: events_1.project_id, events_1.action, (date((events_1.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 102kB
  • Buffers: shared hit=32,018
16. 0.331 63.206 ↓ 996.0 996 1

Nested Loop (cost=56.27..91.95 rows=1 width=23) (actual time=0.290..63.206 rows=996 loops=1)

  • Buffers: shared hit=32,015
17. 0.018 0.225 ↑ 1.0 10 1

HashAggregate (cost=55.70..55.80 rows=10 width=4) (actual time=0.213..0.225 rows=10 loops=1)

  • Group Key: projects_1.id
  • Buffers: shared hit=98
18. 0.010 0.207 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.037..0.207 rows=10 loops=1)

  • Filter: ((project_features_1.issues_access_level > 0) OR (project_features_1.issues_access_level IS NULL))
  • Buffers: shared hit=98
19. 0.117 0.117 ↑ 1.0 10 1

Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..21.02 rows=10 width=4) (actual time=0.018..0.117 rows=10 loops=1)

  • Index Cond: (id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[]))
  • Heap Fetches: 24
  • Buffers: shared hit=58
20. 0.080 0.080 ↑ 1.0 1 10

Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.43..3.45 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=10)

  • Index Cond: (projects_1.id = project_id)
  • Buffers: shared hit=40
21. 62.650 62.650 ↓ 100.0 100 10

Index Scan using index_events_on_author_id_and_project_id on events events_1 (cost=0.57..3.60 rows=1 width=27) (actual time=0.116..6.265 rows=100 loops=10)

  • Index Cond: ((author_id = 64,248) AND (project_id = projects_1.id))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND (action = ANY ('{1,3}'::integer[])) AND ((target_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 3,108
  • Buffers: shared hit=31,917
22. 0.001 1.072 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=91.96..92.01 rows=1 width=12) (actual time=1.072..1.072 rows=0 loops=1)

  • Buffers: shared hit=482
23. 0.001 1.071 ↓ 0.0 0 1

GroupAggregate (cost=91.96..92.00 rows=1 width=27) (actual time=1.071..1.071 rows=0 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=482
24. 0.012 1.070 ↓ 0.0 0 1

Sort (cost=91.96..91.97 rows=1 width=23) (actual time=1.070..1.070 rows=0 loops=1)

  • Sort Key: events_2.project_id, events_2.action, (date((events_2.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=482
25. 0.003 1.058 ↓ 0.0 0 1

Nested Loop (cost=56.27..91.95 rows=1 width=23) (actual time=1.058..1.058 rows=0 loops=1)

  • Buffers: shared hit=482
26. 0.005 0.249 ↑ 5.0 2 1

HashAggregate (cost=55.70..55.80 rows=10 width=4) (actual time=0.248..0.249 rows=2 loops=1)

  • Group Key: projects_2.id
  • Buffers: shared hit=98
27. 0.006 0.244 ↑ 5.0 2 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.069..0.244 rows=2 loops=1)

  • Filter: ((project_features_2.wiki_access_level > 0) OR (project_features_2.wiki_access_level IS NULL))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=98
28. 0.138 0.138 ↑ 1.0 10 1

Index Only Scan using projects_pkey on projects projects_2 (cost=0.43..21.02 rows=10 width=4) (actual time=0.033..0.138 rows=10 loops=1)

  • Index Cond: (id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[]))
  • Heap Fetches: 24
  • Buffers: shared hit=58
29. 0.100 0.100 ↑ 1.0 1 10

Index Scan using index_project_features_on_project_id on project_features project_features_2 (cost=0.43..3.45 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=10)

  • Index Cond: (projects_2.id = project_id)
  • Buffers: shared hit=40
30. 0.806 0.806 ↓ 0.0 0 2

Index Scan using index_events_on_author_id_and_project_id on events events_2 (cost=0.57..3.60 rows=1 width=27) (actual time=0.403..0.403 rows=0 loops=2)

  • Index Cond: ((author_id = 64,248) AND (project_id = projects_2.id))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND (action = ANY ('{1,2}'::integer[])) AND ((target_type)::text = 'WikiPage::Meta'::text))
  • Rows Removed by Filter: 189
  • Buffers: shared hit=384
31. 0.090 67.158 ↓ 766.0 766 1

Subquery Scan on *SELECT* 4 (cost=91.98..92.02 rows=1 width=12) (actual time=66.583..67.158 rows=766 loops=1)

  • Buffers: shared hit=32,015
32. 0.411 67.068 ↓ 766.0 766 1

GroupAggregate (cost=91.98..92.01 rows=1 width=27) (actual time=66.581..67.068 rows=766 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=32,015
33. 2.350 66.657 ↓ 1,481.0 1,481 1

Sort (cost=91.98..91.98 rows=1 width=23) (actual time=66.573..66.657 rows=1,481 loops=1)

  • Sort Key: events_3.project_id, events_3.action, (date((events_3.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 164kB
  • Buffers: shared hit=32,015
34. 0.460 64.307 ↓ 1,481.0 1,481 1

Nested Loop (cost=56.27..91.97 rows=1 width=23) (actual time=0.222..64.307 rows=1,481 loops=1)

  • Buffers: shared hit=32,015
35. 0.019 0.157 ↑ 1.0 10 1

HashAggregate (cost=55.70..55.80 rows=10 width=4) (actual time=0.144..0.157 rows=10 loops=1)

  • Group Key: projects_3.id
  • Buffers: shared hit=98
36. 0.007 0.138 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.048..0.138 rows=10 loops=1)

  • Filter: ((project_features_3.merge_requests_access_level > 0) OR (project_features_3.merge_requests_access_level IS NULL))
  • Buffers: shared hit=98
37. 0.081 0.081 ↑ 1.0 10 1

Index Only Scan using projects_pkey on projects projects_3 (cost=0.43..21.02 rows=10 width=4) (actual time=0.031..0.081 rows=10 loops=1)

  • Index Cond: (id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[]))
  • Heap Fetches: 24
  • Buffers: shared hit=58
38. 0.050 0.050 ↑ 1.0 1 10

Index Scan using index_project_features_on_project_id on project_features project_features_3 (cost=0.43..3.45 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (projects_3.id = project_id)
  • Buffers: shared hit=40
39. 63.690 63.690 ↓ 148.0 148 10

Index Scan using index_events_on_author_id_and_project_id on events events_3 (cost=0.57..3.61 rows=1 width=27) (actual time=0.108..6.369 rows=148 loops=10)

  • Index Cond: ((author_id = 64,248) AND (project_id = projects_3.id))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND ((target_type)::text = 'MergeRequest'::text) AND (action = ANY ('{7,1,3}'::integer[])))
  • Rows Removed by Filter: 3,059
  • Buffers: shared hit=31,917
40. 0.000 63.234 ↓ 4.0 4 1

Subquery Scan on *SELECT* 5 (cost=91.96..92.01 rows=1 width=12) (actual time=63.230..63.234 rows=4 loops=1)

  • Buffers: shared hit=32,015
41. 0.011 63.232 ↓ 4.0 4 1

GroupAggregate (cost=91.96..92.00 rows=1 width=27) (actual time=63.229..63.232 rows=4 loops=1)

  • Group Key: events_4.project_id, events_4.target_type, events_4.action, (date((events_4.created_at + '00:00:00'::interval)))
  • Buffers: shared hit=32,015
42. 0.028 63.221 ↓ 6.0 6 1

Sort (cost=91.96..91.97 rows=1 width=23) (actual time=63.221..63.221 rows=6 loops=1)

  • Sort Key: events_4.project_id, events_4.action, (date((events_4.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=32,015
43. 0.030 63.193 ↓ 6.0 6 1

Nested Loop (cost=56.27..91.95 rows=1 width=23) (actual time=15.471..63.193 rows=6 loops=1)

  • Buffers: shared hit=32,015
44. 0.020 0.253 ↑ 1.0 10 1

HashAggregate (cost=55.70..55.80 rows=10 width=4) (actual time=0.239..0.253 rows=10 loops=1)

  • Group Key: projects_4.id
  • Buffers: shared hit=98
45. 0.011 0.233 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.059..0.233 rows=10 loops=1)

  • Filter: ((project_features_4.issues_access_level > 0) OR (project_features_4.issues_access_level IS NULL))
  • Buffers: shared hit=98
46. 0.132 0.132 ↑ 1.0 10 1

Index Only Scan using projects_pkey on projects projects_4 (cost=0.43..21.02 rows=10 width=4) (actual time=0.026..0.132 rows=10 loops=1)

  • Index Cond: (id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[]))
  • Heap Fetches: 24
  • Buffers: shared hit=58
47. 0.090 0.090 ↑ 1.0 1 10

Index Scan using index_project_features_on_project_id on project_features project_features_4 (cost=0.43..3.45 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=10)

  • Index Cond: (projects_4.id = project_id)
  • Buffers: shared hit=40
48. 62.910 62.910 ↑ 1.0 1 10

Index Scan using index_events_on_author_id_and_project_id on events events_4 (cost=0.57..3.60 rows=1 width=27) (actual time=1.909..6.291 rows=1 loops=10)

  • Index Cond: ((author_id = 64,248) AND (project_id = projects_4.id))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND (action = ANY ('{1,2}'::integer[])) AND ((target_type)::text = 'DesignManagement::Design'::text))
  • Rows Removed by Filter: 3,206
  • Buffers: shared hit=31,917
49. 0.000 4,429.304 ↓ 411.0 411 1

Subquery Scan on *SELECT* 6 (cost=95.56..95.60 rows=1 width=12) (actual time=4,428.843..4,429.304 rows=411 loops=1)

  • Buffers: shared hit=41,571 read=6,952
  • I/O Timings: read=4,224.175
50. 0.374 4,429.236 ↓ 411.0 411 1

GroupAggregate (cost=95.56..95.59 rows=1 width=37) (actual time=4,428.834..4,429.236 rows=411 loops=1)

  • Group Key: events_5.project_id, events_5.target_type, events_5.action, (date((events_5.created_at + '00:00:00'::interval))), notes.noteable_type
  • Buffers: shared hit=41,571 read=6,952
  • I/O Timings: read=4,224.175
51. 3.688 4,428.862 ↓ 1,105.0 1,105 1

Sort (cost=95.56..95.56 rows=1 width=33) (actual time=4,428.779..4,428.862 rows=1,105 loops=1)

  • Sort Key: events_5.project_id, (date((events_5.created_at + '00:00:00'::interval)))
  • Sort Method: quicksort Memory: 135kB
  • Buffers: shared hit=41,571 read=6,952
  • I/O Timings: read=4,224.175
52. 11.237 4,425.174 ↓ 1,105.0 1,105 1

Nested Loop (cost=56.85..95.55 rows=1 width=33) (actual time=3.106..4,425.174 rows=1,105 loops=1)

  • Buffers: shared hit=41,571 read=6,952
  • I/O Timings: read=4,224.175
53. 2.108 88.245 ↓ 3,287.0 3,287 1

Nested Loop (cost=56.27..91.95 rows=1 width=31) (actual time=0.296..88.245 rows=3,287 loops=1)

  • Buffers: shared hit=32,015
54. 0.083 0.317 ↑ 1.0 10 1

HashAggregate (cost=55.70..55.80 rows=10 width=4) (actual time=0.240..0.317 rows=10 loops=1)

  • Group Key: projects_5.id
  • Buffers: shared hit=98
55. 0.030 0.234 ↑ 1.0 10 1

Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=0.069..0.234 rows=10 loops=1)

  • Filter: ((project_features_5.merge_requests_access_level > 0) OR (project_features_5.merge_requests_access_level IS NULL))
  • Buffers: shared hit=98
56. 0.124 0.124 ↑ 1.0 10 1

Index Only Scan using projects_pkey on projects projects_5 (cost=0.43..21.02 rows=10 width=4) (actual time=0.031..0.124 rows=10 loops=1)

  • Index Cond: (id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[]))
  • Heap Fetches: 24
  • Buffers: shared hit=58
57. 0.080 0.080 ↑ 1.0 1 10

Index Scan using index_project_features_on_project_id on project_features project_features_5 (cost=0.43..3.45 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=10)

  • Index Cond: (projects_5.id = project_id)
  • Buffers: shared hit=40
58. 85.820 85.820 ↓ 329.0 329 10

Index Scan using index_events_on_author_id_and_project_id on events events_5 (cost=0.57..3.60 rows=1 width=31) (actual time=0.045..8.582 rows=329 loops=10)

  • Index Cond: ((author_id = 64,248) AND (project_id = projects_5.id))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND ((target_type)::text = 'Note'::text) AND (action = 6))
  • Rows Removed by Filter: 2,878
  • Buffers: shared hit=31,917
59. 4,325.692 4,325.692 ↓ 0.0 0 3,287

Index Scan using notes_pkey on notes (cost=0.57..3.59 rows=1 width=14) (actual time=1.316..1.316 rows=0 loops=3,287)

  • Index Cond: (id = events_5.target_id)
  • Filter: ((noteable_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9,556 read=6,952
  • I/O Timings: read=4,224.175
60. 0.091 126.183 ↓ 673.0 673 1

Subquery Scan on *SELECT* 7 (cost=189.59..189.64 rows=1 width=12) (actual time=125.378..126.183 rows=673 loops=1)

  • Buffers: shared hit=48,359 read=30
  • I/O Timings: read=21.133
61. 0.654 126.092 ↓ 673.0 673 1

GroupAggregate (cost=189.59..189.63 rows=1 width=37) (actual time=125.364..126.092 rows=673 loops=1)

  • Group Key: events_6.project_id, events_6.target_type, events_6.action, (date((events_6.created_at + '00:00:00'::interval))), notes_1.noteable_type
  • Buffers: shared hit=48,359 read=30
  • I/O Timings: read=21.133
62. 2.824 125.438 ↓ 2,182.0 2,182 1

Sort (cost=189.59..189.60 rows=1 width=33) (actual time=125.299..125.438 rows=2,182 loops=1)

  • Sort Key: events_6.project_id, (date((events_6.created_at + '00:00:00'::interval))), notes_1.noteable_type
  • Sort Method: quicksort Memory: 267kB
  • Buffers: shared hit=48,359 read=30
  • I/O Timings: read=21.133
63. 3.138 122.614 ↓ 2,182.0 2,182 1

Nested Loop (cost=1.15..189.58 rows=1 width=33) (actual time=0.237..122.614 rows=2,182 loops=1)

  • Buffers: shared hit=48,359 read=30
  • I/O Timings: read=21.133
64. 70.171 70.171 ↓ 3,287.0 3,287 1

Index Scan using index_events_on_author_id_and_project_id on events events_6 (cost=0.57..185.98 rows=1 width=31) (actual time=0.121..70.171 rows=3,287 loops=1)

  • Index Cond: ((author_id = 64,248) AND (project_id = ANY ('{278964,2009901,554859,20699,140724,1148549,3828396,74823,1304532,14022}'::integer[])))
  • Filter: ((created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone) AND ((target_type)::text = 'Note'::text) AND (action = 6))
  • Rows Removed by Filter: 28,784
  • Buffers: shared hit=31,917
65. 49.305 49.305 ↑ 1.0 1 3,287

Index Scan using notes_pkey on notes notes_1 (cost=0.57..3.59 rows=1 width=14) (actual time=0.015..0.015 rows=1 loops=3,287)

  • Index Cond: (id = events_6.target_id)
  • Filter: ((noteable_type)::text <> 'MergeRequest'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=16,442 read=30
  • I/O Timings: read=21.133
Planning time : 29.805 ms
Execution time : 5,159.438 ms