explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RJ5Z : pending todos

Settings
# exclusive inclusive rows x rows loops node
1. 330.589 987,905.182 ↓ 142.5 277,801 1

Nested Loop (cost=2,888,832.54..4,964,497.03 rows=1,949 width=104) (actual time=582,146.477..987,905.182 rows=277,801 loops=1)

  • Buffers: shared hit=27964635 read=3437156 dirtied=115535 written=51
  • I/O Timings: read=907009.625 write=2.278
  • - planning: 33.080 ms
  • - execution: 16.466 min
  • - hits: 27964635 (~213.40 GiB) from the buffer pool
  • - reads: 3437156 (~26.20 GiB) from the OS file cache, including disk I/O
  • - dirtied: 115535 (~902.60 MiB)
  • - writes: 51 (~408.00 KiB)
  • - reads: 79583 (~621.70 MiB)
  • - writes: 79583 (~621.70 MiB)
2. 8,045.315 951,842.273 ↓ 151.7 343,580 1

Merge Join (cost=2,888,832.11..4,963,174.37 rows=2,265 width=104) (actual time=580,614.340..951,842.273 rows=343,580 loops=1)

  • Buffers: shared hit=26628203 read=3403165 dirtied=115280 written=51
  • I/O Timings: read=872615.588 write=2.278
3. 12,874.053 584,358.602 ↓ 1.0 8,209,371 1

Sort (cost=2,888,831.54..2,909,295.62 rows=8,185,631 width=104) (actual time=580,601.161..584,358.602 rows=8,209,371 loops=1)

  • Sort Key: todos.user_id, todos.project_id
  • Sort Method: external merge Disk: 636440kB
  • Buffers: shared hit=5237643 read=2973158 dirtied=57336 written=51
  • I/O Timings: read=545169.865 write=2.278
4. 571,484.549 571,484.549 ↓ 1.0 8,209,371 1

Index Scan using index_todos_on_user_id_and_id_pending on public.todos (cost=0.43..1,053,623.29 rows=8,185,631 width=104) (actual time=5.577..571,484.549 rows=8,209,371 loops=1)

  • Buffers: shared hit=5237638 read=2973158 dirtied=57336 written=51
  • I/O Timings: read=545169.865 write=2.278
5. 359,438.356 359,438.356 ↓ 1.0 57,580,695 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.56..1,729,116.61 rows=57,484,744 width=8) (actual time=4.621..359,438.356 rows=57,580,695 loops=1)

  • Heap Fetches: 11686081
  • Buffers: shared hit=21390560 read=430007 dirtied=57944
  • I/O Timings: read=327445.723
6. 35,732.320 35,732.320 ↑ 1.0 1 343,580

Index Scan using projects_pkey on public.projects (cost=0.43..0.57 rows=1 width=4) (actual time=0.104..0.104 rows=1 loops=343,580)

  • Index Cond: (projects.id = todos.project_id)
  • Filter: (projects.visibility_level = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1336432 read=33991 dirtied=255
  • I/O Timings: read=34394.037
  • - I/O read: 15.117 min
  • - I/O write: 2.278 ms