explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ugmp

Settings
# exclusive inclusive rows x rows loops node
1. 71,603.657 73,949.698 ↓ 0.0 0 1

Update on notes (cost=73,609.43..73,617.04 rows=1 width=2,506) (actual time=73,949.698..73,949.698 rows=0 loops=1)

  • Buffers: shared hit=302249 read=125882 dirtied=99069
  • I/O Timings: read=71747.281
2. 7.352 2,346.041 ↓ 1,126.0 1,126 1

Nested Loop (cost=73,609.43..73,617.04 rows=1 width=2,506) (actual time=355.529..2,346.041 rows=1,126 loops=1)

  • Buffers: shared hit=73424 read=2581 dirtied=4
  • I/O Timings: read=1974.367
3. 1.944 329.689 ↓ 1,000.0 1,000 1

HashAggregate (cost=73,608.74..73,608.75 rows=1 width=106) (actual time=328.236..329.689 rows=1,000 loops=1)

  • Group Key: ("ANY_subquery".discussion_id)::text
  • Buffers: shared hit=69203
4. 0.215 327.745 ↓ 1,000.0 1,000 1

Subquery Scan on ANY_subquery (cost=73,608.72..73,608.74 rows=1 width=106) (actual time=327.202..327.745 rows=1,000 loops=1)

  • Buffers: shared hit=69203
5. 0.080 327.530 ↓ 1,000.0 1,000 1

Limit (cost=73,608.72..73,608.73 rows=1 width=41) (actual time=327.187..327.530 rows=1,000 loops=1)

  • Buffers: shared hit=69203
6. 0.167 327.450 ↓ 1,000.0 1,000 1

Unique (cost=73,608.72..73,608.73 rows=1 width=41) (actual time=327.185..327.450 rows=1,000 loops=1)

  • Buffers: shared hit=69203
7. 5.711 327.283 ↓ 1,126.0 1,126 1

Sort (cost=73,608.72..73,608.72 rows=1 width=41) (actual time=327.184..327.283 rows=1,126 loops=1)

  • Sort Key: notes_1.discussion_id
  • Sort Method: quicksort Memory: 529kB
  • Buffers: shared hit=69203
8. 0.546 321.572 ↓ 4,313.0 4,313 1

Nested Loop (cost=73,374.30..73,608.71 rows=1 width=41) (actual time=313.540..321.572 rows=4,313 loops=1)

  • Buffers: shared hit=69200
9. 0.028 313.564 ↓ 2.8 182 1

Unique (cost=73,373.73..73,374.06 rows=65 width=4) (actual time=313.506..313.564 rows=182 loops=1)

  • Buffers: shared hit=65016
10. 0.281 313.536 ↓ 3.4 224 1

Sort (cost=73,373.73..73,373.90 rows=65 width=4) (actual time=313.505..313.536 rows=224 loops=1)

  • Sort Key: notes_2.noteable_id
  • Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=65016
11. 40.433 313.255 ↓ 3.4 224 1

Hash Join (cost=67,206.07..73,371.78 rows=65 width=4) (actual time=265.247..313.255 rows=224 loops=1)

  • Hash Cond: (system_note_metadata.note_id = notes_2.id)
  • Buffers: shared hit=65013
12. 73.116 73.116 ↓ 1.0 233,629 1

Index Only Scan using system_note_metadata_note_id_idx on system_note_metadata (cost=0.42..5,310.11 rows=228,098 width=4) (actual time=0.129..73.116 rows=233,629 loops=1)

  • Heap Fetches: 14034
  • Buffers: shared hit=9391
13. 14.581 199.706 ↓ 1.2 52,450 1

Hash (cost=66,671.69..66,671.69 rows=42,717 width=8) (actual time=199.706..199.706 rows=52,450 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2561kB
  • Buffers: shared hit=55622
14. 185.125 185.125 ↓ 1.2 52,450 1

Index Scan using index_notes_on_noteable_type on notes notes_2 (cost=0.57..66,671.69 rows=42,717 width=8) (actual time=0.110..185.125 rows=52,450 loops=1)

  • Index Cond: ((noteable_type)::text = 'Epic'::text)
  • Filter: system
  • Rows Removed by Filter: 6375
  • Buffers: shared hit=55622
15. 7.462 7.462 ↓ 24.0 24 182

Index Scan using index_notes_on_noteable_id_and_noteable_type on notes notes_1 (cost=0.57..3.59 rows=1 width=45) (actual time=0.006..0.041 rows=24 loops=182)

  • Index Cond: ((noteable_id = notes_2.noteable_id) AND ((noteable_type)::text = 'Epic'::text))
  • Buffers: shared hit=4184
16. 2,009.000 2,009.000 ↑ 1.0 1 1,000

Index Scan using index_notes_on_discussion_id on notes (cost=0.70..8.27 rows=1 width=2,446) (actual time=1.652..2.009 rows=1 loops=1,000)

  • Index Cond: ((discussion_id)::text = ("ANY_subquery".discussion_id)::text)
  • Filter: ((noteable_type)::text = 'Epic'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4221 read=2581 dirtied=4
  • I/O Timings: read=1974.367
Planning time : 51.131 ms
Execution time : 73,950.235 ms