explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 785f

Settings
# exclusive inclusive rows x rows loops node
1. 4.210 6,126.165 ↑ 2.5 8,726 1

Sort (cost=53,644.09..53,699.52 rows=22,169 width=58) (actual time=6,125.491..6,126.165 rows=8,726 loops=1)

  • Sort Key: resource_milestone_events.created_at
  • Sort Method: quicksort Memory: 1,066kB
  • Buffers: shared hit=40,378 read=4,416 dirtied=391
  • I/O Timings: read=5,841.488
2. 16.846 6,121.955 ↑ 2.5 8,726 1

HashAggregate (cost=51,822.22..52,043.91 rows=22,169 width=58) (actual time=6,118.832..6,121.955 rows=8,726 loops=1)

  • Group Key: ('milestone'::text), resource_milestone_events.created_at, resource_milestone_events.milestone_id, resource_milestone_events.action, resource_milestone_events.issue_id
  • Buffers: shared hit=40,375 read=4,416 dirtied=391
  • I/O Timings: read=5,841.488
3. 2.131 6,105.109 ↑ 2.5 8,726 1

Append (cost=4,618.90..51,545.10 rows=22,169 width=58) (actual time=17.544..6,105.109 rows=8,726 loops=1)

  • Buffers: shared hit=40,375 read=4,416 dirtied=391
  • I/O Timings: read=5,841.488
4. 7.577 2,649.359 ↑ 1.3 7,160 1

Nested Loop (cost=4,618.90..21,951.96 rows=8,994 width=58) (actual time=17.543..2,649.359 rows=7,160 loops=1)

  • Buffers: shared hit=17,402 read=1,787 dirtied=326
  • I/O Timings: read=2,576.065
5. 4.243 14.446 ↑ 1.7 3,013 1

HashAggregate (cost=4,618.48..4,670.82 rows=5,234 width=8) (actual time=11.776..14.446 rows=3,013 loops=1)

  • Group Key: resource_milestone_events_1.issue_id
  • Buffers: shared hit=2,973 read=17 dirtied=261
  • I/O Timings: read=0.589
6. 10.203 10.203 ↑ 1.0 5,198 1

Index Scan using index_resource_milestone_events_on_milestone_id_and_add_action on public.resource_milestone_events resource_milestone_events_1 (cost=0.43..4,605.37 rows=5,244 width=8) (actual time=0.209..10.203 rows=5,198 loops=1)

  • Index Cond: (resource_milestone_events_1.milestone_id = 1,233,752)
  • Filter: (resource_milestone_events_1.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,973 read=17 dirtied=261
  • I/O Timings: read=0.589
7. 2,627.336 2,627.336 ↑ 1.0 2 3,013

Index Scan using index_resource_milestone_events_on_issue_id on public.resource_milestone_events (cost=0.43..3.27 rows=2 width=26) (actual time=0.856..0.872 rows=2 loops=3,013)

  • Index Cond: (resource_milestone_events.issue_id = resource_milestone_events_1.issue_id)
  • Filter: (resource_milestone_events.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=14,429 read=1,770 dirtied=65
  • I/O Timings: read=2,575.476
8. 0.486 2,001.737 ↑ 11.4 537 1

Subquery Scan on *SELECT* 2 (cost=4,618.90..19,318.44 rows=6,098 width=58) (actual time=31.763..2,001.737 rows=537 loops=1)

  • Buffers: shared hit=11,175 read=1,408 dirtied=57
  • I/O Timings: read=1,847.962
9. 2.974 2,001.251 ↑ 11.4 537 1

Nested Loop (cost=4,618.90..19,242.21 rows=6,098 width=52) (actual time=31.760..2,001.251 rows=537 loops=1)

  • Buffers: shared hit=11,175 read=1,408 dirtied=57
  • I/O Timings: read=1,847.962
10. 4.342 9.697 ↑ 1.7 3,013 1

HashAggregate (cost=4,618.48..4,670.82 rows=5,234 width=8) (actual time=7.275..9.697 rows=3,013 loops=1)

  • Group Key: resource_milestone_events_2.issue_id
  • Buffers: shared hit=2,987
11. 5.355 5.355 ↑ 1.0 5,198 1

Index Scan using index_resource_milestone_events_on_milestone_id_and_add_action on public.resource_milestone_events resource_milestone_events_2 (cost=0.43..4,605.37 rows=5,244 width=8) (actual time=0.023..5.355 rows=5,198 loops=1)

  • Index Cond: (resource_milestone_events_2.milestone_id = 1,233,752)
  • Filter: (resource_milestone_events_2.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,987
12. 1,988.580 1,988.580 ↓ 0.0 0 3,013

Index Scan using index_resource_state_events_on_issue_id_and_created_at on public.resource_state_events (cost=0.43..2.76 rows=1 width=18) (actual time=0.629..0.660 rows=0 loops=3,013)

  • Index Cond: ((resource_state_events.issue_id = resource_milestone_events_2.issue_id) AND (resource_state_events.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone))
  • Buffers: shared hit=8,188 read=1,408 dirtied=57
  • I/O Timings: read=1,847.962
13. 0.633 1,451.882 ↑ 6.9 1,029 1

Subquery Scan on *SELECT* 3 (cost=4,618.90..10,073.92 rows=7,077 width=58) (actual time=13.390..1,451.882 rows=1,029 loops=1)

  • Buffers: shared hit=11,798 read=1,221 dirtied=8
  • I/O Timings: read=1,417.462
14. 2.486 1,451.249 ↑ 6.9 1,029 1

Nested Loop (cost=4,618.90..9,985.46 rows=7,077 width=54) (actual time=13.388..1,451.249 rows=1,029 loops=1)

  • Buffers: shared hit=11,798 read=1,221 dirtied=8
  • I/O Timings: read=1,417.462
15. 3.606 8.549 ↑ 1.7 3,013 1

HashAggregate (cost=4,618.48..4,670.82 rows=5,234 width=8) (actual time=6.656..8.549 rows=3,013 loops=1)

  • Group Key: resource_milestone_events_3.issue_id
  • Buffers: shared hit=2,987
16. 4.943 4.943 ↑ 1.0 5,198 1

Index Scan using index_resource_milestone_events_on_milestone_id_and_add_action on public.resource_milestone_events resource_milestone_events_3 (cost=0.43..4,605.37 rows=5,244 width=8) (actual time=0.044..4.943 rows=5,198 loops=1)

  • Index Cond: (resource_milestone_events_3.milestone_id = 1,233,752)
  • Filter: (resource_milestone_events_3.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,987
17. 1,440.214 1,440.214 ↓ 0.0 0 3,013

Index Scan using index_resource_weight_events_on_issue_id_and_weight on public.resource_weight_events (cost=0.42..1.00 rows=1 width=20) (actual time=0.445..0.478 rows=0 loops=3,013)

  • Index Cond: (resource_weight_events.issue_id = resource_milestone_events_3.issue_id)
  • Filter: (resource_weight_events.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=8,811 read=1,221 dirtied=8
  • I/O Timings: read=1,417.462