explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nbGb

Settings
# exclusive inclusive rows x rows loops node
1. 7,103.314 7,103.314 ↓ 10.0 10 1

CTE Scan on promoted_epics (cost=20,088.65..20,088.67 rows=1 width=8) (actual time=7,043.968..7,103.314 rows=10 loops=1)

  • Buffers: shared hit=562286 read=45
  • I/O Timings: read=52.205
2.          

CTE promotion_notes

3. 0.036 7,091.630 ↑ 2.4 10 1

Nested Loop (cost=19,398.42..19,671.05 rows=24 width=116) (actual time=7,043.356..7,091.630 rows=10 loops=1)

  • Buffers: shared hit=557584 read=45
  • I/O Timings: read=52.205
4. 48.190 7,091.484 ↑ 2.4 10 1

Bitmap Heap Scan on public.notes (cost=19,397.98..19,563.95 rows=24 width=116) (actual time=7,043.334..7,091.484 rows=10 loops=1)

  • Filter: (notes.system AND ((notes.noteable_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=557544 read=45
  • I/O Timings: read=52.205
5. 4.257 7,043.294 ↓ 0.0 0 1

BitmapAnd (cost=19,397.98..19,397.98 rows=82 width=0) (actual time=7,043.294..7,043.294 rows=0 loops=1)

  • Buffers: shared hit=555715 read=45
  • I/O Timings: read=52.205
6. 6,599.428 6,599.428 ↑ 6.3 2,741 1

Bitmap Index Scan using index_notes_on_note_trigram (cost=0.00..1,228.96 rows=17,195 width=0) (actual time=6,599.428..6,599.428 rows=2,741 loops=1)

  • Index Cond: (notes.note ~~ 'promoted to epic%'::text)
  • Buffers: shared hit=551975 read=45
  • I/O Timings: read=52.205
7. 439.609 439.609 ↓ 1.0 1,167,297 1

Bitmap Index Scan using notes_pkey (cost=0.00..18,168.76 rows=1,140,819 width=0) (actual time=439.608..439.609 rows=1,167,297 loops=1)

  • Index Cond: ((notes.id >= 168890616) AND (notes.id <= 170219140))
  • Buffers: shared hit=3740
8. 0.110 0.110 ↑ 1.0 1 10

Index Scan using projects_pkey on public.projects (cost=0.43..4.45 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=10)

  • Index Cond: (projects.id = notes.project_id)
  • Buffers: shared hit=40
9.          

CTE promoted_epics

10. 0.024 7,103.298 ↓ 10.0 10 1

Nested Loop (cost=0.29..417.60 rows=1 width=8) (actual time=7,043.964..7,103.298 rows=10 loops=1)

  • Buffers: shared hit=562286 read=45
  • I/O Timings: read=52.205
11. 7,091.644 7,091.644 ↑ 2.4 10 1

CTE Scan on promotion_notes (cost=0.00..0.48 rows=24 width=40) (actual time=7,043.358..7,091.644 rows=10 loops=1)

  • Buffers: shared hit=557584 read=45
  • I/O Timings: read=52.205
12. 11.630 11.630 ↑ 1.0 1 10

Index Scan using index_epics_on_group_id on public.epics (cost=0.29..17.37 rows=1 width=12) (actual time=0.737..1.163 rows=1 loops=10)

  • Index Cond: (epics.group_id = promotion_notes.epic_group_id)
  • Filter: (promotion_notes.promotion_note = concat('promoted to epic &', epics.iid))
  • Rows Removed by Filter: 572
  • Buffers: shared hit=4702