explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pcyk

Settings
# exclusive inclusive rows x rows loops node
1. 1.508 13,831.675 ↑ 2.5 8,762 1

Limit (cost=54,262.46..54,317.99 rows=22,211 width=58) (actual time=13,828.915..13,831.675 rows=8,762 loops=1)

  • Buffers: shared hit=41,713 read=9,940 dirtied=439
  • I/O Timings: read=13,483.272
2. 5.330 13,830.167 ↑ 2.5 8,762 1

Sort (cost=54,262.46..54,317.99 rows=22,211 width=58) (actual time=13,828.913..13,830.167 rows=8,762 loops=1)

  • Sort Key: resource_milestone_events.created_at
  • Sort Method: quicksort Memory: 1,069kB
  • Buffers: shared hit=41,713 read=9,940 dirtied=439
  • I/O Timings: read=13,483.272
3. 24.839 13,824.837 ↑ 2.5 8,762 1

HashAggregate (cost=52,436.83..52,658.94 rows=22,211 width=58) (actual time=13,821.608..13,824.837 rows=8,762 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=41,710 read=9,940 dirtied=439
  • I/O Timings: read=13,483.272
4. 3.116 13,799.998 ↑ 2.5 8,762 1

Append (cost=4,796.84..52,159.19 rows=22,211 width=58) (actual time=4,842.174..13,799.998 rows=8,762 loops=1)

  • Buffers: shared hit=41,710 read=9,940 dirtied=439
  • I/O Timings: read=13,483.272
5. 10.277 10,218.435 ↑ 1.3 7,170 1

Nested Loop (cost=4,796.84..22,132.39 rows=8,993 width=58) (actual time=4,842.172..10,218.435 rows=7,170 loops=1)

  • Buffers: shared hit=14,215 read=7,258 dirtied=426
  • I/O Timings: read=10,005.887
6. 12.824 4,838.598 ↑ 1.7 3,020 1

HashAggregate (cost=4,796.41..4,848.74 rows=5,233 width=8) (actual time=4,834.942..4,838.598 rows=3,020 loops=1)

  • Group Key: resource_milestone_events_1.issue_id
  • Buffers: shared hit=2,174 read=3,069 dirtied=353
  • I/O Timings: read=4,717.000
7. 4,825.774 4,825.774 ↓ 1.0 5,254 1

Index Scan using index_resource_milestone_events_on_milestone_id on public.resource_milestone_events resource_milestone_events_1 (cost=0.43..4,783.31 rows=5,243 width=8) (actual time=11.451..4,825.774 rows=5,254 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: 78
  • Buffers: shared hit=2,174 read=3,069 dirtied=353
  • I/O Timings: read=4,717.000
8. 5,369.560 5,369.560 ↑ 1.0 2 3,020

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.880..1.778 rows=2 loops=3,020)

  • 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=12,041 read=4,189 dirtied=73
  • I/O Timings: read=5,288.887
9. 0.675 1,983.551 ↑ 10.9 562 1

Subquery Scan on *SELECT* 2 (cost=4,796.84..19,563.96 rows=6,143 width=58) (actual time=42.200..1,983.551 rows=562 loops=1)

  • Buffers: shared hit=13,423 read=1,460 dirtied=5
  • I/O Timings: read=1,925.696
10. 4.155 1,982.876 ↑ 10.9 562 1

Nested Loop (cost=4,796.84..19,487.17 rows=6,143 width=52) (actual time=42.197..1,982.876 rows=562 loops=1)

  • Buffers: shared hit=13,423 read=1,460 dirtied=5
  • I/O Timings: read=1,925.696
11. 5.413 12.701 ↑ 1.7 3,020 1

HashAggregate (cost=4,796.41..4,848.74 rows=5,233 width=8) (actual time=9.463..12.701 rows=3,020 loops=1)

  • Group Key: resource_milestone_events_2.issue_id
  • Buffers: shared hit=5,240
12. 7.288 7.288 ↓ 1.0 5,254 1

Index Scan using index_resource_milestone_events_on_milestone_id on public.resource_milestone_events resource_milestone_events_2 (cost=0.43..4,783.31 rows=5,243 width=8) (actual time=0.227..7.288 rows=5,254 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: 78
  • Buffers: shared hit=5,240
13. 1,966.020 1,966.020 ↓ 0.0 0 3,020

Index Scan using index_resource_state_events_on_issue_id_and_created_at on public.resource_state_events (cost=0.43..2.78 rows=1 width=18) (actual time=0.623..0.651 rows=0 loops=3,020)

  • 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,183 read=1,460 dirtied=5
  • I/O Timings: read=1,925.696
14. 0.843 1,594.896 ↑ 6.9 1,030 1

Subquery Scan on *SELECT* 3 (cost=4,796.83..10,261.86 rows=7,075 width=58) (actual time=16.617..1,594.896 rows=1,030 loops=1)

  • Buffers: shared hit=14,072 read=1,222 dirtied=8
  • I/O Timings: read=1,551.689
15. 3.216 1,594.053 ↑ 6.9 1,030 1

Nested Loop (cost=4,796.83..10,173.42 rows=7,075 width=54) (actual time=16.615..1,594.053 rows=1,030 loops=1)

  • Buffers: shared hit=14,072 read=1,222 dirtied=8
  • I/O Timings: read=1,551.689
16. 4.499 11.377 ↑ 1.7 3,020 1

HashAggregate (cost=4,796.41..4,848.74 rows=5,233 width=8) (actual time=9.061..11.377 rows=3,020 loops=1)

  • Group Key: resource_milestone_events_3.issue_id
  • Buffers: shared hit=5,240
17. 6.878 6.878 ↓ 1.0 5,254 1

Index Scan using index_resource_milestone_events_on_milestone_id on public.resource_milestone_events resource_milestone_events_3 (cost=0.43..4,783.31 rows=5,243 width=8) (actual time=0.047..6.878 rows=5,254 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: 78
  • Buffers: shared hit=5,240
18. 1,579.460 1,579.460 ↓ 0.0 0 3,020

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.484..0.523 rows=0 loops=3,020)

  • 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,832 read=1,222 dirtied=8
  • I/O Timings: read=1,551.689