explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6LiX

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 252.504 ↓ 20.0 20 1

Limit (cost=3,132.39..3,132.40 rows=1 width=1,261) (actual time=252.498..252.504 rows=20 loops=1)

  • Buffers: shared hit=35,467 read=118
  • I/O Timings: read=184.334
2.          

CTE blockers

3. 0.152 182.010 ↓ 12.7 89 1

GroupAggregate (cost=3,107.01..3,107.13 rows=7 width=12) (actual time=181.828..182.010 rows=89 loops=1)

  • Group Key: issues_1.id
  • Buffers: shared hit=35,048 read=92
  • I/O Timings: read=118.312
4. 0.101 181.858 ↓ 14.4 101 1

Sort (cost=3,107.01..3,107.02 rows=7 width=4) (actual time=181.817..181.858 rows=101 loops=1)

  • Sort Key: issues_1.id
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=35,048 read=92
  • I/O Timings: read=118.312
5. 3.658 181.757 ↓ 14.4 101 1

Hash Join (cost=2,663.97..3,106.91 rows=7 width=4) (actual time=167.645..181.757 rows=101 loops=1)

  • Hash Cond: (issue_links2_1.target_id = issues_1.id)
  • Buffers: shared hit=35,048 read=92
  • I/O Timings: read=118.312
6. 1.975 10.828 ↑ 1.0 10,513 1

Append (cost=0.29..415.62 rows=10,513 width=4) (actual time=0.023..10.828 rows=10,513 loops=1)

  • Buffers: shared hit=7,627
7. 8.853 8.853 ↑ 1.0 10,513 1

Index Scan using issue_links2_1_target_id_idx on issue_links2_1 (cost=0.29..363.06 rows=10,513 width=4) (actual time=0.021..8.853 rows=10,513 loops=1)

  • Filter: (link_type = 1)
  • Buffers: shared hit=7,627
8. 13.835 167.271 ↓ 1.0 31,124 1

Hash (cost=2,275.40..2,275.40 rows=31,063 width=4) (actual time=167.270..167.271 rows=31,124 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,351kB
  • Buffers: shared hit=27,421 read=92
  • I/O Timings: read=118.312
9. 153.436 153.436 ↓ 1.0 31,124 1

Index Only Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on issues issues_1 (cost=0.56..2,275.40 rows=31,063 width=4) (actual time=2.074..153.436 rows=31,124 loops=1)

  • Index Cond: ((project_id = 278,964) AND (state_id = 1))
  • Heap Fetches: 8,167
  • Buffers: shared hit=27,421 read=92
  • I/O Timings: read=118.312
10. 0.435 252.499 ↓ 20.0 20 1

Sort (cost=25.26..25.27 rows=1 width=1,261) (actual time=252.497..252.499 rows=20 loops=1)

  • Sort Key: blockers.blocking_count DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 55kB
  • Buffers: shared hit=35,467 read=118
  • I/O Timings: read=184.334
11. 0.195 252.064 ↓ 89.0 89 1

Nested Loop (cost=0.56..25.25 rows=1 width=1,261) (actual time=181.897..252.064 rows=89 loops=1)

  • Buffers: shared hit=35,467 read=118
  • I/O Timings: read=184.334
12. 182.093 182.093 ↓ 12.7 89 1

CTE Scan on blockers (cost=0.00..0.14 rows=7 width=12) (actual time=181.830..182.093 rows=89 loops=1)

  • Buffers: shared hit=35,048 read=92
  • I/O Timings: read=118.312
13. 69.776 69.776 ↑ 1.0 1 89

Index Scan using issues_pkey on issues (cost=0.56..3.59 rows=1 width=1,253) (actual time=0.784..0.784 rows=1 loops=89)

  • Index Cond: (id = blockers.id)
  • Filter: ((project_id = 278,964) AND (state_id = 1))
  • Buffers: shared hit=419 read=26
  • I/O Timings: read=66.022
Planning time : 1.128 ms
Execution time : 252.736 ms