explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DAKi

Settings
# exclusive inclusive rows x rows loops node
1. 0.163 166.623 ↑ 25.0 2 1

Merge Left Join (cost=4,726.67..5,442.80 rows=50 width=288) (actual time=166.620..166.623 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.003 1.082 ↑ 1.0 1 1

Limit (cost=936.82..936.82 rows=1 width=269) (actual time=1.080..1.082 rows=1 loops=1)

4. 0.005 1.079 ↑ 1.0 1 1

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

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

Bitmap Heap Scan on event_instance ei (cost=121.62..936.81 rows=1 width=269) (actual time=0.180..1.074 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.130 0.130 ↓ 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.130..0.130 rows=1,254 loops=1)

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

CTE cards

8. 0.001 1.422 ↑ 1.0 1 1

Unique (cost=13.32..13.34 rows=1 width=613) (actual time=1.421..1.422 rows=1 loops=1)

9. 0.005 1.421 ↑ 1.0 1 1

Sort (cost=13.32..13.32 rows=1 width=613) (actual time=1.421..1.421 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.007 1.416 ↑ 1.0 1 1

Nested Loop (cost=0.74..13.31 rows=1 width=613) (actual time=1.168..1.416 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.008 1.189 ↓ 10.0 10 1

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

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

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

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

Hash Join (cost=0.04..7.16 rows=1 width=228) (actual time=1.135..1.156 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.003 1.086 ↑ 1.0 1 1

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

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

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

17. 0.005 0.005 ↑ 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.005..0.005 rows=1 loops=1)

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

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

19. 0.009 0.009 ↓ 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.009 rows=10 loops=1)

  • Index Cond: ((event_definition_id = edv.event_definition_id) AND (event_definition_version = edv.version))
20. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

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

Append (cost=0.28..4.49 rows=2 width=406) (actual time=0.017..0.022 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.190 0.190 ↓ 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.016..0.019 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.005 1.428 ↑ 50.0 2 1

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

26. 1.423 1.423 ↑ 1.0 1 1

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

27. 0.008 1.443 ↑ 25.0 2 1

Sort (cost=847.41..847.54 rows=50 width=216) (actual time=1.442..1.443 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.435 1.435 ↑ 25.0 2 1

CTE Scan on cards_by_user (cost=0.00..846.00 rows=50 width=216) (actual time=1.432..1.435 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.120 165.017 ↑ 18.5 569 1

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

33. 159.593 164.897 ↑ 44.4 582 1

Sort (cost=2,928.57..2,993.24 rows=25,867 width=117) (actual time=164.543..164.897 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.304 5.304 ↑ 1.0 25,542 1

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

Planning time : 2.999 ms
Execution time : 167.442 ms