explain.depesz.com

PostgreSQL's explain analyze made readable

Result: isFQ

Settings
# exclusive inclusive rows x rows loops node
1. 2,215.560 241,653.934 ↓ 0.0 0 1

ModifyTable on public.issues (cost=44,202.87..44,354.92 rows=34 width=838) (actual time=241,653.934..241,653.934 rows=0 loops=1)

  • Buffers: shared hit=476401 read=214804 dirtied=1367
  • I/O Timings: read=230664.488
2.          

CTE promotion_notes

3. 0.720 237,516.136 ↑ 8.5 100 1

Nested Loop (cost=1,229.61..40,723.53 rows=849 width=116) (actual time=210,411.119..237,516.136 rows=100 loops=1)

  • Buffers: shared hit=369123 read=211827 dirtied=241
  • I/O Timings: read=226831.111
4. 27,461.617 237,333.016 ↑ 8.5 100 1

Bitmap Heap Scan on public.notes (cost=1,229.17..36,946.87 rows=849 width=116) (actual time=210,403.985..237,333.016 rows=100 loops=1)

  • Filter: (notes.system AND (notes.id >= 123972803) AND (notes.id <= 168890563) AND ((notes.noteable_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 593
  • Buffers: shared hit=368815 read=211728 dirtied=235
  • I/O Timings: read=226651.359
5. 209,871.399 209,871.399 ↑ 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=209,871.399..209,871.399 rows=2,741 loops=1)

  • Index Cond: (notes.note ~~ 'promoted to epic%'::text)
  • Buffers: shared hit=366368 read=185652
  • I/O Timings: read=200144.663
6. 182.400 182.400 ↑ 1.0 1 100

Index Scan using projects_pkey on public.projects (cost=0.43..4.44 rows=1 width=8) (actual time=1.741..1.824 rows=1 loops=100)

  • Index Cond: (projects.id = notes.project_id)
  • Buffers: shared hit=308 read=99 dirtied=6
  • I/O Timings: read=179.752
7.          

CTE promoted_epics

8. 0.504 239,046.736 ↓ 2.7 93 1

Nested Loop (cost=0.29..3,478.90 rows=34 width=8) (actual time=210,932.004..239,046.736 rows=93 loops=1)

  • Buffers: shared hit=471123 read=213152 dirtied=272
  • I/O Timings: read=228152.165
9. 237,516.532 237,516.532 ↑ 8.5 100 1

CTE Scan on promotion_notes (cost=0.00..16.98 rows=849 width=40) (actual time=210,411.123..237,516.532 rows=100 loops=1)

  • Buffers: shared hit=369123 read=211827 dirtied=241
  • I/O Timings: read=226831.111
10. 1,529.700 1,529.700 ↑ 1.0 1 100

Index Scan using index_epics_on_group_id on public.epics (cost=0.29..4.07 rows=1 width=12) (actual time=12.011..15.297 rows=1 loops=100)

  • 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: 1111
  • Buffers: shared hit=102000 read=1325 dirtied=31
  • I/O Timings: read=1321.054
11. 0.684 239,438.374 ↓ 2.7 93 1

Nested Loop (cost=0.44..152.49 rows=34 width=838) (actual time=210,940.314..239,438.374 rows=93 loops=1)

  • Buffers: shared hit=471291 read=213364 dirtied=274
  • I/O Timings: read=228539.776
12. 239,047.183 239,047.183 ↓ 2.7 93 1

CTE Scan on promoted_epics (cost=0.00..0.68 rows=34 width=40) (actual time=210,932.037..239,047.183 rows=93 loops=1)

  • Buffers: shared hit=471123 read=213152 dirtied=272
  • I/O Timings: read=228152.165
13. 390.507 390.507 ↑ 1.0 1 93

Index Scan using issues_pkey on public.issues (cost=0.44..4.46 rows=1 width=778) (actual time=4.197..4.199 rows=1 loops=93)

  • Index Cond: (issues.id = promoted_epics.issue_id)
  • Filter: (issues.promoted_to_epic_id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=168 read=212 dirtied=2
  • I/O Timings: read=387.611