explain.depesz.com

PostgreSQL's explain analyze made readable

Result: saYA

Settings
# exclusive inclusive rows x rows loops node
1. 6.134 286.940 ↑ 2.3 8,391 1

Sort (cost=48,065.99..48,114.93 rows=19,577 width=58) (actual time=285.472..286.940 rows=8,391 loops=1)

  • Sort Key: resource_milestone_events.created_at
  • Sort Method: quicksort Memory: 1,040kB
  • Buffers: shared hit=38,704 read=4,099 dirtied=526
  • I/O Timings: read=138.563
2. 15.629 280.806 ↑ 2.3 8,391 1

HashAggregate (cost=46,474.68..46,670.45 rows=19,577 width=58) (actual time=276.006..280.806 rows=8,391 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=38,701 read=4,099 dirtied=526
  • I/O Timings: read=138.563
3. 1.831 265.177 ↑ 2.3 8,391 1

Append (cost=4,163.95..46,229.97 rows=19,577 width=58) (actual time=16.837..265.177 rows=8,391 loops=1)

  • Buffers: shared hit=38,701 read=4,099 dirtied=526
  • I/O Timings: read=138.563
4. 5.568 125.822 ↑ 1.1 6,985 1

Nested Loop (cost=4,163.95..19,863.41 rows=7,980 width=58) (actual time=16.834..125.822 rows=6,985 loops=1)

  • Buffers: shared hit=16,732 read=1,732 dirtied=493
  • I/O Timings: read=61.887
5. 4.285 18.544 ↑ 1.6 2,906 1

HashAggregate (cost=4,163.52..4,209.83 rows=4,631 width=8) (actual time=16.500..18.544 rows=2,906 loops=1)

  • Group Key: resource_milestone_events_1.issue_id
  • Buffers: shared hit=2,744 read=16 dirtied=391
  • I/O Timings: read=0.988
6. 14.259 14.259 ↓ 1.0 4,819 1

Index Scan using add_events on public.resource_milestone_events resource_milestone_events_1 (cost=0.43..4,151.92 rows=4,639 width=8) (actual time=0.391..14.259 rows=4,819 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,744 read=16 dirtied=391
  • I/O Timings: read=0.988
7. 101.710 101.710 ↑ 1.0 2 2,906

Index Scan using index_resource_milestone_events_on_issue_id on public.resource_milestone_events (cost=0.43..3.35 rows=2 width=26) (actual time=0.030..0.035 rows=2 loops=2,906)

  • 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=13,988 read=1,716 dirtied=102
  • I/O Timings: read=60.899
8. 0.279 70.024 ↑ 12.6 422 1

Subquery Scan on *SELECT* 2 (cost=4,163.95..16,923.50 rows=5,334 width=58) (actual time=10.643..70.024 rows=422 loops=1)

  • Buffers: shared hit=10,729 read=1,187 dirtied=13
  • I/O Timings: read=39.100
9. 3.355 69.745 ↑ 12.6 422 1

Nested Loop (cost=4,163.95..16,856.82 rows=5,334 width=52) (actual time=10.638..69.745 rows=422 loops=1)

  • Buffers: shared hit=10,729 read=1,187 dirtied=13
  • I/O Timings: read=39.100
10. 4.143 11.176 ↑ 1.6 2,906 1

HashAggregate (cost=4,163.52..4,209.83 rows=4,631 width=8) (actual time=9.424..11.176 rows=2,906 loops=1)

  • Group Key: resource_milestone_events_2.issue_id
  • Buffers: shared hit=2,757
11. 7.033 7.033 ↓ 1.0 4,819 1

Index Scan using add_events on public.resource_milestone_events resource_milestone_events_2 (cost=0.43..4,151.92 rows=4,639 width=8) (actual time=0.046..7.033 rows=4,819 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,757
12. 55.214 55.214 ↓ 0.0 0 2,906

Index Scan using index_resource_state_events_on_issue_id_and_created_at on public.resource_state_events (cost=0.43..2.71 rows=1 width=18) (actual time=0.019..0.019 rows=0 loops=2,906)

  • 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=7,972 read=1,187 dirtied=13
  • I/O Timings: read=39.100
13. 0.551 67.500 ↑ 6.4 984 1

Subquery Scan on *SELECT* 3 (cost=4,163.94..9,265.38 rows=6,263 width=58) (actual time=8.445..67.500 rows=984 loops=1)

  • Buffers: shared hit=11,240 read=1,180 dirtied=20
  • I/O Timings: read=37.576
14. 1.943 66.949 ↑ 6.4 984 1

Nested Loop (cost=4,163.94..9,187.09 rows=6,263 width=54) (actual time=8.441..66.949 rows=984 loops=1)

  • Buffers: shared hit=11,240 read=1,180 dirtied=20
  • I/O Timings: read=37.576
15. 3.827 9.792 ↑ 1.6 2,906 1

HashAggregate (cost=4,163.52..4,209.83 rows=4,631 width=8) (actual time=8.031..9.792 rows=2,906 loops=1)

  • Group Key: resource_milestone_events_3.issue_id
  • Buffers: shared hit=2,757
16. 5.965 5.965 ↓ 1.0 4,819 1

Index Scan using add_events on public.resource_milestone_events resource_milestone_events_3 (cost=0.43..4,151.92 rows=4,639 width=8) (actual time=0.039..5.965 rows=4,819 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,757
17. 55.214 55.214 ↓ 0.0 0 2,906

Index Scan using index_resource_weight_events_on_issue_id_and_created_at on public.resource_weight_events (cost=0.42..1.05 rows=1 width=20) (actual time=0.018..0.019 rows=0 loops=2,906)

  • Index Cond: ((resource_weight_events.issue_id = resource_milestone_events_3.issue_id) AND (resource_weight_events.created_at <= '2020-08-17 23:59:59.999999+00'::timestamp with time zone))
  • Buffers: shared hit=8,483 read=1,180 dirtied=20
  • I/O Timings: read=37.576