explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R2fo

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

Sort (cost=256,026.17..256,026.38 rows=85 width=1,355) (actual rows= loops=)

  • Sort Key: (CASE WHEN (o.parent_id IS NULL) THEN o.id ELSE (o.parent_id)::bigint END), o.id
  • CTE selected_parent_observations-> Limit (cost=20.36..20.37 rows=1 width=48)-> Sort (cost=20.36..20.36 rows=1 width=48)Sort Key: o_1.created_on-> GroupAggregate (cost=20.25..20.35 rows=1 width=48)Group Key: o_1.id
2. 0.000 0.000 ↓ 0.0

CTE most_recent_distinct_states-> Unique (cost=3182.13..3291.88 rows=14633 width=37)-> Sort (cost=3182.13..3218.71 rows=14633 width=37)Sort Key: osh.observation_id, os_1.state, osh.started_on-> Hash Join (cost=1.46..2169.75 rows=14633 width=37)Hash Cond: (osh.state_id = os_1.id)-> Seq Scan on observation_state_history osh (cost=0.00..1869.76 rows=98776 width=28)-> Hash (cost=1.41..1.41 rows=4 width=17)-> Seq Scan on observation_states os_1 (cost=0.00..1.41 rows=4 width=17) (actual rows= loops=)

  • Filter: (state = ANY ('{detected,impacted,cleared,audited}'::text[]))
3. 0.000 0.000 ↓ 0.0

CTE selected_child_observations-> Sort (cost=3369.35..3390.65 rows=8519 width=24)Sort Key: o_2.created_on-> Hash Join (cost=1.67..2813.21 rows=8519 width=24)Hash Cond: (o_2.state_id = os_3.id)-> Seq Scan on observations o_2 (cost=0.00..2,782.43 rows=9,584 width=24) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Filter: (parent_id IS NULL)-> Hash (cost=1.37..1.37 rows=24 width=4)-> Seq Scan on observation_states os_3 (cost=0.00..1.37 rows=24 width=4) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Filter: (state <> ALL ('{no_event,analyzing_clear_error,frames_expired}'::text[]))-> Hash Join (cost=2.18..249,320.55 rows=85 width=1,355) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Cond: (o.state_id = os.id)-> Nested Loop (cost=0.57..554.50 rows=85 width=2668)-> Nested Loop (cost=0.29..431.98 rows=85 width=1986)-> Nested Loop (cost=0.00..298.19 rows=85 width=8) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Join Filter: ((co.id = po.observation_id) OR (co.parent_id = po.observation_id))-> CTE Scan on selected_parent_observations po (cost=0.00..0.02 rows=1 width=8)-> CTE Scan on selected_child_observations co (cost=0.00..170.38 rows=8519 width=12)-> Index Scan using observations_pkey1 on observations o (cost=0.29..1.57 rows=1 width=1,986) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Cond: (id = co.id)-> Index Scan using event_contexts_pkey on event_contexts ec (cost=0.29..1.44 rows=1 width=698) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Cond: (id = o.context_id)-> Hash (cost=1.27..1.27 rows=27 width=17)-> Seq Scan on observation_states os (cost=0.00..1.27 rows=27 width=17)SubPlan 4-> CTE Scan on most_recent_distinct_states (cost=0.00..365.83 rows=1 width=0) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'detected'::text))SubPlan 5-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_1 (cost=0.00..329.24 rows=73 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Filter: (state = 'detected'::text)SubPlan 6-> Limit (cost=0.00..365.83 rows=1 width=4)-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_2 (cost=0.00..365.83 rows=1 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'detected'::text))SubPlan 7-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_3 (cost=0.00..365.83 rows=1 width=0) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'impacted'::text))SubPlan 8-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_4 (cost=0.00..329.24 rows=73 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Filter: (state = 'impacted'::text)SubPlan 9-> Limit (cost=0.00..365.83 rows=1 width=4)-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_5 (cost=0.00..365.83 rows=1 width=4) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'impacted'::text))SubPlan 10-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_6 (cost=0.00..365.83 rows=1 width=0) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'audited'::text))SubPlan 11-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_7 (cost=0.00..329.24 rows=73 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Filter: (state = 'audited'::text)SubPlan 12-> Limit (cost=0.00..365.83 rows=1 width=8)-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_8 (cost=0.00..365.83 rows=1 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'audited'::text))SubPlan 13-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_9 (cost=0.00..365.83 rows=1 width=0) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Filter: ((observation_id = o.id) AND (state = 'cleared'::text))SubPlan 14-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_10 (cost=0.00..329.24 rows=73 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Filter: (state = 'cleared'::text)SubPlan 15-> Limit (cost=0.00..365.83 rows=1 width=4)-> CTE Scan on most_recent_distinct_states most_recent_distinct_states_11 (cost=0.00..365.83 rows=1 width=4) (actual rows= loops=)

  • Filter: ((observation_id = o.id) AND (state = 'cleared'::text))