explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xdyL

Settings
# exclusive inclusive rows x rows loops node
1. 0.200 9,103.189 ↑ 1.0 1 1

Aggregate (cost=78,222.73..78,222.74 rows=1 width=8) (actual time=9,103.189..9,103.189 rows=1 loops=1)

2.          

CTE promotion_notes

3. 0.964 8,699.757 ↑ 8.1 625 1

Nested Loop (cost=1,044.35..45,519.53 rows=5,058 width=115) (actual time=7,917.044..8,699.757 rows=625 loops=1)

4. 782.635 8,689.418 ↑ 8.1 625 1

Bitmap Heap Scan on notes (cost=1,043.91..28,370.70 rows=5,058 width=115) (actual time=7,916.814..8,689.418 rows=625 loops=1)

  • Recheck Cond: (note ~~ 'promoted to epic%'::text)
  • Rows Removed by Index Recheck: 2046
  • Filter: (system AND ((noteable_type)::text = 'Issue'::text))
  • Rows Removed by Filter: 69
  • Heap Blocks: exact=2641
5. 7,906.783 7,906.783 ↑ 6.3 2,745 1

Bitmap Index Scan on index_notes_on_note_trigram (cost=0.00..1,042.65 rows=17,220 width=0) (actual time=7,906.783..7,906.783 rows=2,745 loops=1)

  • Index Cond: (note ~~ 'promoted to epic%'::text)
6. 9.375 9.375 ↑ 1.0 1 625

Index Scan using projects_pkey on projects (cost=0.43..3.38 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=625)

  • Index Cond: (id = notes.project_id)
7.          

CTE promoted_epics

8. 323.186 9,087.881 ↓ 2.9 586 1

Merge Join (cost=412.62..3,813.05 rows=205 width=8) (actual time=8,701.144..9,087.881 rows=586 loops=1)

  • Merge Cond: (epics.group_id = promotion_notes.epic_group_id)
  • Join Filter: (concat('promoted to epic &', epics.iid) = promotion_notes.promotion_note)
  • Rows Removed by Join Filter: 593394
9. 20.283 20.283 ↑ 1.0 15,945 1

Index Scan using index_epics_on_group_id on epics (cost=0.29..2,539.45 rows=15,986 width=12) (actual time=0.094..20.283 rows=15,945 loops=1)

10. 43.874 8,744.412 ↓ 117.4 593,974 1

Sort (cost=412.34..424.98 rows=5,058 width=40) (actual time=8,700.830..8,744.412 rows=593,974 loops=1)

  • Sort Key: promotion_notes.epic_group_id
  • Sort Method: quicksort Memory: 74kB
11. 8,700.538 8,700.538 ↑ 8.1 625 1

CTE Scan on promotion_notes (cost=0.00..101.16 rows=5,058 width=40) (actual time=7,917.082..8,700.538 rows=625 loops=1)

12. 0.132 9,102.989 ↑ 27,436.0 411 1

Nested Loop (cost=5.05..699.61 rows=11,276,216 width=4) (actual time=9,088.836..9,102.989 rows=411 loops=1)

13. 0.547 9,088.832 ↓ 2.8 561 1

HashAggregate (cost=4.61..6.61 rows=200 width=4) (actual time=9,088.680..9,088.832 rows=561 loops=1)

  • Group Key: promoted_epics.issue_id
14. 9,088.285 9,088.285 ↓ 2.9 586 1

CTE Scan on promoted_epics (cost=0.00..4.10 rows=205 width=4) (actual time=8,701.178..9,088.285 rows=586 loops=1)

15. 14.025 14.025 ↑ 1.0 1 561

Index Scan using issues_pkey on issues (cost=0.44..3.46 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=561)

  • Index Cond: (id = promoted_epics.issue_id)
  • Filter: (promoted_to_epic_id IS NULL)
  • Rows Removed by Filter: 0
Planning time : 12.367 ms
Execution time : 9,103.578 ms