explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l3Vh

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 436.667 ↑ 1.0 20 1

Limit (cost=529,027.83..529,027.88 rows=20 width=1,261) (actual time=436.661..436.667 rows=20 loops=1)

  • Buffers: shared hit=236,285
2. 44.034 436.662 ↑ 1,553.2 20 1

Sort (cost=529,027.83..529,105.49 rows=31,063 width=1,261) (actual time=436.659..436.662 rows=20 loops=1)

  • Sort Key: ((SubPlan 1)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 56kB
  • Buffers: shared hit=236,285
3. 112.512 392.628 ↓ 1.0 31,124 1

Index Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on issues (cost=0.56..528,201.25 rows=31,063 width=1,261) (actual time=0.058..392.628 rows=31,124 loops=1)

  • Index Cond: ((project_id = 278,964) AND (state_id = 1))
  • Buffers: shared hit=236,285
4.          

SubPlan (for Index Scan)

5. 0.000 280.116 ↑ 1.0 1 31,124

Aggregate (cost=15.80..15.81 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=31,124)

  • Buffers: shared hit=204,739
6. 27.953 280.116 ↓ 0.0 0 31,124

Nested Loop (cost=5.01..15.80 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=31,124)

  • Join Filter: (((issue_links.source_id = issues.id) AND (issue_links.link_type = 1) AND (as_blocked.state_id = 1)) OR ((issue_links.target_id = issues.id) AND (issue_links.link_type = 2) AND (as_blocked_by.state_id = 1)))
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=204,739
7. 27.953 248.992 ↓ 0.0 0 31,124

Nested Loop (cost=4.44..12.19 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=31,124)

  • Buffers: shared hit=202,474
8. 62.248 217.868 ↓ 0.0 0 31,124

Bitmap Heap Scan on issue_links (cost=3.88..8.61 rows=1 width=14) (actual time=0.007..0.007 rows=0 loops=31,124)

  • Recheck Cond: ((source_id = issues.id) OR (target_id = issues.id))
  • Filter: (((source_id = issues.id) AND (link_type = 1)) OR ((target_id = issues.id) AND (link_type = 2)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=13,248
  • Buffers: shared hit=200,209
9. 31.124 155.620 ↓ 0.0 0 31,124

BitmapOr (cost=3.88..3.88 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=31,124)

  • Buffers: shared hit=186,961
10. 62.248 62.248 ↓ 0.0 0 31,124

Bitmap Index Scan on index_issue_links_on_source_id_and_target_id (cost=0.00..1.94 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=31,124)

  • Index Cond: (source_id = issues.id)
  • Buffers: shared hit=93,482
11. 62.248 62.248 ↓ 0.0 0 31,124

Bitmap Index Scan on index_issue_links_on_target_id (cost=0.00..1.94 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=31,124)

  • Index Cond: (target_id = issues.id)
  • Buffers: shared hit=93,479
12. 3.171 3.171 ↑ 1.0 1 453

Index Scan using issues_pkey on issues as_blocked (cost=0.56..3.58 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=453)

  • Index Cond: (id = issue_links.target_id)
  • Buffers: shared hit=2,265
13. 3.171 3.171 ↑ 1.0 1 453

Index Scan using issues_pkey on issues as_blocked_by (cost=0.56..3.58 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=453)

  • Index Cond: (id = issue_links.source_id)
  • Buffers: shared hit=2,265
Planning time : 5.112 ms
Execution time : 436.793 ms