explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VdIA : Optimization for: plan #nVGk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9.424 732,311.784 ↓ 9,982.0 9,982 1

Sort (cost=3,504.66..3,504.66 rows=1 width=4) (actual time=732,310.500..732,311.784 rows=9,982 loops=1)

  • Sort Key: notes.id
  • Sort Method: quicksort Memory: 852kB
  • Buffers: shared hit=464171 read=1116175 dirtied=7
  • I/O Timings: read=355138.861
2. 7.313 732,302.360 ↓ 9,982.0 9,982 1

Hash Left Join (cost=12.84..3,504.65 rows=1 width=4) (actual time=12.690..732,302.360 rows=9,982 loops=1)

  • Hash Cond: (notes.id = epic_user_mentions.note_id)
  • Filter: (epic_user_mentions.epic_id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=464171 read=1116175 dirtied=7
  • I/O Timings: read=355138.861
3. 12.242 732,284.805 ↓ 302.5 9,982 1

Merge Join (cost=1.87..3,493.55 rows=33 width=4) (actual time=2.393..732,284.805 rows=9,982 loops=1)

  • Buffers: shared hit=464065 read=1116169 dirtied=2
  • I/O Timings: read=355133.190
4. 732,065.501 732,065.501 ↑ 1.1 10,000 1

Index Scan using index_notes_on_noteable_id_and_noteable_type on public.notes (cost=0.57..4,884,151.37 rows=11,441 width=8) (actual time=1.465..732,065.501 rows=10,000 loops=1)

  • Index Cond: ((notes.noteable_type)::text = 'Epic'::text)
  • Filter: ((notes.note ~~ '%@%'::text) AND (notes.id >= 67972855) AND (notes.id < 268738059))
  • Rows Removed by Filter: 149884
  • Buffers: shared hit=463956 read=1116061
  • I/O Timings: read=354934.294
5. 207.062 207.062 ↓ 1.4 22,698 1

Index Only Scan using epics_pkey on public.epics (cost=0.29..463.48 rows=16,346 width=4) (actual time=0.920..207.062 rows=22,698 loops=1)

  • Heap Fetches: 187
  • Buffers: shared hit=109 read=108 dirtied=2
  • I/O Timings: read=198.896
6. 0.094 10.242 ↑ 1.4 182 1

Hash (cost=7.87..7.87 rows=248 width=8) (actual time=10.242..10.242 rows=182 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=106 read=6 dirtied=5
  • I/O Timings: read=5.671
7. 10.148 10.148 ↓ 1.0 252 1

Index Only Scan using epic_user_mentions_on_epic_id_and_note_id_index on public.epic_user_mentions (cost=0.14..7.87 rows=248 width=8) (actual time=2.937..10.148 rows=252 loops=1)

  • Heap Fetches: 253
  • Buffers: shared hit=106 read=6 dirtied=5
  • I/O Timings: read=5.671