explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2kSn

Settings
# exclusive inclusive rows x rows loops node
1. 5.925 273.977 ↑ 2.3 8,391 1

Sort (cost=48,549.93..48,598.88 rows=19,577 width=58) (actual time=272.353..273.977 rows=8,391 loops=1)

  • Sort Key: resource_milestone_events.created_at
  • Sort Method: quicksort Memory: 1,040kB
  • Buffers: shared hit=44,837 read=4,104 dirtied=526
  • I/O Timings: read=128.731
2. 13.983 268.052 ↑ 2.3 8,391 1

HashAggregate (cost=46,958.63..47,154.40 rows=19,577 width=58) (actual time=263.963..268.052 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=44,837 read=4,104 dirtied=526
  • I/O Timings: read=128.731
3. 1.812 254.069 ↑ 2.3 8,391 1

Append (cost=4,325.26..46,713.92 rows=19,577 width=58) (actual time=18.866..254.069 rows=8,391 loops=1)

  • Buffers: shared hit=44,837 read=4,104 dirtied=526
  • I/O Timings: read=128.731
4. 5.207 118.414 ↑ 1.1 6,985 1

Nested Loop (cost=4,325.26..20,024.72 rows=7,980 width=58) (actual time=18.864..118.414 rows=6,985 loops=1)

  • Buffers: shared hit=18,774 read=1,737 dirtied=493
  • I/O Timings: read=56.975
5. 3.817 20.215 ↑ 1.6 2,906 1

HashAggregate (cost=4,324.84..4,371.15 rows=4,631 width=8) (actual time=18.485..20.215 rows=2,906 loops=1)

  • Group Key: resource_milestone_events_1.issue_id
  • Buffers: shared hit=4,786 read=21 dirtied=393
  • I/O Timings: read=1.632
6. 16.398 16.398 ↓ 1.0 4,819 1

Index Scan using index_resource_milestone_events_on_milestone_id on public.resource_milestone_events resource_milestone_events_1 (cost=0.43..4,313.24 rows=4,639 width=8) (actual time=0.699..16.398 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) AND (resource_milestone_events_1.action = 1))
  • Rows Removed by Filter: 71
  • Buffers: shared hit=4,786 read=21 dirtied=393
  • I/O Timings: read=1.632
7. 92.992 92.992 ↑ 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.027..0.032 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=100
  • I/O Timings: read=55.343
8. 0.237 69.839 ↑ 12.6 422 1

Subquery Scan on *SELECT* 2 (cost=4,325.26..17,084.81 rows=5,334 width=58) (actual time=11.960..69.839 rows=422 loops=1)

  • Buffers: shared hit=12,776 read=1,187 dirtied=13
  • I/O Timings: read=38.809
9. 1.937 69.602 ↑ 12.6 422 1

Nested Loop (cost=4,325.26..17,018.14 rows=5,334 width=52) (actual time=11.956..69.602 rows=422 loops=1)

  • Buffers: shared hit=12,776 read=1,187 dirtied=13
  • I/O Timings: read=38.809
10. 4.025 12.451 ↑ 1.6 2,906 1

HashAggregate (cost=4,324.84..4,371.15 rows=4,631 width=8) (actual time=10.812..12.451 rows=2,906 loops=1)

  • Group Key: resource_milestone_events_2.issue_id
  • Buffers: shared hit=4,804
11. 8.426 8.426 ↓ 1.0 4,819 1

Index Scan using index_resource_milestone_events_on_milestone_id on public.resource_milestone_events resource_milestone_events_2 (cost=0.43..4,313.24 rows=4,639 width=8) (actual time=0.033..8.426 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) AND (resource_milestone_events_2.action = 1))
  • Rows Removed by Filter: 71
  • Buffers: shared hit=4,804
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=38.809
13. 0.491 64.004 ↑ 6.4 984 1

Subquery Scan on *SELECT* 3 (cost=4,325.26..9,426.69 rows=6,263 width=58) (actual time=11.387..64.004 rows=984 loops=1)

  • Buffers: shared hit=13,287 read=1,180 dirtied=20
  • I/O Timings: read=32.947
14. 1.496 63.513 ↑ 6.4 984 1

Nested Loop (cost=4,325.26..9,348.41 rows=6,263 width=54) (actual time=11.385..63.513 rows=984 loops=1)

  • Buffers: shared hit=13,287 read=1,180 dirtied=20
  • I/O Timings: read=32.947
15. 3.860 12.615 ↑ 1.6 2,906 1

HashAggregate (cost=4,324.84..4,371.15 rows=4,631 width=8) (actual time=11.000..12.615 rows=2,906 loops=1)

  • Group Key: resource_milestone_events_3.issue_id
  • Buffers: shared hit=4,804
16. 8.755 8.755 ↓ 1.0 4,819 1

Index Scan using index_resource_milestone_events_on_milestone_id on public.resource_milestone_events resource_milestone_events_3 (cost=0.43..4,313.24 rows=4,639 width=8) (actual time=0.053..8.755 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) AND (resource_milestone_events_3.action = 1))
  • Rows Removed by Filter: 71
  • Buffers: shared hit=4,804
17. 49.402 49.402 ↓ 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.016..0.017 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=32.947