explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ElAN : Optimization for: plan #eayr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=29,417,666.68..29,417,666.69 rows=1 width=32) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Hash Join (cost=29,338,343.98..29,338,366.39 rows=306 width=1,195) (actual rows= loops=)

  • Hash Cond: (cycle.cycle_id = ((SubPlan 5)))
3. 0.000 0.000 ↓ 0.0

Seq Scan on cycle (cost=0.00..16.80 rows=680 width=92) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash (cost=29,338,342.86..29,338,342.86 rows=90 width=1,103) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Join (cost=29,338,340.85..29,338,342.86 rows=90 width=1,103) (actual rows= loops=)

  • Hash Cond: (((SubPlan 6)) = release.releaseid)
6. 0.000 0.000 ↓ 0.0

Unique (cost=29,338,328.83..29,338,329.57 rows=99 width=243) (actual rows= loops=)

7.          

Initplan (forUnique)

8. 0.000 0.000 ↓ 0.0

Limit (cost=11.66..11.67 rows=1 width=20) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=11.66..11.67 rows=1 width=20) (actual rows= loops=)

  • Sort Key: workflow_metadata.index_value
10. 0.000 0.000 ↓ 0.0

Seq Scan on workflow_metadata (cost=0.00..11.65 rows=1 width=20) (actual rows= loops=)

  • Filter: (((entity)::text = 'TestScenario(Post Execution)'::text) AND ((islatest)::text = 'y'::text))
11. 0.000 0.000 ↓ 0.0

Sort (cost=29,338,317.16..29,338,317.41 rows=99 width=243) (actual rows= loops=)

  • Sort Key: t1.tsid DESC, t1.ts_rel_id
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=378.25..29,338,313.88 rows=99 width=243) (actual rows= loops=)

  • Hash Cond: (t1.tsid = t3.tsid)
13. 0.000 0.000 ↓ 0.0

Seq Scan on ts_assignment t1 (cost=0.00..90.81 rows=111 width=24) (actual rows= loops=)

  • Filter: ((userid = 134) AND (isactive = 'y'::bpchar))
14. 0.000 0.000 ↓ 0.0

Hash (cost=371.91..371.91 rows=507 width=73) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Join (cost=291.39..371.91 rows=507 width=73) (actual rows= loops=)

  • Hash Cond: (t2.ts_versionid = t3.versionid)
16. 0.000 0.000 ↓ 0.0

Seq Scan on ts_rel t2 (cost=0.00..79.19 rows=507 width=8) (actual rows= loops=)

  • Filter: (isactive = 'y'::bpchar)
17. 0.000 0.000 ↓ 0.0

Hash (cost=255.62..255.62 rows=2,862 width=73) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on ts_version t3 (cost=0.00..255.62 rows=2,862 width=73) (actual rows= loops=)

19.          

SubPlan (forHash Join)

20. 0.000 0.000 ↓ 0.0

Aggregate (cost=508.35..508.36 rows=1 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on ts_tc (cost=0.00..508.34 rows=5 width=8) (actual rows= loops=)

  • Filter: (ts_versionid = t3.versionid)
22. 0.000 0.000 ↓ 0.0

Index Scan using fk_id on ts_rel (cost=0.28..8.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = t1.ts_rel_id)
23. 0.000 0.000 ↓ 0.0

Index Scan using fk_id on ts_rel ts_rel_1 (cost=0.28..8.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = t1.ts_rel_id)
24. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.30..10.07 rows=1 width=13) (actual rows= loops=)

  • Hash Cond: (status_metadata.status_code = ts.post_approval_status)
25. 0.000 0.000 ↓ 0.0

Seq Scan on status_metadata (cost=0.00..1.60 rows=60 width=21) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=8.29..8.29 rows=1 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Index Scan using fk_id on ts_rel ts (cost=0.28..8.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = t1.ts_rel_id)
28. 0.000 0.000 ↓ 0.0

Index Scan using fk_id on ts_rel ts_1 (cost=0.28..8.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = t1.ts_rel_id)
29. 0.000 0.000 ↓ 0.0

Aggregate (cost=295,798.52..295,798.54 rows=1 width=32) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Subquery Scan on t (cost=295,798.50..295,798.52 rows=1 width=278) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=295,798.50..295,798.51 rows=1 width=628) (actual rows= loops=)

  • Sort Key: a.order_for_execution
32. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=295,757.16..295,798.49 rows=1 width=628) (actual rows= loops=)

  • Hash Cond: ((g.tsid = a.tsid) AND (g.tcid = a.tcid) AND (g.release_run = a.ts_rel_id))
33. 0.000 0.000 ↓ 0.0

Subquery Scan on g (cost=4,773.08..4,806.03 rows=11 width=1,119) (actual rows= loops=)

  • Filter: (g.r = 1)
34. 0.000 0.000 ↓ 0.0

Sort (cost=4,773.08..4,778.57 rows=2,197 width=1,135) (actual rows= loops=)

  • Sort Key: t_1.run_id DESC
35. 0.000 0.000 ↓ 0.0

WindowAgg (cost=740.47..4,651.13 rows=2,197 width=1,135) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=740.47..745.96 rows=2,197 width=1,049) (actual rows= loops=)

  • Sort Key: t_1.tcid, t_1.tsid, t_1.release_run, t_1.end_time DESC
37. 0.000 0.000 ↓ 0.0

HashAggregate (cost=596.55..618.52 rows=2,197 width=1,049) (actual rows= loops=)

  • Group Key: t_1.run_id
38. 0.000 0.000 ↓ 0.0

Seq Scan on run t_1 (cost=0.00..591.06 rows=2,197 width=1,049) (actual rows= loops=)

  • Filter: (end_time IS NOT NULL)
39.          

SubPlan (forWindowAgg)

40. 0.000 0.000 ↓ 0.0

Seq Scan on status_metadata status_metadata_2 (cost=0.00..1.75 rows=1 width=13) (actual rows= loops=)

  • Filter: (status_code = t_1.status)
41. 0.000 0.000 ↓ 0.0

Hash (cost=290,984.06..290,984.06 rows=1 width=254) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=290,982.36..290,984.06 rows=1 width=254) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Unique (cost=290,982.36..290,984.05 rows=1 width=254) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=290,982.36..290,983.20 rows=338 width=254) (actual rows= loops=)

  • Sort Key: t4.tcid, t2_2.order_for_execution
45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..290,968.16 rows=338 width=254) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..619.05 rows=1 width=111) (actual rows= loops=)

  • Join Filter: (t2_2.ts_versionid = t5.ts_versionid)
47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..610.74 rows=1 width=119) (actual rows= loops=)

  • Join Filter: (t4.tcid = t7.tcid)
48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..606.32 rows=1 width=64) (actual rows= loops=)

  • Join Filter: (t2_2.tcid = t4.tcid)
49. 0.000 0.000 ↓ 0.0

Seq Scan on ts_assignment t4 (cost=0.00..97.77 rows=1 width=32) (actual rows= loops=)

  • Filter: ((ts_rel_id = t1.ts_rel_id) AND (tsid = t3.tsid) AND (isactive = 'y'::bpchar))
50. 0.000 0.000 ↓ 0.0

Seq Scan on ts_tc t2_2 (cost=0.00..508.34 rows=17 width=32) (actual rows= loops=)

  • Filter: (tsid = t3.tsid)
51. 0.000 0.000 ↓ 0.0

Index Scan using testcase_pk_tcid on testcase t7 (cost=0.15..4.40 rows=1 width=63) (actual rows= loops=)

  • Index Cond: (tcid = t2_2.tcid)
52. 0.000 0.000 ↓ 0.0

Index Scan using fk_id on ts_rel t5 (cost=0.28..8.30 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (id = t1.ts_rel_id)
  • Filter: ((tsid = t3.tsid) AND (isactive = 'y'::bpchar))
53. 0.000 0.000 ↓ 0.0

Seq Scan on selenium s (cost=0.00..6.38 rows=338 width=5) (actual rows= loops=)

54.          

SubPlan (forNested Loop)

55. 0.000 0.000 ↓ 0.0

Seq Scan on status_metadata status_metadata_1 (cost=0.00..1.75 rows=1 width=13) (actual rows= loops=)

  • Filter: (status_code = t7.status)
56. 0.000 0.000 ↓ 0.0

Aggregate (cost=187.78..187.79 rows=1 width=32) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Sort (cost=186.28..186.53 rows=100 width=46) (actual rows= loops=)

  • Sort Key: cte.rn DESC
58.          

CTE cte

59. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.15..180.68 rows=101 width=46) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..15.04 rows=1 width=46) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on testcase_folder_link tfl (cost=0.00..6.84 rows=1 width=8) (actual rows= loops=)

  • Filter: (tcid = t7.tcid)
62. 0.000 0.000 ↓ 0.0

Index Scan using testcase_folder_pkey on testcase_folder tf (cost=0.15..8.17 rows=1 width=42) (actual rows= loops=)

  • Index Cond: (id = tfl.folder_id)
63. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.33..16.36 rows=10 width=46) (actual rows= loops=)

  • Hash Cond: (m.id = c.parent_id)
64. 0.000 0.000 ↓ 0.0

Seq Scan on testcase_folder m (cost=0.00..14.30 rows=430 width=42) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Hash (cost=0.20..0.20 rows=10 width=6) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

WorkTable Scan on cte c (cost=0.00..0.20 rows=10 width=6) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

CTE Scan on cte (cost=0.00..2.27 rows=100 width=46) (actual rows= loops=)

  • Filter: (name <> '/'::text)
68. 0.000 0.000 ↓ 0.0

Limit (cost=34.49..37.53 rows=1 width=36) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Nested Loop (cost=34.49..37.53 rows=1 width=36) (actual rows= loops=)

  • Join Filter: (t1_1.requirementid = ((elem.value ->> 'id'::text))::integer)
70. 0.000 0.000 ↓ 0.0

Merge Join (cost=34.48..34.53 rows=1 width=43) (actual rows= loops=)

  • Merge Cond: (t2_1.releaseid = t3_1.releaseid)
71. 0.000 0.000 ↓ 0.0

Sort (cost=33.15..33.16 rows=5 width=16) (actual rows= loops=)

  • Sort Key: t2_1.releaseid DESC
72. 0.000 0.000 ↓ 0.0

Hash Join (cost=16.02..33.09 rows=5 width=16) (actual rows= loops=)

  • Hash Cond: (t2_1.requirementid = t1_1.requirementid)
73. 0.000 0.000 ↓ 0.0

Seq Scan on req_release t2_1 (cost=0.00..15.10 rows=510 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Hash (cost=16.00..16.00 rows=2 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Seq Scan on req_tc_release t1_1 (cost=0.00..16.00 rows=2 width=8) (actual rows= loops=)

  • Filter: (tcid = t4.tcid)
76. 0.000 0.000 ↓ 0.0

Sort (cost=1.33..1.34 rows=2 width=35) (actual rows= loops=)

  • Sort Key: t3_1.releaseid DESC
77. 0.000 0.000 ↓ 0.0

Seq Scan on rel_document t3_1 (cost=0.00..1.32 rows=2 width=35) (actual rows= loops=)

  • Filter: (document_name = 'CSV Risk Assessment'::text)
78. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_array_elements elem (cost=0.01..1.00 rows=100 width=32) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Aggregate (cost=631.91..631.92 rows=1 width=8) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..631.90 rows=1 width=4) (actual rows= loops=)

  • Join Filter: (rd.run_id = r.run_id)
81. 0.000 0.000 ↓ 0.0

Seq Scan on run r (cost=0.00..619.61 rows=1 width=4) (actual rows= loops=)

  • Filter: ((tsid = t5.tsid) AND (tcid = t4.tcid) AND (release_run = t4.ts_rel_id))
82. 0.000 0.000 ↓ 0.0

Seq Scan on run_defects rd (cost=0.00..8.80 rows=280 width=8) (actual rows= loops=)

83.          

SubPlan (forHash Right Join)

84. 0.000 0.000 ↓ 0.0

Index Scan using allusers_pkey on allusers (cost=0.27..8.29 rows=1 width=27) (actual rows= loops=)

  • Index Cond: (userid = a.userid)
85. 0.000 0.000 ↓ 0.0

Hash (cost=10.90..10.90 rows=90 width=860) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Seq Scan on release_r release (cost=0.00..10.90 rows=90 width=860) (actual rows= loops=)

87.          

SubPlan (forAggregate)

88. 0.000 0.000 ↓ 0.0

Limit (cost=215.44..215.44 rows=1 width=16) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Sort (cost=215.44..215.46 rows=6 width=16) (actual rows= loops=)

  • Sort Key: ts_timeline.created_on DESC
90. 0.000 0.000 ↓ 0.0

Seq Scan on ts_timeline (cost=0.00..215.41 rows=6 width=16) (actual rows= loops=)

  • Filter: (tsid = t1.tsid)
91. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=18.54..43.70 rows=390 width=32) (actual rows= loops=)

  • Hash Cond: (phase.phase_id = cycle_1.phase_id)
92. 0.000 0.000 ↓ 0.0

Seq Scan on phase (cost=0.00..17.80 rows=780 width=36) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash (cost=18.50..18.50 rows=3 width=8) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Seq Scan on cycle cycle_1 (cost=0.00..18.50 rows=3 width=8) (actual rows= loops=)

  • Filter: (cycle_id = ((SubPlan 5)))