explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qpbm

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 60,253.278 ↑ 1.0 1 1

Sort (cost=764.26..764.27 rows=1 width=336) (actual time=60,253.265..60,253.278 rows=1 loops=1)

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

CTE events

3. 75.911 205.292 ↑ 1.0 5,119 1

Unique (cost=522.56..573.75 rows=5,119 width=161) (actual time=91.927..205.292 rows=5,119 loops=1)

4. 89.963 129.381 ↑ 1.0 5,119 1

Sort (cost=522.56..535.36 rows=5,119 width=161) (actual time=91.912..129.381 rows=5,119 loops=1)

  • Sort Key: ei.environment_id, ei.event_name, ei.lifecycle_id, ei.created DESC
  • Sort Method: quicksort Memory: 1556kB
5. 39.418 39.418 ↑ 1.0 5,119 1

Seq Scan on event_instance ei (cost=0.00..207.19 rows=5,119 width=161) (actual time=0.016..39.418 rows=5,119 loops=1)

6.          

CTE cards

7. 27.474 60,177.838 ↓ 2,005.0 2,005 1

Unique (cost=169.28..169.30 rows=1 width=356) (actual time=60,137.515..60,177.838 rows=2,005 loops=1)

8. 37.939 60,150.364 ↓ 2,005.0 2,005 1

Sort (cost=169.28..169.29 rows=1 width=356) (actual time=60,137.502..60,150.364 rows=2,005 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: 1082kB
9. 642.620 60,112.425 ↓ 2,005.0 2,005 1

Nested Loop (cost=5.27..169.27 rows=1 width=356) (actual time=100.403..60,112.425 rows=2,005 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: 42012
10. 13,919.671 57,383.704 ↓ 20,019.0 20,019 1

Nested Loop (cost=4.99..137.99 rows=1 width=196) (actual time=93.745..57,383.704 rows=20,019 loops=1)

  • Join Filter: ((edv.event_definition_id = trigger_event.event_definition_id) AND (edv.version = trigger_event.event_definition_version))
  • Rows Removed by Join Filter: 2015814
11. 114.910 530.980 ↓ 5,119.0 5,119 1

Nested Loop (cost=4.84..137.41 rows=1 width=196) (actual time=93.641..530.980 rows=5,119 loops=1)

  • Join Filter: (events.version = edv.version)
12. 80.920 364.880 ↓ 365.6 5,119 1

Hash Join (cost=4.70..133.95 rows=14 width=188) (actual time=93.593..364.880 rows=5,119 loops=1)

  • Hash Cond: ((events.environment_id = ed.environment_id) AND ((events.event_name)::text = (ed.event_name)::text))
13. 282.355 282.355 ↑ 1.0 5,119 1

CTE Scan on events (cost=0.00..102.38 rows=5,119 width=184) (actual time=91.959..282.355 rows=5,119 loops=1)

14. 0.828 1.605 ↑ 1.0 108 1

Hash (cost=3.08..3.08 rows=108 width=20) (actual time=1.599..1.605 rows=108 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.777 0.777 ↑ 1.0 108 1

Seq Scan on event_definition ed (cost=0.00..3.08 rows=108 width=20) (actual time=0.023..0.777 rows=108 loops=1)

16. 51.190 51.190 ↑ 1.0 1 5,119

Index Only Scan using event_definition_version_pkey on event_definition_version edv (cost=0.14..0.23 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=5,119)

  • Index Cond: (event_definition_id = ed.id)
  • Heap Fetches: 0
17. 27,043.677 42,933.053 ↓ 66.3 398 5,119

Append (cost=0.14..0.50 rows=6 width=41) (actual time=0.038..8.387 rows=398 loops=5,119)

18. 15,848.424 15,848.424 ↓ 79.6 398 5,119

Index Scan using trigger_event_sys_period_idx on trigger_event (cost=0.14..0.26 rows=5 width=34) (actual time=0.023..3.096 rows=398 loops=5,119)

  • Index Cond: (sys_period @> events.created)
  • Filter: (events.version = event_definition_version)
19. 40.952 40.952 ↓ 0.0 0 5,119

Index Scan using trigger_event_history_sys_period_idx on trigger_event_history (cost=0.14..0.21 rows=1 width=52) (actual time=0.008..0.008 rows=0 loops=5,119)

  • Index Cond: (sys_period @> events.created)
  • Filter: (events.version = event_definition_version)
20. 1,020.969 2,061.957 ↑ 1.5 2 20,019

Append (cost=0.28..6.71 rows=3 width=189) (actual time=0.052..0.103 rows=2 loops=20,019)

21. 260.247 260.247 ↓ 0.0 0 20,019

Index Scan using card_template_version_pkey on card_template_version (cost=0.28..0.64 rows=1 width=183) (actual time=0.012..0.013 rows=0 loops=20,019)

  • 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. 780.741 780.741 ↑ 1.0 2 20,019

Index Scan using card_template_version_history_card_template_id_status_idx on card_template_version_history (cost=0.29..6.06 rows=2 width=190) (actual time=0.022..0.039 rows=2 loops=20,019)

  • 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. 24.144 24.144 ↓ 0.0 0 2,012

Index Scan using batch_pkey on batch (cost=0.15..8.17 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=2,012)

  • 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. 30.020 60,236.142 ↓ 20.1 2,005 1

ProjectSet (cost=0.00..0.53 rows=100 width=264) (actual time=60,137.552..60,236.142 rows=2,005 loops=1)

28. 60,206.122 60,206.122 ↓ 2,005.0 2,005 1

CTE Scan on cards (cost=0.00..0.02 rows=1 width=232) (actual time=60,137.531..60,206.122 rows=2,005 loops=1)

  • Filter: ((type)::text = 'create'::text)
29. 0.037 60,253.252 ↑ 1.0 1 1

Nested Loop Left Join (cost=10.34..20.67 rows=1 width=336) (actual time=60,224.330..60,253.252 rows=1 loops=1)

  • Join Filter: (((card_state.lifecycle_id)::text = (cards_by_user.lifecycle_id)::text) AND (card_state.environment_id = cards_by_user.environment_id) AND ((card_state.event_name)::text = (cards_by_user.event_name)::text) AND (card_state.card_template_id = cards_by_user.card_template_id) AND ((card_state.end_user_id)::text = (cards_by_user.end_user_id)::text))
30. 0.033 60,253.162 ↑ 1.0 1 1

Nested Loop (cost=0.15..10.43 rows=1 width=264) (actual time=60,224.261..60,253.162 rows=1 loops=1)

31. 60,253.089 60,253.089 ↑ 1.0 1 1

CTE Scan on cards_by_user (cost=0.00..2.25 rows=1 width=264) (actual time=60,224.201..60,253.089 rows=1 loops=1)

  • Filter: ((lifecycle_id)::text = '2aee1d72-d7ea-48fd-93c6-9d4d5374baa2'::text)
  • Rows Removed by Filter: 2004
32. 0.040 0.040 ↑ 1.0 1 1

Index Scan using end_user_pkey on end_user eu (cost=0.15..8.17 rows=1 width=37) (actual time=0.034..0.040 rows=1 loops=1)

  • Index Cond: ((environment_id = cards_by_user.environment_id) AND ((id)::text = (cards_by_user.end_user_id)::text))
  • Filter: ((cards_by_user.ctv_status = 'PUBLISHED'::private.card_template_status) OR allow_test)
33. 0.013 0.053 ↓ 0.0 0 1

Unique (cost=10.19..10.21 rows=1 width=176) (actual time=0.046..0.053 rows=0 loops=1)

34. 0.023 0.040 ↓ 0.0 0 1

Sort (cost=10.19..10.20 rows=1 width=176) (actual time=0.034..0.040 rows=0 loops=1)

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

Index Scan using card_state_environment_id_lifecycle_id_end_user_id_event_na_idx on card_state (cost=0.15..10.18 rows=1 width=176) (actual time=0.011..0.017 rows=0 loops=1)

  • Index Cond: ((lifecycle_id)::text = '2aee1d72-d7ea-48fd-93c6-9d4d5374baa2'::text)
Planning time : 2.841 ms
Execution time : 60,254.887 ms