explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I1Bn

Settings
# exclusive inclusive rows x rows loops node
1. 0.147 165.983 ↑ 25.0 2 1

Merge Left Join (cost=4,726.67..5,442.80 rows=50 width=288) (actual time=165.980..165.983 rows=2 loops=1)

  • Merge Cond: (((cards_by_user.environment_id)::text = (card_state.environment_id)::text) AND ((cards_by_user.event_name)::text = (card_state.event_name)::text) AND (cards_by_user.event_source = card_state.event_source) 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))
2.          

CTE events

3. 0.002 1.059 ↑ 1.0 1 1

Limit (cost=936.82..936.82 rows=1 width=269) (actual time=1.058..1.059 rows=1 loops=1)

4. 0.005 1.057 ↑ 1.0 1 1

Sort (cost=936.82..936.82 rows=1 width=269) (actual time=1.057..1.057 rows=1 loops=1)

  • Sort Key: ei.created DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.921 1.052 ↑ 1.0 1 1

Bitmap Heap Scan on event_instance ei (cost=121.62..936.81 rows=1 width=269) (actual time=0.181..1.052 rows=1 loops=1)

  • Recheck Cond: ((event_name)::text = 'testEvent:1'::text)
  • Filter: (((environment_id)::text = '0001-czjY'::text) AND (event_source = 'live'::private.event_source) AND ((lifecycle_id)::text = '9o1tx320j-1'::text))
  • Rows Removed by Filter: 1253
  • Heap Blocks: exact=501
6. 0.131 0.131 ↓ 1.0 1,254 1

Bitmap Index Scan on event_instance_event_name_idx (cost=0.00..121.61 rows=1,244 width=0) (actual time=0.131..0.131 rows=1,254 loops=1)

  • Index Cond: ((event_name)::text = 'testEvent:1'::text)
7.          

CTE cards

8. 0.001 1.385 ↑ 1.0 1 1

Unique (cost=13.32..13.34 rows=1 width=613) (actual time=1.384..1.385 rows=1 loops=1)

9. 0.004 1.384 ↑ 1.0 1 1

Sort (cost=13.32..13.32 rows=1 width=613) (actual time=1.384..1.384 rows=1 loops=1)

  • Sort Key: events.environment_id, events.event_name, events.event_source, events.lifecycle_id, card_template_version.card_template_id, card_template_version.version DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.014 1.380 ↑ 1.0 1 1

Nested Loop (cost=0.74..13.31 rows=1 width=613) (actual time=1.147..1.380 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 (events.event_source = 'test'::private.event_source))))
  • Rows Removed by Join Filter: 21
11. 0.007 1.166 ↓ 10.0 10 1

Nested Loop (cost=0.46..8.78 rows=1 width=236) (actual time=1.136..1.166 rows=10 loops=1)

  • Join Filter: (trigger_event.sys_period @> events.created)
12. 0.004 1.144 ↑ 1.0 1 1

Nested Loop (cost=0.18..7.47 rows=1 width=236) (actual time=1.123..1.144 rows=1 loops=1)

  • Join Filter: (events.version = edv.version)
13. 0.034 1.134 ↑ 1.0 1 1

Hash Join (cost=0.04..7.16 rows=1 width=228) (actual time=1.113..1.134 rows=1 loops=1)

  • Hash Cond: (((ed.environment_id)::text = (events.environment_id)::text) AND ((ed.event_name)::text = (events.event_name)::text))
14. 0.036 0.036 ↓ 1.0 236 1

Seq Scan on event_definition ed (cost=0.00..5.35 rows=235 width=22) (actual time=0.008..0.036 rows=236 loops=1)

15. 0.004 1.064 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=224) (actual time=1.064..1.064 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 1.060 1.060 ↑ 1.0 1 1

CTE Scan on events (cost=0.00..0.02 rows=1 width=224) (actual time=1.059..1.060 rows=1 loops=1)

17. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using event_definition_version_pkey on event_definition_version edv (cost=0.14..0.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (event_definition_id = ed.id)
  • Heap Fetches: 1
18. 0.002 0.015 ↓ 5.0 10 1

Append (cost=0.28..1.29 rows=2 width=36) (actual time=0.008..0.015 rows=10 loops=1)

19. 0.010 0.010 ↓ 10.0 10 1

Index Scan using trigger_event_event_definition_id_event_definition_version_idx on trigger_event (cost=0.28..0.74 rows=1 width=34) (actual time=0.007..0.010 rows=10 loops=1)

  • Index Cond: ((event_definition_id = edv.event_definition_id) AND (event_definition_version = edv.version))
20. 0.003 0.003 ↓ 0.0 0 1

Index Scan using trigger_event_history_event_definition_id_event_definition__idx on trigger_event_history (cost=0.27..0.54 rows=1 width=42) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((event_definition_id = edv.event_definition_id) AND (event_definition_version = edv.version))
21. 0.000 0.200 ↑ 1.0 2 10

Append (cost=0.28..4.49 rows=2 width=406) (actual time=0.016..0.020 rows=2 loops=10)

22. 0.020 0.020 ↓ 0.0 0 10

Index Scan using card_template_version_pkey on card_template_version (cost=0.28..0.59 rows=1 width=434) (actual time=0.002..0.002 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
23. 0.180 0.180 ↓ 2.0 2 10

Index Scan using card_template_version_history_card_template_id_status_idx on card_template_version_history (cost=0.29..3.88 rows=1 width=405) (actual time=0.015..0.018 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
24.          

CTE cards_by_user

25. 0.006 1.392 ↑ 50.0 2 1

ProjectSet (cost=0.00..0.53 rows=100 width=304) (actual time=1.390..1.392 rows=2 loops=1)

26. 1.386 1.386 ↑ 1.0 1 1

CTE Scan on cards (cost=0.00..0.02 rows=1 width=272) (actual time=1.386..1.386 rows=1 loops=1)

  • Filter: ((type)::text = 'create'::text)
27. 0.008 1.406 ↑ 25.0 2 1

Sort (cost=847.41..847.54 rows=50 width=216) (actual time=1.406..1.406 rows=2 loops=1)

  • Sort Key: cards_by_user.environment_id, cards_by_user.event_name, cards_by_user.event_source, cards_by_user.lifecycle_id, cards_by_user.card_template_id, cards_by_user.end_user_id
  • Sort Method: quicksort Memory: 26kB
28. 1.398 1.398 ↑ 25.0 2 1

CTE Scan on cards_by_user (cost=0.00..846.00 rows=50 width=216) (actual time=1.395..1.398 rows=2 loops=1)

  • Filter: (((ctv_status = 'PUBLISHED'::private.card_template_status) AND (event_source = 'live'::private.event_source)) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
29.          

SubPlan (for CTE Scan)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using end_user_pkey on end_user eu (cost=0.41..8.43 rows=1 width=0) (never executed)

  • Index Cond: (((environment_id)::text = (cards_by_user.environment_id)::text) AND ((id)::text = (cards_by_user.end_user_id)::text))
  • Filter: allow_test
31. 0.000 0.000 ↓ 0.0 0

Index Scan using end_user_allow_test_idx on end_user eu_1 (cost=0.29..10.68 rows=41 width=64) (never executed)

  • Index Cond: (allow_test = true)
  • Filter: allow_test
32. 0.108 164.430 ↑ 18.5 569 1

Unique (cost=2,928.57..3,381.24 rows=10,503 width=117) (actual time=164.006..164.430 rows=569 loops=1)

33. 159.009 164.322 ↑ 44.4 582 1

Sort (cost=2,928.57..2,993.24 rows=25,867 width=117) (actual time=164.004..164.322 rows=582 loops=1)

  • Sort Key: card_state.environment_id, card_state.event_name, card_state.event_source, card_state.lifecycle_id, card_state.card_template_id, card_state.end_user_id, card_state.created DESC
  • Sort Method: external merge Disk: 2792kB
34. 5.313 5.313 ↑ 1.0 25,542 1

Seq Scan on card_state (cost=0.00..1,032.67 rows=25,867 width=117) (actual time=0.004..5.313 rows=25,542 loops=1)

Planning time : 3.123 ms
Execution time : 166.829 ms