explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c5io

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 2,712.813 ↑ 401.8 141 1

Append (cost=1.56..1,006,271.23 rows=56,660 width=77) (actual time=0.865..2,712.813 rows=141 loops=1)

2. 0.076 1.293 ↓ 14.0 14 1

Nested Loop (cost=1.56..589.75 rows=1 width=77) (actual time=0.865..1.293 rows=14 loops=1)

  • Join Filter: (sprints.pmt_id = pmts.id)
  • Rows Removed by Join Filter: 1036
3. 0.013 1.119 ↓ 14.0 14 1

Nested Loop (cost=1.56..585.06 rows=1 width=45) (actual time=0.847..1.119 rows=14 loops=1)

4. 0.051 1.078 ↓ 14.0 14 1

Nested Loop Left Join (cost=1.27..576.75 rows=1 width=33) (actual time=0.838..1.078 rows=14 loops=1)

  • Filter: (issue_changelogs.id IS NULL)
  • Rows Removed by Filter: 255
5. 0.072 0.433 ↓ 1.7 99 1

Nested Loop (cost=0.84..495.07 rows=57 width=41) (actual time=0.061..0.433 rows=99 loops=1)

6. 0.064 0.064 ↓ 1.7 99 1

Index Only Scan using index_sprint_issues_on_sprint_id_and_issue_id on sprint_issues (cost=0.42..13.42 rows=57 width=16) (actual time=0.050..0.064 rows=99 loops=1)

  • Index Cond: (sprint_id = 9452392)
  • Heap Fetches: 6
7. 0.297 0.297 ↑ 1.0 1 99

Index Scan using issues_pkey on issues (cost=0.42..8.44 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=99)

  • Index Cond: (id = sprint_issues.issue_id)
8. 0.594 0.594 ↑ 1.0 3 99

Index Scan using index_issue_changelogs_on_issue_id_field on issue_changelogs (cost=0.43..1.40 rows=3 width=16) (actual time=0.005..0.006 rows=3 loops=99)

  • Index Cond: ((issues.id = issue_id) AND ((field)::text = 'Sprint'::text))
9. 0.028 0.028 ↑ 1.0 1 14

Index Scan using sprints_pkey on sprints (cost=0.29..8.30 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (id = 9452392)
10. 0.098 0.098 ↑ 1.0 75 14

Seq Scan on pmts (cost=0.00..3.75 rows=75 width=16) (actual time=0.001..0.007 rows=75 loops=14)

11. 0.173 1,451.332 ↑ 2.6 103 1

Unique (cost=111,973.38..111,977.37 rows=266 width=77) (actual time=1,451.070..1,451.332 rows=103 loops=1)

12. 1.110 1,451.159 ↓ 4.3 1,133 1

Sort (cost=111,973.38..111,974.05 rows=266 width=77) (actual time=1,451.068..1,451.159 rows=1,133 loops=1)

  • Sort Key: pmts_1.organization_id, sprints_1.external_id, issue_changelogs_1.issue_id, issues_1.human_id, issue_changelogs_1.pmt_created_at
  • Sort Method: quicksort Memory: 208kB
13. 0.318 1,450.049 ↓ 4.3 1,133 1

Merge Join (cost=5,073.23..111,962.67 rows=266 width=77) (actual time=997.090..1,450.049 rows=1,133 loops=1)

  • Merge Cond: (project_boards.board_id = sprint_boards.board_id)
  • Join Filter: (issues_1.project_id = project_boards.project_id)
  • Rows Removed by Join Filter: 11
14. 0.266 0.266 ↑ 2.2 595 1

Index Scan using "index_data.project_boards_on_board_id" on project_boards (cost=0.28..191.13 rows=1,332 width=16) (actual time=0.031..0.266 rows=595 loops=1)

15. 0.219 1,449.465 ↑ 12.6 1,144 1

Materialize (cost=5,072.81..114,042.03 rows=14,383 width=61) (actual time=996.781..1,449.465 rows=1,144 loops=1)

16. 0.190 1,449.246 ↑ 12.6 1,144 1

Nested Loop (cost=5,072.81..114,006.08 rows=14,383 width=61) (actual time=996.776..1,449.246 rows=1,144 loops=1)

17. 0.026 0.026 ↑ 1.1 11 1

Index Only Scan using index_sprint_boards_on_sprint_id_and_board_id on sprint_boards (cost=0.29..4.50 rows=12 width=16) (actual time=0.020..0.026 rows=11 loops=1)

  • Index Cond: (sprint_id = 9452392)
  • Heap Fetches: 4
18. 0.118 1,449.030 ↑ 11.5 104 11

Materialize (cost=5,072.52..113,824.73 rows=1,199 width=53) (actual time=90.614..131.730 rows=104 loops=11)

19. 0.110 1,448.912 ↑ 11.5 104 1

Nested Loop (cost=5,072.52..113,818.73 rows=1,199 width=53) (actual time=996.750..1,448.912 rows=104 loops=1)

20. 193.820 1,448.386 ↑ 11.5 104 1

Nested Loop (cost=5,072.10..112,136.38 rows=1,199 width=36) (actual time=996.734..1,448.386 rows=104 loops=1)

  • Join Filter: (((issue_changelogs_1."to")::text ~ (('(^|[, ])'::text || (sprints_1.external_id)::text) || '($|[, ])'::text)) AND ((issue_changelogs_1."from" IS NULL) OR ((issue_changelogs_1."from")::text !~ (('(^|[, ])'::text || (sprints_1.external_id)::text) || '($|[, ])'::text))))
  • Rows Removed by Join Filter: 240873
21. 0.026 0.046 ↑ 1.0 1 1

Hash Join (cost=8.31..12.36 rows=1 width=20) (actual time=0.037..0.046 rows=1 loops=1)

  • Hash Cond: (pmts_1.id = sprints_1.pmt_id)
22. 0.010 0.010 ↑ 1.0 75 1

Seq Scan on pmts pmts_1 (cost=0.00..3.75 rows=75 width=16) (actual time=0.001..0.010 rows=75 loops=1)

23. 0.006 0.010 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.004 0.004 ↑ 1.0 1 1

Index Scan using sprints_pkey on sprints sprints_1 (cost=0.29..8.30 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (id = 9452392)
25. 297.507 1,254.520 ↓ 1.0 240,977 1

Bitmap Heap Scan on issue_changelogs issue_changelogs_1 (cost=5,063.79..106,113.20 rows=240,433 width=26) (actual time=965.380..1,254.520 rows=240,977 loops=1)

  • Recheck Cond: ((field)::text = 'Sprint'::text)
  • Rows Removed by Index Recheck: 959733
  • Heap Blocks: exact=40433 lossy=26505
26. 957.013 957.013 ↓ 1.0 240,977 1

Bitmap Index Scan on ch_test_index_issue_changelogs_on_field (cost=0.00..5,003.68 rows=240,433 width=0) (actual time=957.013..957.013 rows=240,977 loops=1)

  • Index Cond: ((field)::text = 'Sprint'::text)
27. 0.416 0.416 ↑ 1.0 1 104

Index Scan using issues_pkey on issues issues_1 (cost=0.42..1.39 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=104)

  • Index Cond: (id = issue_changelogs_1.issue_id)
28. 0.037 499.183 ↑ 14.0 19 1

Unique (cost=111,974.43..111,978.42 rows=266 width=77) (actual time=499.133..499.183 rows=19 loops=1)

29. 0.186 499.146 ↑ 1.3 209 1

Sort (cost=111,974.43..111,975.10 rows=266 width=77) (actual time=499.132..499.146 rows=209 loops=1)

  • Sort Key: pmts_2.organization_id, sprints_2.external_id, issue_changelogs_2.issue_id, issues_2.human_id, issue_changelogs_2.pmt_created_at
  • Sort Method: quicksort Memory: 54kB
30. 0.112 498.960 ↑ 1.3 209 1

Merge Join (cost=5,073.23..111,963.72 rows=266 width=77) (actual time=210.506..498.960 rows=209 loops=1)

  • Merge Cond: (project_boards_1.board_id = sprint_boards_1.board_id)
  • Join Filter: (issues_2.project_id = project_boards_1.project_id)
31. 0.275 0.275 ↑ 2.2 595 1

Index Scan using "index_data.project_boards_on_board_id" on project_boards project_boards_1 (cost=0.28..191.13 rows=1,332 width=16) (actual time=0.039..0.275 rows=595 loops=1)

32. 0.050 498.573 ↑ 68.7 209 1

Materialize (cost=5,072.81..114,038.88 rows=14,367 width=61) (actual time=210.187..498.573 rows=209 loops=1)

33. 0.046 498.523 ↑ 68.7 209 1

Nested Loop (cost=5,072.81..114,002.96 rows=14,367 width=61) (actual time=210.183..498.523 rows=209 loops=1)

34. 0.023 0.023 ↑ 1.1 11 1

Index Only Scan using index_sprint_boards_on_sprint_id_and_board_id on sprint_boards sprint_boards_1 (cost=0.29..4.50 rows=12 width=16) (actual time=0.015..0.023 rows=11 loops=1)

  • Index Cond: (sprint_id = 9452392)
  • Heap Fetches: 4
35. 0.029 498.454 ↑ 63.0 19 11

Materialize (cost=5,072.52..113,821.91 rows=1,197 width=53) (actual time=19.106..45.314 rows=19 loops=11)

36. 0.022 498.425 ↑ 63.0 19 1

Nested Loop (cost=5,072.52..113,815.93 rows=1,197 width=53) (actual time=210.160..498.425 rows=19 loops=1)

37. 162.244 498.289 ↑ 63.0 19 1

Nested Loop (cost=5,072.10..112,136.38 rows=1,197 width=36) (actual time=210.143..498.289 rows=19 loops=1)

  • Join Filter: (((issue_changelogs_2."from")::text ~ (('(^|[, ])'::text || (sprints_2.external_id)::text) || '($|[, ])'::text)) AND ((issue_changelogs_2."to" IS NULL) OR ((issue_changelogs_2."to")::text !~ (('(^|[, ])'::text || (sprints_2.external_id)::text) || '($|[, ])'::text))))
  • Rows Removed by Join Filter: 240958
38. 0.035 0.060 ↑ 1.0 1 1

Hash Join (cost=8.31..12.36 rows=1 width=20) (actual time=0.043..0.060 rows=1 loops=1)

  • Hash Cond: (pmts_2.id = sprints_2.pmt_id)
39. 0.010 0.010 ↑ 1.0 75 1

Seq Scan on pmts pmts_2 (cost=0.00..3.75 rows=75 width=16) (actual time=0.004..0.010 rows=75 loops=1)

40. 0.004 0.015 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=20) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.011 0.011 ↑ 1.0 1 1

Index Scan using sprints_pkey on sprints sprints_2 (cost=0.29..8.30 rows=1 width=20) (actual time=0.009..0.011 rows=1 loops=1)

  • Index Cond: (id = 9452392)
42. 298.353 335.985 ↓ 1.0 240,977 1

Bitmap Heap Scan on issue_changelogs issue_changelogs_2 (cost=5,063.79..106,113.20 rows=240,433 width=26) (actual time=45.034..335.985 rows=240,977 loops=1)

  • Recheck Cond: ((field)::text = 'Sprint'::text)
  • Rows Removed by Index Recheck: 959733
  • Heap Blocks: exact=40433 lossy=26505
43. 37.632 37.632 ↓ 1.0 240,977 1

Bitmap Index Scan on ch_test_index_issue_changelogs_on_field (cost=0.00..5,003.68 rows=240,433 width=0) (actual time=37.632..37.632 rows=240,977 loops=1)

  • Index Cond: ((field)::text = 'Sprint'::text)
44. 0.114 0.114 ↑ 1.0 1 19

Index Scan using issues_pkey on issues issues_2 (cost=0.42..1.39 rows=1 width=25) (actual time=0.005..0.006 rows=1 loops=19)

  • Index Cond: (id = issue_changelogs_2.issue_id)
45. 0.019 760.996 ↑ 11,225.4 5 1

Unique (cost=780,317.18..781,159.08 rows=56,127 width=77) (actual time=760.970..760.996 rows=5 loops=1)

46. 0.085 760.977 ↑ 1,020.5 55 1

Sort (cost=780,317.18..780,457.50 rows=56,127 width=77) (actual time=760.969..760.977 rows=55 loops=1)

  • Sort Key: pmts_3.organization_id, sprints_3.external_id, issues_3.id, issues_3.human_id, issues_3.pmt_created_at
  • Sort Method: quicksort Memory: 32kB
47. 0.030 760.892 ↑ 1,020.5 55 1

Merge Join (cost=1.41..773,394.27 rows=56,127 width=77) (actual time=53.482..760.892 rows=55 loops=1)

  • Merge Cond: (pmts_3.id = sprints_3.pmt_id)
48. 0.035 0.035 ↑ 1.5 50 1

Index Scan using pmts_pkey on pmts pmts_3 (cost=0.14..21.27 rows=75 width=16) (actual time=0.022..0.035 rows=50 loops=1)

49. 0.033 760.827 ↑ 1,020.5 55 1

Materialize (cost=1.27..772,671.22 rows=56,127 width=45) (actual time=53.443..760.827 rows=55 loops=1)

50. 74.765 760.794 ↑ 1,020.5 55 1

Nested Loop (cost=1.27..772,530.91 rows=56,127 width=45) (actual time=53.438..760.794 rows=55 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 143792
51. 0.014 3.501 ↑ 2.0 11 1

Nested Loop (cost=0.85..5,885.78 rows=22 width=28) (actual time=2.047..3.501 rows=11 loops=1)

52. 0.007 3.443 ↑ 1.1 11 1

Nested Loop (cost=0.57..5,818.12 rows=12 width=28) (actual time=2.039..3.443 rows=11 loops=1)

53. 3.394 3.394 ↑ 1.0 1 1

Index Scan using index_sprints_on_pmt_id_and_external_id on sprints sprints_3 (cost=0.29..5,813.51 rows=1 width=20) (actual time=2.006..3.394 rows=1 loops=1)

  • Filter: (id = 9452392)
  • Rows Removed by Filter: 5848
54. 0.042 0.042 ↑ 1.1 11 1

Index Only Scan using index_sprint_boards_on_sprint_id_and_board_id on sprint_boards sprint_boards_2 (cost=0.29..4.50 rows=12 width=16) (actual time=0.032..0.042 rows=11 loops=1)

  • Index Cond: (sprint_id = 9452392)
  • Heap Fetches: 4
55. 0.044 0.044 ↑ 1.0 1 11

Index Scan using "index_data.project_boards_on_board_id" on project_boards project_boards_2 (cost=0.28..5.63 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=11)

  • Index Cond: (board_id = sprint_boards_2.board_id)
56. 107.140 107.140 ↓ 5.6 13,077 11

Index Scan using index_issues_on_project_id_and_external_id on issues issues_3 (cost=0.42..218.34 rows=2,343 width=33) (actual time=0.009..9.740 rows=13,077 loops=11)

  • Index Cond: (project_id = project_boards_2.project_id)
57.          

SubPlan (for Nested Loop)

58. 0.000 575.388 ↓ 0.0 0 143,847

Subquery Scan on initial_sprint_changelog (cost=14.74..14.77 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=143,847)

  • Filter: (((initial_sprint_changelog."from")::text ~ (('(^|[, ])'::text || (sprints_3.external_id)::text) || '($|[, ])'::text)) AND ((initial_sprint_changelog."to" IS NULL) OR ((initial_sprint_changelog."to")::text !~ (('(^|[, ])'::text || (sprints_3.external_id)::text) || '($|[, ])'::text))))
  • Rows Removed by Filter: 0
59. 0.000 575.388 ↓ 0.0 0 143,847

Limit (cost=14.74..14.74 rows=1 width=26) (actual time=0.004..0.004 rows=0 loops=143,847)

60. 143.847 575.388 ↓ 0.0 0 143,847

Sort (cost=14.74..14.75 rows=3 width=26) (actual time=0.004..0.004 rows=0 loops=143,847)

  • Sort Key: issue_changelogs_3.pmt_created_at
  • Sort Method: quicksort Memory: 25kB
61. 431.541 431.541 ↑ 3.0 1 143,847

Index Scan using index_issue_changelogs_on_issue_id_field on issue_changelogs issue_changelogs_3 (cost=0.43..14.73 rows=3 width=26) (actual time=0.002..0.003 rows=1 loops=143,847)

  • Index Cond: ((issue_id = issues_3.id) AND ((field)::text = 'Sprint'::text))
Planning time : 6.284 ms
Execution time : 2,714.056 ms