explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JS8c : done todos

Settings
# exclusive inclusive rows x rows loops node
1. 130.106 1,190,493.006 ↓ 60.5 429,806 1

Nested Loop (cost=8,655,469.96..10,896,513.28 rows=7,110 width=104) (actual time=784,085.036..1,190,493.006 rows=429,806 loops=1)

  • Buffers: shared hit=44957594 read=9254547 dirtied=31626
  • I/O Timings: read=1035808.916
  • - planning: 49.220 ms
  • - execution: 19.880 min
  • - hits: 44957594 (~343.00 GiB) from the buffer pool
  • - reads: 9254547 (~70.60 GiB) from the OS file cache, including disk I/O
  • - dirtied: 31626 (~247.10 MiB)
  • - writes: 0
  • - reads: 289558 (~2.20 GiB)
  • - writes: 289558 (~2.20 GiB)
2. 11,291.087 1,148,521.155 ↓ 92.1 760,759 1

Merge Join (cost=8,655,469.52..10,892,402.97 rows=8,260 width=104) (actual time=783,088.299..1,148,521.155 rows=760,759 loops=1)

  • Buffers: shared hit=41986364 read=9214817 dirtied=31569
  • I/O Timings: read=996423.621
3. 39,240.161 796,541.608 ↑ 1.0 29,846,031 1

Sort (cost=8,655,468.96..8,730,104.07 rows=29,854,045 width=104) (actual time=782,786.381..796,541.608 rows=29,846,031 loops=1)

  • Sort Key: todos.user_id, todos.project_id
  • Sort Method: external merge Disk: 2315624kB
  • Buffers: shared hit=20649833 read=8784542 dirtied=31343
  • I/O Timings: read=684401.764
4. 757,301.447 757,301.447 ↑ 1.0 29,846,031 1

Index Scan using index_todos_on_user_id_and_id_done on public.todos (cost=0.56..1,683,586.84 rows=29,854,045 width=104) (actual time=5.915..757,301.447 rows=29,846,031 loops=1)

  • Buffers: shared hit=20649828 read=8784542 dirtied=31343
  • I/O Timings: read=684401.764
5. 340,688.460 340,688.460 ↓ 1.0 57,596,717 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=6.213..340,688.460 rows=57,596,717 loops=1)

  • Heap Fetches: 11663578
  • Buffers: shared hit=21336531 read=430275 dirtied=226
  • I/O Timings: read=312021.857
6. 41,841.745 41,841.745 ↑ 1.0 1 760,759

Index Scan using projects_pkey on public.projects (cost=0.43..0.49 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=760,759)

  • Index Cond: (projects.id = todos.project_id)
  • Filter: (projects.visibility_level = 0)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2971230 read=39730 dirtied=57
  • I/O Timings: read=39385.295
  • - I/O read: 17.263 min
  • - I/O write: 0.000 ms