explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5vzo : PresenceLoad

Settings
# exclusive inclusive rows x rows loops node
1. 680.744 2,597.528 ↓ 2,273.0 2,273 1

Nested Loop Left Join (cost=1.41..57,442.22 rows=1 width=1,494) (actual time=298.405..2,597.528 rows=2,273 loops=1)

  • Output: d1_.id, d1_.day, d1_.dateday, a3_.id, a3_.intervention_id, a3_.units_id, a3_.person_id, a3_.creator_id, a3_.modifier_id, a3_.sent, a3_.unit_qty, a3_.start_date, a3_.end_date, a3_.blocked, a3_.controlled, a3_.state, a3_.comment, a3_.comment_admin, a3_.update_date, a3_.create_date, a3_.activitytype_id, a3_.blockedactivity_id, a3_.startrangevalues_id, a3_.endrangevalues_id, a3_.superparent_id, a3_.is_internal, a2_.id, a2_.entity_id, a2_.unit_id, a2_.range_id, a2_.activity_code, a2_.calculateqty, a2_.onlydate, a2_.multiday, a2_.mission_compatibility, a2_.activity_type_default, a2_.write_authorized, a4_.id, a4_.intervention_id, a4_.units_id, a4_.person_id, a4_.creator_id, a4_.modifier_id, a4_.sent, a4_.unit_qty, a4_.start_date, a4_.end_date, a4_.blocked, a4_.controlled, a4_.state, a4_.comment, a4_.comment_admin, a4_.update_date, a4_.create_date, a4_.activitytype_id, a4_.blockedactivity_id, a4_.startrangevalues_id, a4_.endrangevalues_id, a4_.superparent_id, a4_.is_internal, w0_.id, w0_.entity_id, w0_.person_id, w0_.planning_id, w0_.status_id, w0_.creator_id, w0_.modifier_id, w0_.work_role, w0_.start_date, w0_.end_date, w0_.update_date, w0_.create_date, w0_.planningdepedant_id, w0_.role_id, w0_.staff_number, w0_.in_planning, w0_.sub_entity_intervention_allowed, w0_.contracttype_id, w0_.is_internal, w0_.employeegroup_id, r5_.id, r5_.entity_id, r5_.creator_id, r5_.modifier_id, r5_.label, r5_.manager, r5_.update_date, r5_.create_date, r5_.employeegroup_id, r5_.nb_sunday_volonteer
  • Join Filter: (w0_.role_id = r5_.id)
  • Rows Removed by Join Filter: 10396702
2. 0.553 1,409.905 ↓ 2,273.0 2,273 1

Nested Loop (cost=1.41..57,279.65 rows=1 width=1,427) (actual time=297.896..1,409.905 rows=2,273 loops=1)

  • Output: d1_.id, d1_.day, d1_.dateday, a3_.id, a3_.intervention_id, a3_.units_id, a3_.person_id, a3_.creator_id, a3_.modifier_id, a3_.sent, a3_.unit_qty, a3_.start_date, a3_.end_date, a3_.blocked, a3_.controlled, a3_.state, a3_.comment, a3_.comment_admin, a3_.update_date, a3_.create_date, a3_.activitytype_id, a3_.blockedactivity_id, a3_.startrangevalues_id, a3_.endrangevalues_id, a3_.superparent_id, a3_.is_internal, a2_.id, a2_.entity_id, a2_.unit_id, a2_.range_id, a2_.activity_code, a2_.calculateqty, a2_.onlydate, a2_.multiday, a2_.mission_compatibility, a2_.activity_type_default, a2_.write_authorized, a4_.id, a4_.intervention_id, a4_.units_id, a4_.person_id, a4_.creator_id, a4_.modifier_id, a4_.sent, a4_.unit_qty, a4_.start_date, a4_.end_date, a4_.blocked, a4_.controlled, a4_.state, a4_.comment, a4_.comment_admin, a4_.update_date, a4_.create_date, a4_.activitytype_id, a4_.blockedactivity_id, a4_.startrangevalues_id, a4_.endrangevalues_id, a4_.superparent_id, a4_.is_internal, w0_.id, w0_.entity_id, w0_.person_id, w0_.planning_id, w0_.status_id, w0_.creator_id, w0_.modifier_id, w0_.work_role, w0_.start_date, w0_.end_date, w0_.update_date, w0_.create_date, w0_.planningdepedant_id, w0_.role_id, w0_.staff_number, w0_.in_planning, w0_.sub_entity_intervention_allowed, w0_.contracttype_id, w0_.is_internal, w0_.employeegroup_id
3. 525.799 1,402.068 ↓ 2,428.0 2,428 1

Nested Loop (cost=1.12..57,279.30 rows=1 width=1,341) (actual time=288.788..1,402.068 rows=2,428 loops=1)

  • Output: d1_.id, d1_.day, d1_.dateday, a3_.id, a3_.intervention_id, a3_.units_id, a3_.person_id, a3_.creator_id, a3_.modifier_id, a3_.sent, a3_.unit_qty, a3_.start_date, a3_.end_date, a3_.blocked, a3_.controlled, a3_.state, a3_.comment, a3_.comment_admin, a3_.update_date, a3_.create_date, a3_.activitytype_id, a3_.blockedactivity_id, a3_.startrangevalues_id, a3_.endrangevalues_id, a3_.superparent_id, a3_.is_internal, a2_.id, a2_.entity_id, a2_.unit_id, a2_.range_id, a2_.activity_code, a2_.calculateqty, a2_.onlydate, a2_.multiday, a2_.mission_compatibility, a2_.activity_type_default, a2_.write_authorized, a4_.id, a4_.intervention_id, a4_.units_id, a4_.person_id, a4_.creator_id, a4_.modifier_id, a4_.sent, a4_.unit_qty, a4_.start_date, a4_.end_date, a4_.blocked, a4_.controlled, a4_.state, a4_.comment, a4_.comment_admin, a4_.update_date, a4_.create_date, a4_.activitytype_id, a4_.blockedactivity_id, a4_.startrangevalues_id, a4_.endrangevalues_id, a4_.superparent_id, a4_.is_internal
  • Join Filter: (a3_.activitytype_id = a2_.id)
  • Rows Removed by Join Filter: 8243060
4. 44.791 482.933 ↓ 2,428.0 2,428 1

Nested Loop (cost=1.12..57,174.03 rows=1 width=1,302) (actual time=288.758..482.933 rows=2,428 loops=1)

  • Output: d1_.id, d1_.day, d1_.dateday, a3_.id, a3_.intervention_id, a3_.units_id, a3_.person_id, a3_.creator_id, a3_.modifier_id, a3_.sent, a3_.unit_qty, a3_.start_date, a3_.end_date, a3_.blocked, a3_.controlled, a3_.state, a3_.comment, a3_.comment_admin, a3_.update_date, a3_.create_date, a3_.activitytype_id, a3_.blockedactivity_id, a3_.startrangevalues_id, a3_.endrangevalues_id, a3_.superparent_id, a3_.is_internal, a4_.id, a4_.intervention_id, a4_.units_id, a4_.person_id, a4_.creator_id, a4_.modifier_id, a4_.sent, a4_.unit_qty, a4_.start_date, a4_.end_date, a4_.blocked, a4_.controlled, a4_.state, a4_.comment, a4_.comment_admin, a4_.update_date, a4_.create_date, a4_.activitytype_id, a4_.blockedactivity_id, a4_.startrangevalues_id, a4_.endrangevalues_id, a4_.superparent_id, a4_.is_internal
5. 38.544 141.286 ↓ 148,428.0 148,428 1

Merge Left Join (cost=0.84..57,140.40 rows=1 width=1,292) (actual time=0.029..141.286 rows=148,428 loops=1)

  • Output: a3_.id, a3_.intervention_id, a3_.units_id, a3_.person_id, a3_.creator_id, a3_.modifier_id, a3_.sent, a3_.unit_qty, a3_.start_date, a3_.end_date, a3_.blocked, a3_.controlled, a3_.state, a3_.comment, a3_.comment_admin, a3_.update_date, a3_.create_date, a3_.activitytype_id, a3_.blockedactivity_id, a3_.startrangevalues_id, a3_.endrangevalues_id, a3_.superparent_id, a3_.is_internal, a4_.id, a4_.intervention_id, a4_.units_id, a4_.person_id, a4_.creator_id, a4_.modifier_id, a4_.sent, a4_.unit_qty, a4_.start_date, a4_.end_date, a4_.blocked, a4_.controlled, a4_.state, a4_.comment, a4_.comment_admin, a4_.update_date, a4_.create_date, a4_.activitytype_id, a4_.blockedactivity_id, a4_.startrangevalues_id, a4_.endrangevalues_id, a4_.superparent_id, a4_.is_internal
  • Merge Cond: (a3_.id = a4_.blockedactivity_id)
  • Filter: (a4_.id IS NULL)
  • Rows Removed by Filter: 33
6. 102.703 102.703 ↑ 1.0 148,461 1

Index Scan using activity_pkey on public.activity a3_ (cost=0.42..56,753.93 rows=148,883 width=646) (actual time=0.018..102.703 rows=148,461 loops=1)

  • Output: a3_.id, a3_.intervention_id, a3_.units_id, a3_.person_id, a3_.creator_id, a3_.modifier_id, a3_.sent, a3_.unit_qty, a3_.start_date, a3_.end_date, a3_.blocked, a3_.controlled, a3_.state, a3_.comment, a3_.comment_admin, a3_.update_date, a3_.create_date, a3_.activitytype_id, a3_.blockedactivity_id, a3_.startrangevalues_id, a3_.endrangevalues_id, a3_.superparent_id, a3_.is_internal
  • Filter: ((a3_.blockedactivity_id IS NULL) AND (a3_.superparent_id = 1) AND ((a3_.intervention_id = ANY ('{109413,145318,166669,166282,156340,139400,166720,42264,46924,7505,109417,166652,166646,109262,145316,166722,109414,139398,166726,109418,42268,6431,125557,166285,166311,166505,6438,46923,163296,151992,163292,46927,7501,166667,6432,12978,166650,131588,145311,166287,125555,42267,149660,42484,42486,131582,42878,43266,131586,156341,131584,46928,156339,166284,145347,149700,139395,43267,7502,12971,139402,166655,7503,166660,163291,166653,125550,43272,139397,149640,145302,9626,166647,42487,6434,125554,109266,166312,109264,166286,42874,163290,166395,9624,9628,166503,6436,166504,166658,164626,43271,125552,166657,109261,139399,42483,42263,163297,6435,156343,131585,156338,1236,1233,9629,42488,46926,109415,156342,42489,109263,166397,166727,131583,166283,156345,166721,145349,7499,166659,109267,166648,42261,163295,7504,46925,1237,1238,131581,42881,159243,166666,125553,46922,139396,145366,166297,6433,156344,42875,166668,42265,46929,42266,145315,42482,166656,1232,125556,9630,166492,166495,145317,166494,43273,125551,166661,6437,9623,149639,109265,1235,166651,9625,109268,145368,109411,43269,139401,42262,1234,42880,12979,163293,109412,166493,43270,7506,166496,166396,9627,43268,145372,166313,166649,7500,109416,12977,1231,131587,145314,159242,145352,145303,166281,42879,163294,145331,42876}'::integer[])) OR (a3_.intervention_id IS NULL)))
  • Rows Removed by Filter: 21625
7. 0.039 0.039 ↑ 1,809.4 94 1

Index Scan using uniq_55026b0c10a742b5 on public.activity a4_ (cost=0.42..18,922.15 rows=170,087 width=646) (actual time=0.007..0.039 rows=94 loops=1)

  • Output: a4_.id, a4_.intervention_id, a4_.units_id, a4_.person_id, a4_.creator_id, a4_.modifier_id, a4_.sent, a4_.unit_qty, a4_.start_date, a4_.end_date, a4_.blocked, a4_.controlled, a4_.state, a4_.comment, a4_.comment_admin, a4_.update_date, a4_.create_date, a4_.activitytype_id, a4_.blockedactivity_id, a4_.startrangevalues_id, a4_.endrangevalues_id, a4_.superparent_id, a4_.is_internal
8. 296.856 296.856 ↓ 0.0 0 148,428

Index Scan using dateday_dateday on public.dateday d1_ (cost=0.29..33.62 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=148,428)

  • Output: d1_.id, d1_.day, d1_.dateday
  • Index Cond: ((a3_.start_date <= d1_.dateday) AND (a3_.end_date >= d1_.dateday))
  • Filter: (d1_.id = ANY ('{22014,22021,22028,22035,22042,22049,22056,22063}'::integer[]))
  • Rows Removed by Filter: 4
9. 393.336 393.336 ↓ 1.1 3,396 2,428

Seq Scan on public.activitytype a2_ (cost=0.00..65.12 rows=3,212 width=39) (actual time=0.001..0.162 rows=3,396 loops=2,428)

  • Output: a2_.id, a2_.entity_id, a2_.unit_id, a2_.range_id, a2_.activity_code, a2_.calculateqty, a2_.onlydate, a2_.multiday, a2_.mission_compatibility, a2_.activity_type_default, a2_.write_authorized
10. 7.284 7.284 ↑ 1.0 1 2,428

Index Scan using idx_86892cb4217bbb47 on public.worksfor w0_ (cost=0.29..0.34 rows=1 width=86) (actual time=0.002..0.003 rows=1 loops=2,428)

  • Output: w0_.id, w0_.entity_id, w0_.person_id, w0_.planning_id, w0_.status_id, w0_.creator_id, w0_.modifier_id, w0_.work_role, w0_.start_date, w0_.end_date, w0_.update_date, w0_.create_date, w0_.planningdepedant_id, w0_.role_id, w0_.staff_number, w0_.in_planning, w0_.sub_entity_intervention_allowed, w0_.contracttype_id, w0_.is_internal, w0_.employeegroup_id
  • Index Cond: (w0_.person_id = a3_.person_id)
  • Filter: (w0_.in_planning AND (a3_.start_date >= w0_.start_date) AND ((w0_.end_date IS NULL) OR (a3_.end_date IS NULL) OR (a3_.end_date <= w0_.end_date)))
  • Rows Removed by Filter: 1
11. 506.879 506.879 ↓ 1.1 4,575 2,273

Seq Scan on public.role r5_ (cost=0.00..110.03 rows=4,203 width=67) (actual time=0.001..0.223 rows=4,575 loops=2,273)

  • Output: r5_.id, r5_.entity_id, r5_.creator_id, r5_.modifier_id, r5_.label, r5_.manager, r5_.update_date, r5_.create_date, r5_.employeegroup_id, r5_.nb_sunday_volonteer
Planning time : 3.132 ms
Execution time : 2,597.881 ms