explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3LKE

Settings
# exclusive inclusive rows x rows loops node
1. 217.557 5,115.824 ↓ 43,546.0 43,546 1

Sort (cost=796,143.49..796,143.50 rows=1 width=278) (actual time=5,096.220..5,115.824 rows=43,546 loops=1)

  • Output: insecure_transition_events.record_type, insecure_record_types.name, insecure_transition_events.workflow_template_api_name, wt_2.name, insecure_transition_events.record_id, (w.team_member_ids[1]), (CASE w.status WHEN 'STARTED'::workflow_status THEN ((((('/records/'::text || insecure_transition_events.record_type) || '/'::text) || (insecure_transition_events.record_id)::text) || '/workflows/'::text) || insecure_stage_templates.workflow_template_api_name) ELSE NULL::text END), w.status, e.first_timestamp, (CASE WHEN (w.status = 'CLOSED'::workflow_status) THEN e.latest_timestamp ELSE NULL::timestamp with time zone END), insecure_transition_events."timestamp", insecure_stage_templates.api_name, insecure_stage_templates.name, (CASE WHEN (insecure_stage_templates.status = 'archived'::stage_template_status) THEN true ELSE false END), (CASE WHEN ((w_1.stage_api_name IS NOT NULL) AND e.is_latest_timestamp) THEN true ELSE false END), (false), insecure_stage_templates."order", (CASE WHEN ((insecure_transition_events.from_stage ~~ '%_complete'::text) AND e.is_first_timestamp) THEN 'N/A (start of workflow)'::text ELSE insecure_transition_events.from_stage END), en."timestamp", (date_part('day'::text, (COALESCE(en."timestamp", now()) - e."timestamp")))
  • Sort Key: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, insecure_transition_events."timestamp
  • Sort Method: external merge Disk: 8,632kB
2. 655.704 4,898.267 ↓ 43,546.0 43,546 1

Nested Loop (cost=771,999.38..796,143.48 rows=1 width=278) (actual time=1,501.202..4,898.267 rows=43,546 loops=1)

  • Output: insecure_transition_events.record_type, insecure_record_types.name, insecure_transition_events.workflow_template_api_name, wt_2.name, insecure_transition_events.record_id, w.team_member_ids[1], CASE w.status WHEN 'STARTED'::workflow_status THEN ((((('/records/'::text || insecure_transition_events.record_type) || '/'::text) || (insecure_transition_events.record_id)::text) || '/workflows/'::text) || insecure_stage_templates.workflow_template_api_name) ELSE NULL::text END, w.status, e.first_timestamp, CASE WHEN (w.status = 'CLOSED'::workflow_status) THEN e.latest_timestamp ELSE NULL::timestamp with time zone END, insecure_transition_events."timestamp", insecure_stage_templates.api_name, insecure_stage_templates.name, CASE WHEN (insecure_stage_templates.status = 'archived'::stage_template_status) THEN true ELSE false END, CASE WHEN ((w_1.stage_api_name IS NOT NULL) AND e.is_latest_timestamp) THEN true ELSE false END, (false), insecure_stage_templates."order", CASE WHEN ((insecure_transition_events.from_stage ~~ '%_complete'::text) AND e.is_first_timestamp) THEN 'N/A (start of workflow)'::text ELSE insecure_transition_events.from_stage END, en."timestamp", (date_part('day'::text, (COALESCE(en."timestamp", now()) - e."timestamp")))
  • Join Filter: ((insecure_transition_events.record_type = insecure_stage_templates.record_type) AND (insecure_transition_events.workflow_template_api_name = insecure_stage_templates.workflow_template_api_name) AND (insecure_transition_events.to_stage = insecure_stage_templates.api_name))
  • Rows Removed by Join Filter: 2,111,033
3. 84.765 2,439.752 ↓ 43,971.0 43,971 1

Nested Loop Semi Join (cost=720,699.17..744,843.14 rows=1 width=237) (actual time=1,491.982..2,439.752 rows=43,971 loops=1)

  • Output: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, e.first_timestamp, e.latest_timestamp, insecure_transition_events."timestamp", e.is_latest_timestamp, insecure_transition_events.from_stage, e.is_first_timestamp, en."timestamp", (date_part('day'::text, (COALESCE(en."timestamp", now()) - e."timestamp"))), insecure_transition_events.to_stage, w_1.stage_api_name, w.team_member_ids, w.status, w.record_type, w.workflow_template_api_name, wt.record_type, wt.api_name
  • Join Filter: ((insecure_transition_events.record_type = wt.record_type) AND (insecure_transition_events.workflow_template_api_name = wt.api_name))
4. 97.255 2,223.074 ↓ 43,971.0 43,971 1

Nested Loop (cost=720,698.88..733,513.41 rows=1 width=173) (actual time=1,491.947..2,223.074 rows=43,971 loops=1)

  • Output: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, e.first_timestamp, e.latest_timestamp, insecure_transition_events."timestamp", e.is_latest_timestamp, insecure_transition_events.from_stage, e.is_first_timestamp, en."timestamp", (date_part('day'::text, (COALESCE(en."timestamp", now()) - e."timestamp"))), insecure_transition_events.to_stage, w_1.stage_api_name, w.team_member_ids, w.status, w.record_type, w.workflow_template_api_name
5. 46.530 1,993.906 ↓ 43,971.0 43,971 1

Nested Loop Left Join (cost=720,698.46..733,504.95 rows=1 width=117) (actual time=1,491.938..1,993.906 rows=43,971 loops=1)

  • Output: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, e.first_timestamp, e.latest_timestamp, insecure_transition_events."timestamp", e.is_latest_timestamp, insecure_transition_events.from_stage, e.is_first_timestamp, en."timestamp", (date_part('day'::text, (COALESCE(en."timestamp", now()) - e."timestamp"))), insecure_transition_events.to_stage, w_1.stage_api_name
6. 83.284 1,595.608 ↓ 43,971.0 43,971 1

Hash Right Join (cost=720,697.74..722,166.76 rows=1 width=107) (actual time=1,491.883..1,595.608 rows=43,971 loops=1)

  • Output: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, insecure_transition_events."timestamp", insecure_transition_events.from_stage, insecure_transition_events.to_stage, e.is_first_timestamp, e.is_latest_timestamp, e.first_timestamp, e.latest_timestamp, en."timestamp", date_part('day'::text, (COALESCE(en."timestamp", now()) - e."timestamp"))
  • Hash Cond: ((en.record_type = e.record_type) AND (en.workflow_template_api_name = e.workflow_template_api_name) AND (en.record_id = e.record_id) AND (en.rank_number = (e.rank_number + 1)))
7.          

CTE e

8. 47.656 1,142.149 ↓ 1.1 43,971 1

WindowAgg (cost=706,078.35..707,294.07 rows=40,524 width=130) (actual time=1,080.128..1,142.149 rows=43,971 loops=1)

  • Output: insecure_transition_events_1.record_type, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1.record_id, insecure_transition_events_1."timestamp", insecure_transition_events_1.from_stage, insecure_transition_events_1.to_stage, CASE WHEN (insecure_transition_events_1."timestamp" = insecure_transition_events_2."timestamp") THEN true ELSE false END, CASE WHEN (insecure_transition_events_1."timestamp" = insecure_transition_events_3."timestamp") THEN true ELSE false END, insecure_transition_events_2."timestamp", insecure_transition_events_3."timestamp", rank() OVER (?), insecure_transition_events_1.record_type, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1.record_id
9. 156.124 1,094.493 ↓ 1.1 43,971 1

Sort (cost=706,078.35..706,179.66 rows=40,524 width=89) (actual time=1,080.118..1,094.493 rows=43,971 loops=1)

  • Output: insecure_transition_events_1."timestamp", insecure_transition_events_1.record_type, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1.record_id, insecure_transition_events_1.from_stage, insecure_transition_events_1.to_stage, insecure_transition_events_2."timestamp", insecure_transition_events_3."timestamp
  • Sort Key: insecure_transition_events_1.record_type, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1.record_id, insecure_transition_events_1."timestamp
  • Sort Method: external sort Disk: 4,512kB
10. 58.702 938.369 ↓ 1.1 43,971 1

Nested Loop (cost=17.73..702,976.95 rows=40,524 width=89) (actual time=0.060..938.369 rows=43,971 loops=1)

  • Output: insecure_transition_events_1."timestamp", insecure_transition_events_1.record_type, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1.record_id, insecure_transition_events_1.from_stage, insecure_transition_events_1.to_stage, insecure_transition_events_2."timestamp", insecure_transition_events_3."timestamp
11. 64.983 483.928 ↓ 1.1 43,971 1

Nested Loop (cost=9.14..353,862.69 rows=40,524 width=81) (actual time=0.038..483.928 rows=43,971 loops=1)

  • Output: insecure_transition_events_1.record_type, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1.record_id, insecure_transition_events_1."timestamp", insecure_transition_events_1.from_stage, insecure_transition_events_1.to_stage, insecure_transition_events_2."timestamp
12. 23.206 23.206 ↓ 1.1 43,971 1

Index Only Scan using insecure_transition_events_pkey on records.insecure_transition_events insecure_transition_events_1 (cost=0.56..4,748.43 rows=40,524 width=73) (actual time=0.015..23.206 rows=43,971 loops=1)

  • Output: insecure_transition_events_1.organization_id, insecure_transition_events_1.record_type, insecure_transition_events_1.record_id, insecure_transition_events_1.from_stage, insecure_transition_events_1.to_stage, insecure_transition_events_1.workflow_template_api_name, insecure_transition_events_1."timestamp
  • Index Cond: (insecure_transition_events_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 8,748
13. 43.971 395.739 ↑ 1.0 1 43,971

Limit (cost=8.59..8.60 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=43,971)

  • Output: insecure_transition_events_2."timestamp
14. 131.913 351.768 ↑ 1.0 1 43,971

Sort (cost=8.59..8.60 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=43,971)

  • Output: insecure_transition_events_2."timestamp
  • Sort Key: insecure_transition_events_2."timestamp
  • Sort Method: quicksort Memory: 25kB
15. 219.855 219.855 ↓ 4.0 4 43,971

Index Only Scan using insecure_transition_events_pkey on records.insecure_transition_events insecure_transition_events_2 (cost=0.56..8.58 rows=1 width=8) (actual time=0.004..0.005 rows=4 loops=43,971)

  • Output: insecure_transition_events_2."timestamp
  • Index Cond: ((insecure_transition_events_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_transition_events_2.record_type = insecure_transition_events_1.record_type) AND (insecure_transition_events_2.record_id = insecure_transition_events_1.record_id) AND (insecure_transition_events_2.workflow_template_api_name = insecure_transition_events_1.workflow_template_api_name))
  • Heap Fetches: 29,306
16. 43.971 395.739 ↑ 1.0 1 43,971

Limit (cost=8.59..8.60 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=43,971)

  • Output: insecure_transition_events_3."timestamp
17. 131.913 351.768 ↑ 1.0 1 43,971

Sort (cost=8.59..8.60 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=43,971)

  • Output: insecure_transition_events_3."timestamp
  • Sort Key: insecure_transition_events_3."timestamp" DESC
  • Sort Method: quicksort Memory: 25kB
18. 219.855 219.855 ↓ 4.0 4 43,971

Index Only Scan using insecure_transition_events_pkey on records.insecure_transition_events insecure_transition_events_3 (cost=0.56..8.58 rows=1 width=8) (actual time=0.004..0.005 rows=4 loops=43,971)

  • Output: insecure_transition_events_3."timestamp
  • Index Cond: ((insecure_transition_events_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_transition_events_3.record_type = insecure_transition_events_1.record_type) AND (insecure_transition_events_3.record_id = insecure_transition_events_1.record_id) AND (insecure_transition_events_3.workflow_template_api_name = insecure_transition_events_1.workflow_template_api_name))
  • Heap Fetches: 29,306
19. 20.521 20.521 ↓ 1.1 43,971 1

CTE Scan on e en (cost=0.00..810.48 rows=40,524 width=84) (actual time=0.018..20.521 rows=43,971 loops=1)

  • Output: en.record_type, en.workflow_template_api_name, en.record_id, en."timestamp", en.from_stage, en.to_stage, en.is_first_timestamp, en.is_latest_timestamp, en.first_timestamp, en.latest_timestamp, en.rank_number
20. 32.596 1,491.803 ↓ 43,971.0 43,971 1

Hash (cost=13,403.66..13,403.66 rows=1 width=175) (actual time=1,491.803..1,491.803 rows=43,971 loops=1)

  • Output: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, insecure_transition_events."timestamp", insecure_transition_events.from_stage, insecure_transition_events.to_stage, e.is_first_timestamp, e.is_latest_timestamp, e.first_timestamp, e.latest_timestamp, e."timestamp", e.record_type, e.workflow_template_api_name, e.record_id, e.rank_number
  • Buckets: 65,536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 6,479kB
21. 69.986 1,459.207 ↓ 43,971.0 43,971 1

Merge Join (cost=7,237.44..13,403.66 rows=1 width=175) (actual time=1,331.939..1,459.207 rows=43,971 loops=1)

  • Output: insecure_transition_events.record_type, insecure_transition_events.workflow_template_api_name, insecure_transition_events.record_id, insecure_transition_events."timestamp", insecure_transition_events.from_stage, insecure_transition_events.to_stage, e.is_first_timestamp, e.is_latest_timestamp, e.first_timestamp, e.latest_timestamp, e."timestamp", e.record_type, e.workflow_template_api_name, e.record_id, e.rank_number
  • Merge Cond: ((insecure_transition_events.record_type = e.record_type) AND (insecure_transition_events.record_id = e.record_id) AND (insecure_transition_events.from_stage = e.from_stage) AND (insecure_transition_events.to_stage = e.to_stage) AND (insecure_transition_events.workflow_template_api_name = e.workflow_template_api_name) AND (insecure_transition_events."timestamp" = e."timestamp"))
22. 23.903 23.903 ↓ 1.1 43,971 1

Index Only Scan using insecure_transition_events_pkey on records.insecure_transition_events (cost=0.56..4,748.43 rows=40,524 width=73) (actual time=0.090..23.903 rows=43,971 loops=1)

  • Output: insecure_transition_events.organization_id, insecure_transition_events.record_type, insecure_transition_events.record_id, insecure_transition_events.from_stage, insecure_transition_events.to_stage, insecure_transition_events.workflow_template_api_name, insecure_transition_events."timestamp
  • Index Cond: (insecure_transition_events.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 8,748
23. 18.752 1,365.318 ↓ 1.1 43,971 1

Materialize (cost=7,236.88..7,439.50 rows=40,524 width=166) (actual time=1,331.844..1,365.318 rows=43,971 loops=1)

  • Output: e.is_first_timestamp, e.is_latest_timestamp, e.first_timestamp, e.latest_timestamp, e."timestamp", e.record_type, e.workflow_template_api_name, e.record_id, e.from_stage, e.to_stage, e.rank_number
24. 153.139 1,346.566 ↓ 1.1 43,971 1

Sort (cost=7,236.88..7,338.19 rows=40,524 width=166) (actual time=1,331.841..1,346.566 rows=43,971 loops=1)

  • Output: e.is_first_timestamp, e.is_latest_timestamp, e.first_timestamp, e.latest_timestamp, e."timestamp", e.record_type, e.workflow_template_api_name, e.record_id, e.from_stage, e.to_stage, e.rank_number
  • Sort Key: e.record_type, e.record_id, e.from_stage, e.to_stage, e.workflow_template_api_name, e."timestamp
  • Sort Method: external sort Disk: 5,200kB
25. 1,193.427 1,193.427 ↓ 1.1 43,971 1

CTE Scan on e (cost=0.00..810.48 rows=40,524 width=166) (actual time=1,080.132..1,193.427 rows=43,971 loops=1)

  • Output: e.is_first_timestamp, e.is_latest_timestamp, e.first_timestamp, e.latest_timestamp, e."timestamp", e.record_type, e.workflow_template_api_name, e.record_id, e.from_stage, e.to_stage, e.rank_number
26. 70.623 351.768 ↓ 0.0 0 43,971

Nested Loop Semi Join (cost=0.71..11,338.17 rows=1 width=41) (actual time=0.008..0.008 rows=0 loops=43,971)

  • Output: w_1.stage_api_name, w_1.record_type, w_1.workflow_template_api_name, w_1.record_id
27. 219.855 219.855 ↓ 0.0 0 43,971

Index Scan using insecure_workflows_pkey on records.insecure_workflows w_1 (cost=0.43..8.46 rows=1 width=41) (actual time=0.004..0.005 rows=0 loops=43,971)

  • Output: w_1.organization_id, w_1.record_type, w_1.workflow_template_api_name, w_1.record_id, w_1.stage_api_name, w_1.team_member_ids, w_1.status, w_1.planning_input_date, w_1.enable_auto_replanning, w_1.enable_workflow_planning, w_1.timezone
  • Index Cond: ((w_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_transition_events.record_type = w_1.record_type) AND (insecure_transition_events.record_id = w_1.record_id) AND (insecure_transition_events.workflow_template_api_name = w_1.workflow_template_api_name))
  • Filter: (insecure_transition_events.to_stage = w_1.stage_api_name)
  • Rows Removed by Filter: 1
28. 61.290 61.290 ↑ 1.0 1 20,430

Index Scan using insecure_workflow_templates_organization_id_record_type_sub_idx on records.insecure_workflow_templates wt_1 (cost=0.29..5,665.00 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=20,430)

  • Output: wt_1.record_type, wt_1.api_name
  • Index Cond: ((wt_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (wt_1.record_type = w_1.record_type))
  • Filter: ((w_1.workflow_template_api_name = wt_1.api_name) AND (((current_setting('lanetix.permissions'::text))::text[] && '{customer-administrator,lanetix-staff}'::text[]) OR (SubPlan 12)))
  • Rows Removed by Filter: 0
29.          

SubPlan (for Index Scan)

30. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5,662.12..6,297.69 rows=249 width=0) (never executed)

  • Filter: ((COALESCE((CASE WHEN NULLIF((NOT users_3.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_3.status) <> 'principal'::group_status) OR (alternatives: SubPlan 7 or hashed SubPlan 8))
31.          

Initplan (for Nested Loop Left Join)

32. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=4,790.53..5,660.94 rows=103 width=4) (never executed)

  • Output: g_2.id
  • Merge Cond: (g_2.id = insecure_temp_user_group_8.group_id)
  • Filter: (COALESCE((CASE WHEN NULLIF((NOT users_2.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_2.status) = 'everyone'::group_status)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_2 (cost=0.29..503.28 rows=3,205 width=8) (never executed)

  • Output: g_2.organization_id, g_2.id, g_2.status, g_2.name, g_2.description, g_2.reference_metadata
  • Index Cond: (g_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
34. 0.000 0.000 ↓ 0.0 0

Sort (cost=4,790.23..4,796.91 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_8.group_id, (CASE WHEN NULLIF((NOT users_2.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Sort Key: insecure_temp_user_group_8.group_id
35. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=156.73..4,638.15 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_8.group_id, (CASE WHEN NULLIF((NOT users_2.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Hash Cond: (users_2.id = insecure_temp_user_group_8.user_id)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using users_organization_id on public.users users_2 (cost=0.29..4,467.44 rows=2,953 width=8) (never executed)

  • Output: users_2.id, CASE WHEN NULLIF((NOT users_2.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
37. 0.000 0.000 ↓ 0.0 0

Hash (cost=123.03..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_8.group_id, insecure_temp_user_group_8.user_id
38. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_8 (cost=0.29..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_8.group_id, insecure_temp_user_group_8.user_id
  • Index Cond: (insecure_temp_user_group_8.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
39. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.60..136.09 rows=39 width=8) (never executed)

  • Output: g_3.id, g_3.status
40. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_permissio_organization_id_schema_permis_idx on records.insecure_temp_group_permission p_1 (cost=0.31..127.77 rows=1 width=4) (never executed)

  • Output: p_1.organization_id, p_1.schema, p_1.group_id, p_1.permission
  • Index Cond: ((p_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (p_1.schema = 'records'::text) AND (p_1.permission = (((('recordType'::ltree || (wt_1.record_type)::ltree) || 'workflow'::ltree) || ("substring"(lower(wt_1.api_name), 1, 31))::ltree) || 'permissions.read'::ltree)))
  • Filter: ((p_1.group_id = $14) OR (alternatives: SubPlan 10 or hashed SubPlan 11))
41.          

SubPlan (for Index Scan)

42. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.44 rows=1 width=0) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_9 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_9.organization_id, insecure_temp_user_group_9.user_id, insecure_temp_user_group_9.group_id
  • Index Cond: ((insecure_temp_user_group_9.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_9.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
44. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_2 (cost=0.42..111.11 rows=1 width=4) (never executed)

  • Output: insecure_temp_group_closure_2.organization_id, insecure_temp_group_closure_2.ancestor_id, insecure_temp_group_closure_2.descendent_id
  • Index Cond: ((insecure_temp_group_closure_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_2.descendent_id = insecure_temp_user_group_9.group_id))
  • Filter: (insecure_temp_group_closure_2.ancestor_id = p_1.group_id)
45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.70 rows=222 width=4) (never executed)

  • Output: insecure_temp_group_closure_3.ancestor_id
46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_10 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_10.organization_id, insecure_temp_user_group_10.user_id, insecure_temp_user_group_10.group_id
  • Index Cond: ((insecure_temp_user_group_10.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_10.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
47. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_3 (cost=0.42..111.02 rows=36 width=8) (never executed)

  • Output: insecure_temp_group_closure_3.organization_id, insecure_temp_group_closure_3.ancestor_id, insecure_temp_group_closure_3.descendent_id
  • Index Cond: ((insecure_temp_group_closure_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_3.descendent_id = insecure_temp_user_group_10.group_id))
48. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_3 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Output: g_3.organization_id, g_3.id, g_3.status, g_3.name, g_3.description, g_3.reference_metadata
  • Index Cond: ((g_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (g_3.id = p_1.group_id))
49. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.58..4.51 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_11.group_id, (CASE WHEN NULLIF((NOT users_3.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_11 (cost=0.29..0.40 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_11.organization_id, insecure_temp_user_group_11.user_id, insecure_temp_user_group_11.group_id
  • Index Cond: ((insecure_temp_user_group_11.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_11.group_id = g_3.id))
51. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on public.users users_3 (cost=0.29..4.10 rows=1 width=8) (never executed)

  • Output: users_3.id, CASE WHEN NULLIF((NOT users_3.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_3.id = insecure_temp_user_group_11.user_id)
  • Filter: (users_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
52.          

SubPlan (for Nested Loop Left Join)

53. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_6 (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: ((insecure_temp_user_group_6.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_6.group_id = g_3.id))
  • Heap Fetches: 0
54. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_7 (cost=0.29..111.03 rows=2,672 width=4) (never executed)

  • Output: insecure_temp_user_group_7.group_id
  • Index Cond: (insecure_temp_user_group_7.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
55. 131.913 131.913 ↑ 1.0 1 43,971

Index Scan using insecure_workflows_pkey on records.insecure_workflows w (cost=0.43..8.45 rows=1 width=60) (actual time=0.003..0.003 rows=1 loops=43,971)

  • Output: w.organization_id, w.record_type, w.workflow_template_api_name, w.record_id, w.stage_api_name, w.team_member_ids, w.status, w.planning_input_date, w.enable_auto_replanning, w.enable_workflow_planning, w.timezone
  • Index Cond: ((w.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (w.record_type = insecure_transition_events.record_type) AND (w.record_id = insecure_transition_events.record_id) AND (w.workflow_template_api_name = insecure_transition_events.workflow_template_api_name))
56. 131.913 131.913 ↑ 1.0 1 43,971

Index Scan using insecure_workflow_templates_organization_id_record_type_sub_idx on records.insecure_workflow_templates wt (cost=0.29..5,665.00 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=43,971)

  • Output: wt.record_type, wt.api_name
  • Index Cond: ((wt.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (wt.record_type = w.record_type))
  • Filter: ((w.workflow_template_api_name = wt.api_name) AND (((current_setting('lanetix.permissions'::text))::text[] && '{customer-administrator,lanetix-staff}'::text[]) OR (SubPlan 6)))
  • Rows Removed by Filter: 0
57.          

SubPlan (for Index Scan)

58. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5,662.12..6,297.69 rows=249 width=0) (never executed)

  • Filter: ((COALESCE((CASE WHEN NULLIF((NOT users_1.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_1.status) <> 'principal'::group_status) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
59.          

Initplan (for Nested Loop Left Join)

60. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=4,790.53..5,660.94 rows=103 width=4) (never executed)

  • Output: g.id
  • Merge Cond: (g.id = insecure_temp_user_group_2.group_id)
  • Filter: (COALESCE((CASE WHEN NULLIF((NOT users.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g.status) = 'everyone'::group_status)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g (cost=0.29..503.28 rows=3,205 width=8) (never executed)

  • Output: g.organization_id, g.id, g.status, g.name, g.description, g.reference_metadata
  • Index Cond: (g.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
62. 0.000 0.000 ↓ 0.0 0

Sort (cost=4,790.23..4,796.91 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_2.group_id, (CASE WHEN NULLIF((NOT users.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Sort Key: insecure_temp_user_group_2.group_id
63. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=156.73..4,638.15 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_2.group_id, (CASE WHEN NULLIF((NOT users.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Hash Cond: (users.id = insecure_temp_user_group_2.user_id)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using users_organization_id on public.users (cost=0.29..4,467.44 rows=2,953 width=8) (never executed)

  • Output: users.id, CASE WHEN NULLIF((NOT users.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
65. 0.000 0.000 ↓ 0.0 0

Hash (cost=123.03..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_2.group_id, insecure_temp_user_group_2.user_id
66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_2 (cost=0.29..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_2.group_id, insecure_temp_user_group_2.user_id
  • Index Cond: (insecure_temp_user_group_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.60..136.09 rows=39 width=8) (never executed)

  • Output: g_1.id, g_1.status
68. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_permissio_organization_id_schema_permis_idx on records.insecure_temp_group_permission p (cost=0.31..127.77 rows=1 width=4) (never executed)

  • Output: p.organization_id, p.schema, p.group_id, p.permission
  • Index Cond: ((p.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (p.schema = 'records'::text) AND (p.permission = (((('recordType'::ltree || (wt.record_type)::ltree) || 'workflow'::ltree) || ("substring"(lower(wt.api_name), 1, 31))::ltree) || 'permissions.read'::ltree)))
  • Filter: ((p.group_id = $2) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
69.          

SubPlan (for Index Scan)

70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.44 rows=1 width=0) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_3 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_3.organization_id, insecure_temp_user_group_3.user_id, insecure_temp_user_group_3.group_id
  • Index Cond: ((insecure_temp_user_group_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_3.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
72. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure (cost=0.42..111.11 rows=1 width=4) (never executed)

  • Output: insecure_temp_group_closure.organization_id, insecure_temp_group_closure.ancestor_id, insecure_temp_group_closure.descendent_id
  • Index Cond: ((insecure_temp_group_closure.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure.descendent_id = insecure_temp_user_group_3.group_id))
  • Filter: (insecure_temp_group_closure.ancestor_id = p.group_id)
73. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.70 rows=222 width=4) (never executed)

  • Output: insecure_temp_group_closure_1.ancestor_id
74. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_4 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_4.organization_id, insecure_temp_user_group_4.user_id, insecure_temp_user_group_4.group_id
  • Index Cond: ((insecure_temp_user_group_4.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_4.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
75. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_1 (cost=0.42..111.02 rows=36 width=8) (never executed)

  • Output: insecure_temp_group_closure_1.organization_id, insecure_temp_group_closure_1.ancestor_id, insecure_temp_group_closure_1.descendent_id
  • Index Cond: ((insecure_temp_group_closure_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_1.descendent_id = insecure_temp_user_group_4.group_id))
76. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_1 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Output: g_1.organization_id, g_1.id, g_1.status, g_1.name, g_1.description, g_1.reference_metadata
  • Index Cond: ((g_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (g_1.id = p.group_id))
77. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.58..4.51 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_5.group_id, (CASE WHEN NULLIF((NOT users_1.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
78. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_5 (cost=0.29..0.40 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_5.organization_id, insecure_temp_user_group_5.user_id, insecure_temp_user_group_5.group_id
  • Index Cond: ((insecure_temp_user_group_5.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_5.group_id = g_1.id))
79. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on public.users users_1 (cost=0.29..4.10 rows=1 width=8) (never executed)

  • Output: users_1.id, CASE WHEN NULLIF((NOT users_1.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_1.id = insecure_temp_user_group_5.user_id)
  • Filter: (users_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
80.          

SubPlan (for Nested Loop Left Join)

81. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: ((insecure_temp_user_group.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group.group_id = g_1.id))
  • Heap Fetches: 0
82. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_1 (cost=0.29..111.03 rows=2,672 width=4) (never executed)

  • Output: insecure_temp_user_group_1.group_id
  • Index Cond: (insecure_temp_user_group_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
83. 1,231.188 1,802.811 ↓ 24.5 49 43,971

Unique (cost=51,300.21..51,300.26 rows=2 width=205) (actual time=0.001..0.041 rows=49 loops=43,971)

  • Output: insecure_stage_templates.record_type, insecure_record_types.name, wt_2.name, insecure_stage_templates.api_name, insecure_stage_templates.name, insecure_stage_templates.workflow_template_api_name, ((insecure_stage_templates."order" - 1)), insecure_stage_templates."order", insecure_stage_templates.status, (false)
84. 570.745 571.623 ↓ 24.5 49 43,971

Sort (cost=51,300.21..51,300.21 rows=2 width=205) (actual time=0.000..0.013 rows=49 loops=43,971)

  • Output: insecure_stage_templates.record_type, insecure_record_types.name, wt_2.name, insecure_stage_templates.api_name, insecure_stage_templates.name, insecure_stage_templates.workflow_template_api_name, ((insecure_stage_templates."order" - 1)), insecure_stage_templates."order", insecure_stage_templates.status, (false)
  • Sort Key: insecure_stage_templates.record_type, insecure_record_types.name, wt_2.name, insecure_stage_templates.api_name, insecure_stage_templates.name, insecure_stage_templates.workflow_template_api_name, ((insecure_stage_templates."order" - 1)), insecure_stage_templates."order", insecure_stage_templates.status, (false)
  • Sort Method: quicksort Memory: 34kB
85. 0.019 0.878 ↓ 24.5 49 1

Append (cost=0.86..51,300.20 rows=2 width=205) (actual time=0.082..0.878 rows=49 loops=1)

86. 0.040 0.504 ↓ 37.0 37 1

Nested Loop (cost=0.86..45,388.85 rows=1 width=117) (actual time=0.081..0.504 rows=37 loops=1)

  • Output: insecure_stage_templates.record_type, insecure_record_types.name, wt_2.name, insecure_stage_templates.api_name, insecure_stage_templates.name, insecure_stage_templates.workflow_template_api_name, (insecure_stage_templates."order" - 1), insecure_stage_templates."order", insecure_stage_templates.status, false
87. 0.030 0.242 ↓ 37.0 37 1

Nested Loop (cost=0.57..45,383.16 rows=1 width=131) (actual time=0.059..0.242 rows=37 loops=1)

  • Output: insecure_stage_templates.record_type, insecure_stage_templates.api_name, insecure_stage_templates.name, insecure_stage_templates.workflow_template_api_name, insecure_stage_templates."order", insecure_stage_templates.status, wt_2.name, wt_2.record_type
88. 0.044 0.044 ↓ 3.0 12 1

Index Scan using insecure_workflow_templates_pkey on records.insecure_workflow_templates wt_2 (cost=0.29..45,349.86 rows=4 width=96) (actual time=0.021..0.044 rows=12 loops=1)

  • Output: wt_2.name, wt_2.record_type, wt_2.api_name
  • Index Cond: (wt_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Filter: ((wt_2.status <> 'archived'::workflow_template_status) AND ((wt_2.status = ANY ('{published,archived}'::workflow_template_status[])) OR ((current_setting('lanetix.permissions'::text))::text[] && '{customer-administrator,lanetix-staff}'::text[]) OR (SubPlan 25)))
  • Rows Removed by Filter: 2
89.          

SubPlan (for Index Scan)

90. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5,662.11..6,297.68 rows=249 width=0) (never executed)

  • Filter: ((COALESCE((CASE WHEN NULLIF((NOT users_7.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_7.status) <> 'principal'::group_status) OR (alternatives: SubPlan 20 or hashed SubPlan 21))
91.          

Initplan (for Nested Loop Left Join)

92. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=4,790.53..5,660.94 rows=103 width=4) (never executed)

  • Output: g_6.id
  • Merge Cond: (g_6.id = insecure_temp_user_group_20.group_id)
  • Filter: (COALESCE((CASE WHEN NULLIF((NOT users_6.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_6.status) = 'everyone'::group_status)
93. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_6 (cost=0.29..503.28 rows=3,205 width=8) (never executed)

  • Output: g_6.organization_id, g_6.id, g_6.status, g_6.name, g_6.description, g_6.reference_metadata
  • Index Cond: (g_6.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
94. 0.000 0.000 ↓ 0.0 0

Sort (cost=4,790.23..4,796.91 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_20.group_id, (CASE WHEN NULLIF((NOT users_6.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Sort Key: insecure_temp_user_group_20.group_id
95. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=156.73..4,638.15 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_20.group_id, (CASE WHEN NULLIF((NOT users_6.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Hash Cond: (users_6.id = insecure_temp_user_group_20.user_id)
96. 0.000 0.000 ↓ 0.0 0

Index Scan using users_organization_id on public.users users_6 (cost=0.29..4,467.44 rows=2,953 width=8) (never executed)

  • Output: users_6.id, CASE WHEN NULLIF((NOT users_6.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_6.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
97. 0.000 0.000 ↓ 0.0 0

Hash (cost=123.03..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_20.group_id, insecure_temp_user_group_20.user_id
98. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_20 (cost=0.29..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_20.group_id, insecure_temp_user_group_20.user_id
  • Index Cond: (insecure_temp_user_group_20.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
99. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.60..136.09 rows=39 width=8) (never executed)

  • Output: g_7.id, g_7.status
100. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_permissio_organization_id_schema_permis_idx on records.insecure_temp_group_permission p_3 (cost=0.30..127.77 rows=1 width=4) (never executed)

  • Output: p_3.organization_id, p_3.schema, p_3.group_id, p_3.permission
  • Index Cond: ((p_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (p_3.schema = 'records'::text) AND (p_3.permission = (((('recordType'::ltree || wt_2.record_type) || 'workflow'::ltree) || ("substring"(lower(wt_2.api_name), 1, 31))::ltree) || 'permissions.admin'::ltree)))
  • Filter: ((p_3.group_id = $45) OR (alternatives: SubPlan 23 or hashed SubPlan 24))
101.          

SubPlan (for Index Scan)

102. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.44 rows=1 width=0) (never executed)

103. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_21 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_21.organization_id, insecure_temp_user_group_21.user_id, insecure_temp_user_group_21.group_id
  • Index Cond: ((insecure_temp_user_group_21.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_21.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
104. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_6 (cost=0.42..111.11 rows=1 width=4) (never executed)

  • Output: insecure_temp_group_closure_6.organization_id, insecure_temp_group_closure_6.ancestor_id, insecure_temp_group_closure_6.descendent_id
  • Index Cond: ((insecure_temp_group_closure_6.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_6.descendent_id = insecure_temp_user_group_21.group_id))
  • Filter: (insecure_temp_group_closure_6.ancestor_id = p_3.group_id)
105. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.70 rows=222 width=4) (never executed)

  • Output: insecure_temp_group_closure_7.ancestor_id
106. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_22 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_22.organization_id, insecure_temp_user_group_22.user_id, insecure_temp_user_group_22.group_id
  • Index Cond: ((insecure_temp_user_group_22.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_22.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
107. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_7 (cost=0.42..111.02 rows=36 width=8) (never executed)

  • Output: insecure_temp_group_closure_7.organization_id, insecure_temp_group_closure_7.ancestor_id, insecure_temp_group_closure_7.descendent_id
  • Index Cond: ((insecure_temp_group_closure_7.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_7.descendent_id = insecure_temp_user_group_22.group_id))
108. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_7 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Output: g_7.organization_id, g_7.id, g_7.status, g_7.name, g_7.description, g_7.reference_metadata
  • Index Cond: ((g_7.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (g_7.id = p_3.group_id))
109. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.58..4.51 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_23.group_id, (CASE WHEN NULLIF((NOT users_7.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
110. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_23 (cost=0.29..0.40 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_23.organization_id, insecure_temp_user_group_23.user_id, insecure_temp_user_group_23.group_id
  • Index Cond: ((insecure_temp_user_group_23.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_23.group_id = g_7.id))
111. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on public.users users_7 (cost=0.29..4.10 rows=1 width=8) (never executed)

  • Output: users_7.id, CASE WHEN NULLIF((NOT users_7.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_7.id = insecure_temp_user_group_23.user_id)
  • Filter: (users_7.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
112.          

SubPlan (for Nested Loop Left Join)

113. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_18 (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: ((insecure_temp_user_group_18.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_18.group_id = g_7.id))
  • Heap Fetches: 0
114. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_19 (cost=0.29..111.03 rows=2,672 width=4) (never executed)

  • Output: insecure_temp_user_group_19.group_id
  • Index Cond: (insecure_temp_user_group_19.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
115. 0.168 0.168 ↓ 3.0 3 12

Index Scan using insecure_stage_templates_pkey on records.insecure_stage_templates (cost=0.29..8.31 rows=1 width=67) (actual time=0.009..0.014 rows=3 loops=12)

  • Output: insecure_stage_templates.organization_id, insecure_stage_templates.record_type, insecure_stage_templates.api_name, insecure_stage_templates.name, insecure_stage_templates.workflow_template_api_name, insecure_stage_templates."order", insecure_stage_templates.bucket_api_name, insecure_stage_templates.status, insecure_stage_templates.exclude_from_planning, insecure_stage_templates.auto_advance, insecure_stage_templates.enable_auto_replanning, insecure_stage_templates.reference_metadata
  • Index Cond: ((insecure_stage_templates.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_stage_templates.record_type = wt_2.record_type) AND (insecure_stage_templates.workflow_template_api_name = wt_2.api_name))
  • Filter: (insecure_stage_templates."order" > 0)
  • Rows Removed by Filter: 2
116. 0.222 0.222 ↑ 1.0 1 37

Index Scan using insecure_record_types_pkey on records.insecure_record_types (cost=0.29..5.67 rows=1 width=25) (actual time=0.005..0.006 rows=1 loops=37)

  • Output: insecure_record_types.name, insecure_record_types.api_name
  • Index Cond: (((insecure_record_types.api_name)::text = insecure_stage_templates.record_type) AND (insecure_record_types.organization_id = (current_setting('lanetix.organization_id'::text))::integer))
117. 0.028 0.355 ↓ 12.0 12 1

Nested Loop (cost=126.35..5,911.33 rows=1 width=151) (actual time=0.179..0.355 rows=12 loops=1)

  • Output: insecure_stage_templates_1.record_type, insecure_record_types_1.name, wt_3.name, (insecure_stage_templates_1.workflow_template_api_name || '_complete'::text), 'Completed Workflow Stage'::text, insecure_stage_templates_1.workflow_template_api_name, insecure_stage_templates_1."order", (insecure_stage_templates_1."order" + 1), insecure_stage_templates_1.status, true
  • Join Filter: ((insecure_stage_templates_1.record_type = wt_3.record_type) AND (insecure_stage_templates_1.workflow_template_api_name = wt_3.api_name))
  • Rows Removed by Join Filter: 6
118. 0.028 0.271 ↓ 14.0 14 1

Nested Loop (cost=126.06..244.38 rows=1 width=91) (actual time=0.146..0.271 rows=14 loops=1)

  • Output: insecure_stage_templates_1.record_type, insecure_stage_templates_1.workflow_template_api_name, insecure_stage_templates_1."order", insecure_stage_templates_1.status, insecure_record_types_1.name, insecure_record_types_1.api_name, insecure_stage_templates_2.record_type, insecure_stage_templates_2.workflow_template_api_name
119. 0.046 0.187 ↓ 14.0 14 1

Hash Join (cost=125.77..239.56 rows=1 width=66) (actual time=0.134..0.187 rows=14 loops=1)

  • Output: insecure_stage_templates_1.record_type, insecure_stage_templates_1.workflow_template_api_name, insecure_stage_templates_1."order", insecure_stage_templates_1.status, insecure_stage_templates_2.record_type, insecure_stage_templates_2.workflow_template_api_name
  • Hash Cond: ((insecure_stage_templates_1.record_type = insecure_stage_templates_2.record_type) AND (insecure_stage_templates_1.workflow_template_api_name = insecure_stage_templates_2.workflow_template_api_name) AND (insecure_stage_templates_1."order" = (max(insecure_stage_templates_2."order"))))
120. 0.031 0.044 ↑ 1.1 67 1

Bitmap Heap Scan on records.insecure_stage_templates insecure_stage_templates_1 (cost=4.84..117.81 rows=71 width=37) (actual time=0.018..0.044 rows=67 loops=1)

  • Output: insecure_stage_templates_1.organization_id, insecure_stage_templates_1.record_type, insecure_stage_templates_1.api_name, insecure_stage_templates_1.name, insecure_stage_templates_1.workflow_template_api_name, insecure_stage_templates_1."order", insecure_stage_templates_1.bucket_api_name, insecure_stage_templates_1.status, insecure_stage_templates_1.exclude_from_planning, insecure_stage_templates_1.auto_advance, insecure_stage_templates_1.enable_auto_replanning, insecure_stage_templates_1.reference_metadata
  • Recheck Cond: (insecure_stage_templates_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Blocks: exact=13
121. 0.013 0.013 ↑ 1.1 67 1

Bitmap Index Scan on insecure_stage_templates_pkey (cost=0.00..4.82 rows=71 width=0) (actual time=0.013..0.013 rows=67 loops=1)

  • Index Cond: (insecure_stage_templates_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
122. 0.007 0.097 ↑ 4.9 14 1

Hash (cost=119.73..119.73 rows=69 width=33) (actual time=0.097..0.097 rows=14 loops=1)

  • Output: insecure_stage_templates_2.record_type, insecure_stage_templates_2.workflow_template_api_name, (max(insecure_stage_templates_2."order"))
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
123. 0.051 0.090 ↑ 4.9 14 1

HashAggregate (cost=118.35..119.04 rows=69 width=33) (actual time=0.084..0.090 rows=14 loops=1)

  • Output: insecure_stage_templates_2.record_type, insecure_stage_templates_2.workflow_template_api_name, max(insecure_stage_templates_2."order")
  • Group Key: insecure_stage_templates_2.record_type, insecure_stage_templates_2.workflow_template_api_name
124. 0.028 0.039 ↑ 1.1 67 1

Bitmap Heap Scan on records.insecure_stage_templates insecure_stage_templates_2 (cost=4.84..117.81 rows=71 width=33) (actual time=0.013..0.039 rows=67 loops=1)

  • Output: insecure_stage_templates_2.organization_id, insecure_stage_templates_2.record_type, insecure_stage_templates_2.api_name, insecure_stage_templates_2.name, insecure_stage_templates_2.workflow_template_api_name, insecure_stage_templates_2."order", insecure_stage_templates_2.bucket_api_name, insecure_stage_templates_2.status, insecure_stage_templates_2.exclude_from_planning, insecure_stage_templates_2.auto_advance, insecure_stage_templates_2.enable_auto_replanning, insecure_stage_templates_2.reference_metadata
  • Recheck Cond: (insecure_stage_templates_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Blocks: exact=13
125. 0.011 0.011 ↑ 1.1 67 1

Bitmap Index Scan on insecure_stage_templates_pkey (cost=0.00..4.82 rows=71 width=0) (actual time=0.011..0.011 rows=67 loops=1)

  • Index Cond: (insecure_stage_templates_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
126. 0.056 0.056 ↑ 1.0 1 14

Index Scan using insecure_record_types_pkey on records.insecure_record_types insecure_record_types_1 (cost=0.29..4.81 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=14)

  • Output: insecure_record_types_1.name, insecure_record_types_1.api_name
  • Index Cond: (((insecure_record_types_1.api_name)::text = insecure_stage_templates_1.record_type) AND (insecure_record_types_1.organization_id = (current_setting('lanetix.organization_id'::text))::integer))
127. 0.056 0.056 ↑ 1.0 1 14

Index Scan using insecure_workflow_templates_organization_id_record_type_sub_idx on records.insecure_workflow_templates wt_3 (cost=0.29..5,666.93 rows=1 width=96) (actual time=0.003..0.004 rows=1 loops=14)

  • Output: wt_3.name, wt_3.record_type, wt_3.api_name
  • Index Cond: ((wt_3.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (wt_3.record_type = (insecure_record_types_1.api_name)::text))
  • Filter: ((wt_3.status <> 'archived'::workflow_template_status) AND ((wt_3.status = ANY ('{published,archived}'::workflow_template_status[])) OR ((current_setting('lanetix.permissions'::text))::text[] && '{customer-administrator,lanetix-staff}'::text[]) OR (SubPlan 19)))
  • Rows Removed by Filter: 0
128.          

SubPlan (for Index Scan)

129. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5,662.11..6,297.68 rows=249 width=0) (never executed)

  • Filter: ((COALESCE((CASE WHEN NULLIF((NOT users_5.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_5.status) <> 'principal'::group_status) OR (alternatives: SubPlan 14 or hashed SubPlan 15))
130.          

Initplan (for Nested Loop Left Join)

131. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=4,790.53..5,660.94 rows=103 width=4) (never executed)

  • Output: g_4.id
  • Merge Cond: (g_4.id = insecure_temp_user_group_14.group_id)
  • Filter: (COALESCE((CASE WHEN NULLIF((NOT users_4.active), false) THEN 'archived'::group_status ELSE NULL::group_status END), g_4.status) = 'everyone'::group_status)
132. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_4 (cost=0.29..503.28 rows=3,205 width=8) (never executed)

  • Output: g_4.organization_id, g_4.id, g_4.status, g_4.name, g_4.description, g_4.reference_metadata
  • Index Cond: (g_4.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
133. 0.000 0.000 ↓ 0.0 0

Sort (cost=4,790.23..4,796.91 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_14.group_id, (CASE WHEN NULLIF((NOT users_4.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Sort Key: insecure_temp_user_group_14.group_id
134. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=156.73..4,638.15 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_14.group_id, (CASE WHEN NULLIF((NOT users_4.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
  • Hash Cond: (users_4.id = insecure_temp_user_group_14.user_id)
135. 0.000 0.000 ↓ 0.0 0

Index Scan using users_organization_id on public.users users_4 (cost=0.29..4,467.44 rows=2,953 width=8) (never executed)

  • Output: users_4.id, CASE WHEN NULLIF((NOT users_4.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_4.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
136. 0.000 0.000 ↓ 0.0 0

Hash (cost=123.03..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_14.group_id, insecure_temp_user_group_14.user_id
137. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_14 (cost=0.29..123.03 rows=2,672 width=8) (never executed)

  • Output: insecure_temp_user_group_14.group_id, insecure_temp_user_group_14.user_id
  • Index Cond: (insecure_temp_user_group_14.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
138. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.60..136.09 rows=39 width=8) (never executed)

  • Output: g_5.id, g_5.status
139. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_permissio_organization_id_schema_permis_idx on records.insecure_temp_group_permission p_2 (cost=0.30..127.77 rows=1 width=4) (never executed)

  • Output: p_2.organization_id, p_2.schema, p_2.group_id, p_2.permission
  • Index Cond: ((p_2.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (p_2.schema = 'records'::text) AND (p_2.permission = (((('recordType'::ltree || wt_3.record_type) || 'workflow'::ltree) || ("substring"(lower(wt_3.api_name), 1, 31))::ltree) || 'permissions.admin'::ltree)))
  • Filter: ((p_2.group_id = $33) OR (alternatives: SubPlan 17 or hashed SubPlan 18))
140.          

SubPlan (for Index Scan)

141. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.44 rows=1 width=0) (never executed)

142. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_15 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_15.organization_id, insecure_temp_user_group_15.user_id, insecure_temp_user_group_15.group_id
  • Index Cond: ((insecure_temp_user_group_15.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_15.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
143. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_4 (cost=0.42..111.11 rows=1 width=4) (never executed)

  • Output: insecure_temp_group_closure_4.organization_id, insecure_temp_group_closure_4.ancestor_id, insecure_temp_group_closure_4.descendent_id
  • Index Cond: ((insecure_temp_group_closure_4.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_4.descendent_id = insecure_temp_user_group_15.group_id))
  • Filter: (insecure_temp_group_closure_4.ancestor_id = p_2.group_id)
144. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..119.70 rows=222 width=4) (never executed)

  • Output: insecure_temp_group_closure_5.ancestor_id
145. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_user_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_16 (cost=0.30..8.32 rows=1 width=4) (never executed)

  • Output: insecure_temp_user_group_16.organization_id, insecure_temp_user_group_16.user_id, insecure_temp_user_group_16.group_id
  • Index Cond: ((insecure_temp_user_group_16.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_16.user_id = (current_setting('lanetix.user_id'::text))::integer))
  • Heap Fetches: 0
146. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_descendent_id on records.insecure_temp_group_closure insecure_temp_group_closure_5 (cost=0.42..111.02 rows=36 width=8) (never executed)

  • Output: insecure_temp_group_closure_5.organization_id, insecure_temp_group_closure_5.ancestor_id, insecure_temp_group_closure_5.descendent_id
  • Index Cond: ((insecure_temp_group_closure_5.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_group_closure_5.descendent_id = insecure_temp_user_group_16.group_id))
147. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_group_pkey on records.insecure_temp_group g_5 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Output: g_5.organization_id, g_5.id, g_5.status, g_5.name, g_5.description, g_5.reference_metadata
  • Index Cond: ((g_5.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (g_5.id = p_2.group_id))
148. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.58..4.51 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_17.group_id, (CASE WHEN NULLIF((NOT users_5.active), false) THEN 'archived'::group_status ELSE NULL::group_status END)
149. 0.000 0.000 ↓ 0.0 0

Index Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_17 (cost=0.29..0.40 rows=1 width=8) (never executed)

  • Output: insecure_temp_user_group_17.organization_id, insecure_temp_user_group_17.user_id, insecure_temp_user_group_17.group_id
  • Index Cond: ((insecure_temp_user_group_17.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_17.group_id = g_5.id))
150. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on public.users users_5 (cost=0.29..4.10 rows=1 width=8) (never executed)

  • Output: users_5.id, CASE WHEN NULLIF((NOT users_5.active), false) THEN 'archived'::group_status ELSE NULL::group_status END
  • Index Cond: (users_5.id = insecure_temp_user_group_17.user_id)
  • Filter: (users_5.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
151.          

SubPlan (for Nested Loop Left Join)

152. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_12 (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: ((insecure_temp_user_group_12.organization_id = (current_setting('lanetix.organization_id'::text))::integer) AND (insecure_temp_user_group_12.group_id = g_5.id))
  • Heap Fetches: 0
153. 0.000 0.000 ↓ 0.0 0

Index Only Scan using insecure_temp_user_group_organization_id_group_id_idx on records.insecure_temp_user_group insecure_temp_user_group_13 (cost=0.29..111.03 rows=2,672 width=4) (never executed)

  • Output: insecure_temp_user_group_13.group_id
  • Index Cond: (insecure_temp_user_group_13.organization_id = (current_setting('lanetix.organization_id'::text))::integer)
  • Heap Fetches: 0
Planning time : 23.474 ms
Execution time : 5,131.109 ms