explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JAX

Settings
# exclusive inclusive rows x rows loops node
1. 0.158 171.735 ↑ 25.0 2 1

Merge Left Join (cost=5,031.84..5,806.21 rows=50 width=288) (actual time=171.731..171.735 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.101 ↑ 1.0 1 1

Limit (cost=987.78..987.78 rows=1 width=268) (actual time=1.100..1.101 rows=1 loops=1)

4. 0.004 1.099 ↑ 1.0 1 1

Sort (cost=987.78..987.78 rows=1 width=268) (actual time=1.099..1.099 rows=1 loops=1)

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

Bitmap Heap Scan on event_instance ei (cost=126.41..987.77 rows=1 width=268) (actual time=0.198..1.095 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: 1357
  • Heap Blocks: exact=540
6. 0.145 0.145 ↓ 1.0 1,361 1

Bitmap Index Scan on event_instance_event_name_idx (cost=0.00..126.41 rows=1,350 width=0) (actual time=0.145..0.145 rows=1,361 loops=1)

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

CTE cards

8. 0.001 1.420 ↑ 1.0 1 1

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

9. 0.004 1.419 ↑ 1.0 1 1

Sort (cost=13.32..13.32 rows=1 width=613) (actual time=1.419..1.419 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.012 1.415 ↑ 1.0 1 1

Nested Loop (cost=0.74..13.31 rows=1 width=613) (actual time=1.184..1.415 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.203 ↓ 10.0 10 1

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

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

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

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

Hash Join (cost=0.04..7.16 rows=1 width=228) (actual time=1.154..1.174 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.034 0.034 ↓ 1.0 236 1

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

15. 0.004 1.106 ↑ 1.0 1 1

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

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

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

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

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

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

19. 0.008 0.008 ↓ 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.006..0.008 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.015..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.005 1.426 ↑ 50.0 2 1

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

26. 1.421 1.421 ↑ 1.0 1 1

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

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

Sort (cost=847.41..847.54 rows=50 width=216) (actual time=1.439..1.440 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.432 1.432 ↑ 25.0 2 1

CTE Scan on cards_by_user (cost=0.00..846.00 rows=50 width=216) (actual time=1.429..1.432 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.114 170.137 ↑ 18.6 610 1

Unique (cost=3,182.78..3,672.31 rows=11,358 width=117) (actual time=169.682..170.137 rows=610 loops=1)

33. 164.641 170.023 ↑ 44.9 623 1

Sort (cost=3,182.78..3,252.71 rows=27,973 width=117) (actual time=169.681..170.023 rows=623 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: 2920kB
34. 5.382 5.382 ↑ 1.1 26,542 1

Seq Scan on card_state (cost=0.00..1,116.73 rows=27,973 width=117) (actual time=0.004..5.382 rows=26,542 loops=1)

Planning time : 3.233 ms
Execution time : 172.563 ms