explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IkBM : 112233

Settings
# exclusive inclusive rows x rows loops node
1. 0.090 21.510 ↓ 5.0 5 1

Sort (cost=188.70..188.71 rows=1 width=256) (actual time=21.472..21.510 rows=5 loops=1)

  • Sort Key: (COALESCE(card_state.created, cards_by_user.created)) DESC
  • Sort Method: quicksort Memory: 27kB
2.          

CTE events

3. 0.035 0.469 ↑ 1.0 1 1

Unique (cost=93.25..93.26 rows=1 width=185) (actual time=0.438..0.469 rows=1 loops=1)

4. 0.043 0.434 ↑ 1.0 1 1

Sort (cost=93.25..93.25 rows=1 width=185) (actual time=0.420..0.434 rows=1 loops=1)

  • Sort Key: ei.environment_id, ei.event_name, ei.created DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.391 0.391 ↑ 1.0 1 1

Seq Scan on event_instance ei (cost=0.00..93.24 rows=1 width=185) (actual time=0.023..0.391 rows=1 loops=1)

  • Filter: ((lifecycle_id)::text = '19f65521-9295-4bca-9ce6-6d10439e57fa'::text)
  • Rows Removed by Filter: 2018
6.          

CTE cards

7. 0.033 5.153 ↑ 1.0 1 1

Unique (cost=33.63..33.65 rows=1 width=356) (actual time=5.123..5.153 rows=1 loops=1)

8. 0.041 5.120 ↑ 1.0 1 1

Sort (cost=33.63..33.64 rows=1 width=356) (actual time=5.106..5.120 rows=1 loops=1)

  • Sort Key: events.environment_id, events.event_name, events.lifecycle_id, card_template_version.card_template_id, card_template_version.version DESC
  • Sort Method: quicksort Memory: 25kB
9. 0.368 5.079 ↑ 1.0 1 1

Nested Loop (cost=0.73..33.62 rows=1 width=356) (actual time=0.898..5.079 rows=1 loops=1)

  • Join Filter: ((card_template_version.sys_period @> events.created) AND ((card_template_version.status = 'PUBLISHED'::private.card_template_status) OR ((card_template_version.status = 'DRAFT'::private.card_template_status) AND (alternatives: SubPlan 2 or hashed SubPlan 3))))
  • Rows Removed by Join Filter: 21
10. 0.258 3.474 ↓ 10.0 10 1

Nested Loop (cost=0.45..5.04 rows=1 width=196) (actual time=0.832..3.474 rows=10 loops=1)

  • Join Filter: ((ed.id = edv.event_definition_id) AND (events.version = edv.version))
11. 0.196 3.096 ↓ 10.0 10 1

Nested Loop (cost=0.31..4.85 rows=1 width=208) (actual time=0.785..3.096 rows=10 loops=1)

  • Join Filter: ((trigger_event.sys_period @> events.created) AND (events.version = trigger_event.event_definition_version))
12. 1.034 2.567 ↑ 1.0 1 1

Hash Join (cost=0.04..2.94 rows=1 width=188) (actual time=0.713..2.567 rows=1 loops=1)

  • Hash Cond: ((ed.environment_id = events.environment_id) AND ((ed.event_name)::text = (events.event_name)::text))
13. 1.000 1.000 ↑ 1.0 108 1

Seq Scan on event_definition ed (cost=0.00..2.08 rows=108 width=20) (actual time=0.014..1.000 rows=108 loops=1)

14. 0.032 0.533 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=184) (actual time=0.526..0.533 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.501 0.501 ↑ 1.0 1 1

CTE Scan on events (cost=0.00..0.02 rows=1 width=184) (actual time=0.454..0.501 rows=1 loops=1)

16. 0.179 0.333 ↑ 1.2 10 1

Append (cost=0.28..1.73 rows=12 width=41) (actual time=0.040..0.333 rows=10 loops=1)

17. 0.134 0.134 ↓ 1.1 10 1

Index Scan using trigger_event_event_definition_id_event_definition_version_idx on trigger_event (cost=0.28..1.06 rows=9 width=34) (actual time=0.024..0.134 rows=10 loops=1)

  • Index Cond: (event_definition_id = ed.id)
18. 0.020 0.020 ↓ 0.0 0 1

Index Scan using trigger_event_history_event_definition_id_event_definition__idx on trigger_event_history (cost=0.15..0.61 rows=3 width=52) (actual time=0.013..0.020 rows=0 loops=1)

  • Index Cond: (event_definition_id = ed.id)
19. 0.120 0.120 ↑ 1.0 1 10

Index Only Scan using event_definition_version_pkey on event_definition_version edv (cost=0.14..0.18 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=10)

  • Index Cond: ((event_definition_id = trigger_event.event_definition_id) AND (version = trigger_event.event_definition_version))
  • Heap Fetches: 10
20. 0.610 1.210 ↑ 1.5 2 10

Append (cost=0.28..4.01 rows=3 width=190) (actual time=0.057..0.121 rows=2 loops=10)

21. 0.140 0.140 ↓ 0.0 0 10

Index Scan using card_template_version_pkey on card_template_version (cost=0.28..0.39 rows=1 width=186) (actual time=0.012..0.014 rows=0 loops=10)

  • Index Cond: ((card_template_id = trigger_event.card_template_id) AND (version = trigger_event.card_template_version))
  • Filter: ((status = 'PUBLISHED'::private.card_template_status) OR (status = 'DRAFT'::private.card_template_status))
  • Rows Removed by Filter: 1
22. 0.460 0.460 ↑ 1.0 2 10

Index Scan using card_template_version_history_card_template_id_status_idx on card_template_version_history (cost=0.29..3.61 rows=2 width=190) (actual time=0.025..0.046 rows=2 loops=10)

  • Index Cond: (card_template_id = trigger_event.card_template_id)
  • Filter: ((trigger_event.card_template_version = version) AND ((status = 'PUBLISHED'::private.card_template_status) OR (status = 'DRAFT'::private.card_template_status)))
  • Rows Removed by Filter: 98
23.          

SubPlan (forNested Loop)

24. 0.027 0.027 ↓ 0.0 0 1

Index Scan using batch_pkey on batch (cost=0.15..8.17 rows=1 width=0) (actual time=0.021..0.027 rows=0 loops=1)

  • Index Cond: (id = events.batch_id)
  • Filter: (event_source = 'test'::private.event_source)
  • Rows Removed by Filter: 1
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on batch batch_1 (cost=0.00..29.62 rows=8 width=8) (never executed)

  • Filter: (event_source = 'test'::private.event_source)
26.          

CTE cards_by_user

27. 0.072 5.268 ↑ 20.0 5 1

ProjectSet (cost=0.00..0.53 rows=100 width=264) (actual time=5.170..5.268 rows=5 loops=1)

28. 5.196 5.196 ↑ 1.0 1 1

CTE Scan on cards (cost=0.00..0.02 rows=1 width=232) (actual time=5.150..5.196 rows=1 loops=1)

  • Filter: ((type)::text = 'create'::text)
29. 0.089 21.420 ↓ 5.0 5 1

Merge Left Join (cost=52.68..61.26 rows=1 width=256) (actual time=21.317..21.420 rows=5 loops=1)

  • Merge Cond: ((cards_by_user.environment_id = card_state.environment_id) AND ((cards_by_user.event_name)::text = (card_state.event_name)::text) AND ((cards_by_user.lifecycle_id)::text = (card_state.lifecycle_id)::text) AND (cards_by_user.card_template_id = card_state.card_template_id) AND ((cards_by_user.end_user_id)::text = (card_state.end_user_id)::text))
30. 0.105 21.259 ↓ 5.0 5 1

Sort (cost=29.08..29.08 rows=1 width=184) (actual time=21.222..21.259 rows=5 loops=1)

  • Sort Key: cards_by_user.environment_id, cards_by_user.event_name, cards_by_user.lifecycle_id, cards_by_user.card_template_id, cards_by_user.end_user_id
  • Sort Method: quicksort Memory: 27kB
31. 7.913 21.154 ↓ 5.0 5 1

Hash Join (cost=3.50..29.07 rows=1 width=184) (actual time=9.292..21.154 rows=5 loops=1)

  • Hash Cond: ((eu.environment_id = cards_by_user.environment_id) AND ((eu.id)::text = (cards_by_user.end_user_id)::text))
  • Join Filter: ((cards_by_user.ctv_status = 'PUBLISHED'::private.card_template_status) OR eu.allow_test)
32. 7.791 7.791 ↑ 1.0 1,003 1

Seq Scan on end_user eu (cost=0.00..18.03 rows=1,003 width=12) (actual time=0.016..7.791 rows=1,003 loops=1)

33. 0.069 5.450 ↑ 20.0 5 1

Hash (cost=2.00..2.00 rows=100 width=188) (actual time=5.443..5.450 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 5.381 5.381 ↑ 20.0 5 1

CTE Scan on cards_by_user (cost=0.00..2.00 rows=100 width=188) (actual time=5.192..5.381 rows=5 loops=1)

35. 0.014 0.072 ↓ 0.0 0 1

Unique (cost=23.60..27.65 rows=200 width=176) (actual time=0.066..0.072 rows=0 loops=1)

36. 0.038 0.058 ↓ 0.0 0 1

Sort (cost=23.60..24.28 rows=270 width=176) (actual time=0.052..0.058 rows=0 loops=1)

  • Sort Key: card_state.environment_id, card_state.event_name, card_state.lifecycle_id, card_state.card_template_id, card_state.end_user_id, card_state.created DESC
  • Sort Method: quicksort Memory: 25kB
37. 0.020 0.020 ↓ 0.0 0 1

Seq Scan on card_state (cost=0.00..12.70 rows=270 width=176) (actual time=0.014..0.020 rows=0 loops=1)

Planning time : 3.017 ms
Execution time : 22.362 ms