explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7EQO

Settings
# exclusive inclusive rows x rows loops node
1. 273.667 613.736 ↓ 18.9 11,258 1

HashAggregate (cost=130,520.92..130,526.89 rows=597 width=1,256) (actual time=602.357..613.736 rows=11,258 loops=1)

  • Group Key: issues.id, issues.title, issues.author_id, issues.project_id, issues.created_at, issues.updated_at, issues.descr
  • Buffers: shared hit=118096
2. 1.534 340.069 ↓ 21.3 12,696 1

Append (cost=452.41..130,479.13 rows=597 width=1,256) (actual time=5.585..340.069 rows=12,696 loops=1)

  • Buffers: shared hit=87377
3. 1.638 7.116 ↓ 68.8 413 1

Bitmap Heap Scan on issues (cost=452.41..461.49 rows=6 width=753) (actual time=5.584..7.116 rows=413 loops=1)

  • Recheck Cond: ((author_id = 64248) AND (project_id = 13083))
  • Heap Blocks: exact=430
  • Buffers: shared hit=609
4. 5.478 5.478 ↓ 0.0 0 1

BitmapAnd (cost=452.41..452.41 rows=6 width=0) (actual time=5.478..5.478 rows=0 loops=1)

  • Buffers: shared hit=179
  • -> Bitmap Index Scan on index_issues_on_author_id (cost=0.00..17.30 rows=1049 width=0) (actual time=0.2
  • Index Cond: (author_id = 64248)
  • Buffers: shared hit=11
  • -> Bitmap Index Scan on index_issues_on_project_id_and_iid (cost=0.00..434.86 rows=28523 width=0) (actu
  • Index Cond: (project_id = 13083)
  • Buffers: shared hit=168
5. 1.672 1.672 ↓ 71.0 71 1

Nested Loop (cost=0.86..400.76 rows=1 width=753) (actual time=0.143..1.672 rows=71 loops=1)

  • Buffers: shared hit=682
  • -> Index Scan using index_issue_assignees_on_user_id on issue_assignees (cost=0.43..120.29 rows=81 width=4) (
  • Index Cond: (user_id = 64248)
  • Buffers: shared hit=137
  • -> Index Scan using issues_pkey on issues issues_1 (cost=0.43..3.45 rows=1 width=753) (actual time=0.009..0.0
  • Index Cond: (id = issue_assignees.issue_id)
  • Filter: (project_id = 13083)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=545
6. 114.286 147.227 ↓ 18.3 8,940 1

Hash Semi Join (cost=3,692.34..41,513.41 rows=489 width=753) (actual time=43.546..147.227 rows=8,940 loops=1)

  • Hash Cond: (issues_2.id = award_emoji.awardable_id)
  • Buffers: shared hit=26714
  • -> Bitmap Heap Scan on issues issues_2 (cost=441.99..37955.97 rows=28523 width=753) (actual time=10.269..98.5
  • Recheck Cond: (project_id = 13083)
  • Heap Blocks: exact=26095
  • Buffers: shared hit=26263
  • -> Bitmap Index Scan on index_issues_on_project_id_and_iid (cost=0.00..434.86 rows=28523 width=0) (actu
  • Index Cond: (project_id = 13083)
  • Buffers: shared hit=168
7. 32.941 32.941 ↓ 1.1 87,370 1

Hash (cost=2,212.47..2,212.47 rows=83,031 width=4) (actual time=32.941..32.941 rows=87,370 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4096kB
  • Buffers: shared hit=451
  • -> Index Only Scan using index_award_emoji_on_awardable_type_and_awardable_id on award_emoji (cost=0.42
  • Index Cond: (awardable_type = 'Issue'::text)
  • Heap Fetches: 17
  • Buffers: shared hit=451
8. 100.560 179.454 ↓ 32.4 3,235 1

Hash Join (cost=49,444.18..87,066.03 rows=100 width=753) (actual time=92.047..179.454 rows=3,235 loops=1)

  • Hash Cond: (issues_3.id = notes.noteable_id)
  • Buffers: shared hit=57913
  • -> Bitmap Heap Scan on issues issues_3 (cost=441.99..37955.97 rows=28523 width=753) (actual time=12.402..91.3
  • Recheck Cond: (project_id = 13083)
  • Heap Blocks: exact=26095
  • Buffers: shared hit=26263
  • -> Bitmap Index Scan on index_issues_on_project_id_and_iid (cost=0.00..434.86 rows=28523 width=0) (actu
  • Index Cond: (project_id = 13083)
  • Buffers: shared hit=168
9. 78.894 78.894 ↑ 3.0 5,691 1

Hash (cost=48,790.45..48,790.45 rows=16,940 width=4) (actual time=78.894..78.894 rows=5,691 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 457kB
  • Buffers: shared hit=31650
  • -> HashAggregate (cost=48621.05..48790.45 rows=16940 width=4) (actual time=75.495..77.166 rows=5691 loo
  • Group Key: notes.noteable_id
  • Buffers: shared hit=31650
  • -> Index Scan using index_notes_on_author_id on notes (cost=0.56..48578.48 rows=17025 width=4) (a
  • Index Cond: (author_id = 64248)
  • Filter: ((noteable_type)::text = 'Issue'::text)
  • Rows Removed by Filter: 16955
  • Buffers: shared hit=31650
10. 0.914 3.066 ↓ 37.0 37 1

Nested Loop (cost=982.33..1,031.47 rows=1 width=753) (actual time=2.173..3.066 rows=37 loops=1)

  • Buffers: shared hit=1459
  • -> Index Scan using issues_pkey on issues issues_4 (cost=0.43..0.79 rows=1 width=753) (actual time=0.009..0.0
11. 0.044 2.152 ↓ 1.6 97 1

HashAggregate (cost=981.89..982.50 rows=61 width=4) (actual time=2.138..2.152 rows=97 loops=1)

  • Group Key: notes_1.noteable_id
  • Buffers: shared hit=1068
  • Index Cond: (id = notes_1.noteable_id)
  • Filter: (project_id = 13083)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=391
12. 2.108 2.108 ↓ 1.7 101 1

Nested Loop (cost=0.98..981.74 rows=61 width=4) (actual time=0.086..2.108 rows=101 loops=1)

  • Buffers: shared hit=1068
  • -> Index Scan using index_award_emoji_on_user_id_and_name on award_emoji award_emoji_1 (cost=0.42
  • Index Cond: (user_id = 64248)
  • Filter: ((awardable_type)::text = 'Note'::text)
  • Rows Removed by Filter: 249
  • Buffers: shared hit=368
  • -> Index Scan using notes_pkey on notes notes_1 (cost=0.56..3.58 rows=1 width=8) (actual time=0.0
  • Index Cond: (id = award_emoji_1.awardable_id)
  • Filter: ((noteable_type)::text = 'Issue'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=700