explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nVGk

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 22.315 1,208,554.206 ↓ 9,982.0 9,982 1

Nested Loop (cost=4,884,999.26..4,885,005.15 rows=1 width=4) (actual time=1,197,308.698..1,208,554.206 rows=9,982 loops=1)

  • Buffers: shared hit=485520 read=1164918 dirtied=2034 written=41250
  • I/O Timings: read=837452.978 write=7903.005
2. 12.287 1,208,461.891 ↓ 10,000.0 10,000 1

Nested Loop (cost=4,884,998.98..4,885,003.02 rows=1 width=8) (actual time=1,197,308.439..1,208,461.891 rows=10,000 loops=1)

  • Buffers: shared hit=465510 read=1164815 dirtied=2034 written=41250
  • I/O Timings: read=837450.047 write=7903.005
3. 13.523 1,197,309.604 ↓ 10,000.0 10,000 1

HashAggregate (cost=4,884,998.41..4,884,998.42 rows=1 width=4) (actual time=1,197,298.967..1,197,309.604 rows=10,000 loops=1)

  • Group Key: notes_1.id
  • Buffers: shared hit=433605 read=1146659 dirtied=2034 written=41250
  • I/O Timings: read=826508.334 write=7903.005
4. 1.895 1,197,296.081 ↓ 10,000.0 10,000 1

Merge Join (cost=4,884,940.33..4,884,998.40 rows=1 width=4) (actual time=1,197,292.915..1,197,296.081 rows=10,000 loops=1)

  • Filter: (epic_user_mentions.epic_id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=433605 read=1146659 dirtied=2034 written=41250
  • I/O Timings: read=826508.334 write=7903.005
5. 25.308 1,197,292.676 ↑ 1.1 10,000 1

Sort (cost=4,884,922.60..4,884,951.21 rows=11,441 width=4) (actual time=1,197,291.328..1,197,292.676 rows=10,000 loops=1)

  • Sort Key: notes_1.id
  • Sort Method: quicksort Memory: 853kB
  • Buffers: shared hit=433498 read=1146654 dirtied=2034 written=41250
  • I/O Timings: read=826507.221 write=7903.005
6. 1,197,267.368 1,197,267.368 ↑ 1.1 10,000 1

Index Scan using index_notes_on_noteable_id_and_noteable_type on public.notes notes_1 (cost=0.57..4,884,151.37 rows=11,441 width=4) (actual time=26.007..1,197,267.368 rows=10,000 loops=1)

  • Index Cond: ((notes_1.noteable_type)::text = 'Epic'::text)
  • Filter: ((notes_1.note ~~ '%@%'::text) AND (notes_1.id >= 67972855) AND (notes_1.id < 268738059))
  • Rows Removed by Filter: 149884
  • Buffers: shared hit=433498 read=1146654 dirtied=2034 written=41250
  • I/O Timings: read=826507.221 write=7903.005
7. 0.183 1.510 ↑ 248.0 1 1

Sort (cost=17.73..18.35 rows=248 width=8) (actual time=1.510..1.510 rows=1 loops=1)

  • Sort Key: epic_user_mentions.note_id
  • Sort Method: quicksort Memory: 36kB
  • Buffers: shared hit=107 read=5
  • I/O Timings: read=1.113
8. 1.327 1.327 ↓ 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=1.167..1.327 rows=252 loops=1)

  • Heap Fetches: 252
  • Buffers: shared hit=107 read=5
  • I/O Timings: read=1.113
9. 11,140.000 11,140.000 ↑ 1.0 1 10,000

Index Scan using notes_pkey on public.notes (cost=0.57..4.59 rows=1 width=8) (actual time=1.110..1.114 rows=1 loops=10,000)

  • Index Cond: (notes.id = notes_1.id)
  • Buffers: shared hit=31905 read=18156
  • I/O Timings: read=10941.713
10. 70.000 70.000 ↑ 1.0 1 10,000

Index Only Scan using epics_pkey on public.epics (cost=0.29..2.12 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=10,000)

  • Index Cond: (epics.id = notes.noteable_id)
  • Heap Fetches: 36
  • Buffers: shared hit=20010 read=103
  • I/O Timings: read=2.931