explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q23q

Settings
# exclusive inclusive rows x rows loops node
1. 3.752 5,412.242 ↑ 2.3 8,391 1

Sort (cost=48,549.93..48,598.88 rows=19,577 width=58) (actual time=5,411.534..5,412.242 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=5,189.578
2. 20.091 5,408.490 ↑ 2.3 8,391 1

HashAggregate (cost=46,958.63..47,154.40 rows=19,577 width=58) (actual time=5,405.302..5,408.490 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=5,189.578
3. 2.912 5,388.399 ↑ 2.3 8,391 1

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

  • Buffers: shared hit=44,837 read=4,104 dirtied=526
  • I/O Timings: read=5,189.578
4. 10.110 2,349.371 ↑ 1.1 6,985 1

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

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

HashAggregate (cost=4,324.84..4,371.15 rows=4,631 width=8) (actual time=59.965..63.863 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=34.683
6. 56.810 56.810 ↓ 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=7.615..56.810 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=34.683
7. 2,275.398 2,275.398 ↑ 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.768..0.783 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=2,198.869
8. 0.436 1,578.473 ↑ 12.6 422 1

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

  • Buffers: shared hit=12,776 read=1,187 dirtied=13
  • I/O Timings: read=1,536.606
9. 2.781 1,578.037 ↑ 12.6 422 1

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

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

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

  • Group Key: resource_milestone_events_2.issue_id
  • Buffers: shared hit=4,804
11. 5.041 5.041 ↓ 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.026..5.041 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. 1,566.334 1,566.334 ↓ 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.520..0.539 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=1,536.606
13. 1.167 1,457.643 ↑ 6.4 984 1

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

  • Buffers: shared hit=13,287 read=1,180 dirtied=20
  • I/O Timings: read=1,419.420
14. 2.401 1,456.476 ↑ 6.4 984 1

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

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

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

  • Group Key: resource_milestone_events_3.issue_id
  • Buffers: shared hit=4,804
16. 5.922 5.922 ↓ 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.031..5.922 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. 1,444.282 1,444.282 ↓ 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.466..0.497 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=1,419.420