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=27,964,635 read=3,437,156 dirtied=115,535 written=51
  • I/O Timings: read=907,009.625 write=2.278
  • - planning: 33.080 ms
  • - execution: 16.466 min
  • - hits: 27,964,635 (~213.40 GiB) from the buffer pool
  • - reads: 3,437,156 (~26.20 GiB) from the OS file cache, including disk I/O
  • - dirtied: 115,535 (~902.60 MiB)
  • - writes: 51 (~408.00 KiB)
  • - reads: 79,583 (~621.70 MiB)
  • - writes: 79,583 (~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=26,628,203 read=3,403,165 dirtied=115,280 written=51
  • I/O Timings: read=872,615.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: 636,440kB
  • Buffers: shared hit=5,237,643 read=2,973,158 dirtied=57,336 written=51
  • I/O Timings: read=545,169.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=5,237,638 read=2,973,158 dirtied=57,336 written=51
  • I/O Timings: read=545,169.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: 11,686,081
  • Buffers: shared hit=21,390,560 read=430,007 dirtied=57,944
  • I/O Timings: read=327,445.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=1,336,432 read=33,991 dirtied=255
  • I/O Timings: read=34,394.037
  • - I/O read: 15.117 min
  • - I/O write: 2.278 ms