explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Anqo

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 20.781 ↑ 3.0 1 1

HashAggregate (cost=1,606.62..1,606.67 rows=3 width=155) (actual time=20.781..20.781 rows=1 loops=1)

  • Group Key: "*VALUES*".column1, "*VALUES*".column2, t.role_id, t.source_entit_code, t.source_entity_id
2.          

CTE ctv

3. 0.000 0.136 ↑ 1.0 1 1

Unique (cost=33.95..33.97 rows=1 width=106) (actual time=0.136..0.136 rows=1 loops=1)

4.          

Initplan (for Unique)

5. 0.019 0.019 ↑ 1.0 1 1

Index Scan using uq_user_userid_clientid on "user" t_1 (cost=0.28..8.30 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (user_id = $1)
  • Filter: ((deleted_date IS NULL) AND ((status_code)::text ~~* 'active'::text))
6. 0.010 0.135 ↑ 1.0 1 1

Sort (cost=25.66..25.66 rows=1 width=106) (actual time=0.135..0.135 rows=1 loops=1)

  • Sort Key: t_3.role_id, t_3.entity_id, ((t_6.code)::character varying(20))
  • Sort Method: quicksort Memory: 25kB
7. 0.021 0.125 ↑ 1.0 1 1

Result (cost=0.97..25.65 rows=1 width=106) (actual time=0.122..0.125 rows=1 loops=1)

  • One-Time Filter: $0
8. 0.006 0.104 ↑ 1.0 1 1

Nested Loop (cost=0.97..25.65 rows=1 width=106) (actual time=0.101..0.104 rows=1 loops=1)

  • Join Filter: (((t_6.business_entity_id = t_3.business_entity_id) AND ((t_6.code)::text ~~* 'unit'::text)) OR ((t_6.code)::text ~~* 'corporate'::text))
  • Rows Removed by Join Filter: 1
9. 0.000 0.047 ↑ 1.0 1 1

Nested Loop (cost=0.97..14.43 rows=1 width=64) (actual time=0.045..0.047 rows=1 loops=1)

10. 0.001 0.036 ↑ 1.0 1 1

Nested Loop (cost=0.83..14.26 rows=1 width=80) (actual time=0.034..0.036 rows=1 loops=1)

11. 0.004 0.025 ↑ 1.0 1 1

Nested Loop (cost=0.55..12.59 rows=1 width=64) (actual time=0.023..0.025 rows=1 loops=1)

12. 0.009 0.009 ↑ 1.0 1 1

Index Scan using ix_usergroup_userid on user_group t_2 (cost=0.28..8.29 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (user_id = $1)
13. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using uq_group_role_entity on group_role_entity t_3 (cost=0.27..4.29 rows=1 width=64) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (group_id = t_2.group_id)
  • Heap Fetches: 0
14. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_role on role t_4 (cost=0.28..1.66 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (role_id = t_3.role_id)
15. 0.011 0.011 ↑ 1.0 1 1

Index Scan using pk_status_status_id on status t_5 (cost=0.14..0.17 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (status_id = t_4.status_id)
  • Filter: ((code)::text ~~* 'active'::text)
16. 0.051 0.051 ↓ 2.0 2 1

Seq Scan on business_entity t_6 (cost=0.00..11.20 rows=1 width=434) (actual time=0.014..0.051 rows=2 loops=1)

  • Filter: (((code)::text ~~* 'unit'::text) OR ((code)::text ~~* 'corporate'::text))
  • Rows Removed by Filter: 31
17.          

CTE ctv2

18. 0.002 20.499 ↑ 1.0 1 1

Nested Loop Left Join (cost=675.73..1,539.56 rows=1 width=154) (actual time=20.495..20.499 rows=1 loops=1)

19. 0.003 20.495 ↑ 1.0 1 1

Nested Loop Left Join (cost=675.45..1,539.24 rows=1 width=186) (actual time=20.492..20.495 rows=1 loops=1)

  • Join Filter: ((t_7.be_code)::text ~~* 'corporate'::text)
20. 0.001 20.481 ↑ 1.0 1 1

Nested Loop Left Join (cost=675.18..1,530.93 rows=1 width=170) (actual time=20.479..20.481 rows=1 loops=1)

  • Join Filter: ((t_7.be_code)::text ~~* 'unit'::text)
21. 1.121 20.478 ↑ 1.0 1 1

Hash Right Join (cost=674.90..1,522.62 rows=1 width=138) (actual time=20.475..20.478 rows=1 loops=1)

  • Hash Cond: ("*SELECT* 1".inspection_stage_id = t_7.entity_id)
  • Join Filter: ((t_7.be_code)::text ~~* 'inspection'::text)
22. 0.876 19.217 ↑ 1.0 13,634 1

Append (cost=674.86..1,470.61 rows=13,634 width=32) (actual time=6.649..19.217 rows=13,634 loops=1)

23. 1.092 18.092 ↑ 1.0 13,616 1

Subquery Scan on *SELECT* 1 (cost=674.86..1,310.48 rows=13,616 width=32) (actual time=6.649..18.092 rows=13,616 loops=1)

24. 5.721 17.000 ↑ 1.0 13,616 1

Hash Join (cost=674.86..1,174.32 rows=13,616 width=183) (actual time=6.648..17.000 rows=13,616 loops=1)

  • Hash Cond: ((t_11.if_id = inf.if_id) AND (t_11.if_version = inf.if_version))
25. 3.273 5.730 ↑ 1.0 13,616 1

Hash Join (cost=110.16..538.12 rows=13,616 width=53) (actual time=1.005..5.730 rows=13,616 loops=1)

  • Hash Cond: (t_11.unit_id = u.unit_id)
26. 1.481 1.481 ↑ 1.0 13,616 1

Seq Scan on inspection t_11 (cost=0.00..392.16 rows=13,616 width=53) (actual time=0.003..1.481 rows=13,616 loops=1)

27. 0.477 0.976 ↓ 1.0 2,587 1

Hash (cost=77.85..77.85 rows=2,585 width=16) (actual time=0.976..0.976 rows=2,587 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 154kB
28. 0.499 0.499 ↓ 1.0 2,587 1

Seq Scan on unit u (cost=0.00..77.85 rows=2,585 width=16) (actual time=0.004..0.499 rows=2,587 loops=1)

29. 3.438 5.549 ↑ 1.0 15,108 1

Hash (cost=338.08..338.08 rows=15,108 width=21) (actual time=5.549..5.549 rows=15,108 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 911kB
30. 2.111 2.111 ↑ 1.0 15,108 1

Seq Scan on inspection_form inf (cost=0.00..338.08 rows=15,108 width=21) (actual time=0.005..2.111 rows=15,108 loops=1)

31. 0.003 0.249 ↑ 1.0 18 1

Subquery Scan on *SELECT* 2 (cost=0.85..160.13 rows=18 width=32) (actual time=0.090..0.249 rows=18 loops=1)

32. 0.023 0.246 ↑ 1.0 18 1

Nested Loop (cost=0.85..159.95 rows=18 width=183) (actual time=0.089..0.246 rows=18 loops=1)

33. 0.015 0.169 ↑ 1.0 18 1

Nested Loop (cost=0.57..152.41 rows=18 width=53) (actual time=0.066..0.169 rows=18 loops=1)

34. 0.020 0.100 ↑ 1.0 18 1

Nested Loop (cost=0.29..146.63 rows=18 width=53) (actual time=0.045..0.100 rows=18 loops=1)

35. 0.008 0.008 ↑ 1.0 18 1

Seq Scan on inspection_part t_12 (cost=0.00..1.18 rows=18 width=32) (actual time=0.006..0.008 rows=18 loops=1)

36. 0.072 0.072 ↑ 1.0 1 18

Index Scan using idx_inspection_inspectionid on inspection ins (cost=0.29..8.08 rows=1 width=53) (actual time=0.004..0.004 rows=1 loops=18)

  • Index Cond: (inspection_id = t_12.inspection_id)
37. 0.054 0.054 ↑ 1.0 1 18

Index Only Scan using pk_unit on unit u_1 (cost=0.28..0.32 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=18)

  • Index Cond: (unit_id = ins.unit_id)
  • Heap Fetches: 18
38. 0.054 0.054 ↑ 1.0 1 18

Index Only Scan using pk_if_inspection_form_if_id_if_version on inspection_form inf_1 (cost=0.29..0.42 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=18)

  • Index Cond: ((if_id = ins.if_id) AND (if_version = ins.if_version))
  • Heap Fetches: 18
39. 0.002 0.140 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=106) (actual time=0.140..0.140 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.138 0.138 ↑ 1.0 1 1

CTE Scan on ctv t_7 (cost=0.00..0.02 rows=1 width=106) (actual time=0.137..0.138 rows=1 loops=1)

41. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pk_unit on unit t_8 (cost=0.28..8.30 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (unit_id = t_7.entity_id)
42. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using pk_corporate on corporate t_9 (cost=0.28..8.29 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (corporate_id = t_7.entity_id)
  • Heap Fetches: 0
43. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pk_unit on unit t_10 (cost=0.28..0.32 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (unit_id = "*SELECT* 1".unit_id)
44.          

CTE ctv3

45. 0.003 20.669 ↑ 2.0 1 1

Unique (cost=11.12..11.16 rows=2 width=186) (actual time=20.668..20.669 rows=1 loops=1)

46. 0.018 20.666 ↑ 2.0 1 1

Sort (cost=11.12..11.12 rows=2 width=186) (actual time=20.665..20.666 rows=1 loops=1)

  • Sort Key: t2.user_id, t2.role_id, t2.be_code, t2.entity_id, (CASE ((t_13.corporate_id = t_13.parent_corporate_id) OR (t2.corporate_id IS NULL)) WHEN CASE_TEST_EXPR THEN t2.be_code ELSE NULL::character varying END), ("isnull"(t_13.parent_corporate_id, 'ffffffff-ffff-ffff-ffff-ffffffffffff'::uuid)), (CASE ((t_13.corporate_id = t_13.parent_corporate_id) OR (t2.corporate_id IS NULL)) WHEN CASE_TEST_EXPR THEN t2.unit_id ELSE '00000000-0000-0000-0000-000000000000'::uuid END), (CASE ((t_13.corporate_id = t_13.parent_corporate_id) OR (t2.corporate_id IS NULL)) WHEN CASE_TEST_EXPR THEN t2.inspection_id ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
  • Sort Method: quicksort Memory: 25kB
47. 0.143 20.648 ↑ 2.0 1 1

Nested Loop Left Join (cost=4.30..11.11 rows=2 width=186) (actual time=20.645..20.648 rows=1 loops=1)

48. 20.502 20.502 ↑ 1.0 1 1

CTE Scan on ctv2 t2 (cost=0.00..0.02 rows=1 width=154) (actual time=20.498..20.502 rows=1 loops=1)

49. 0.002 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on corporate_with_parent t_13 (cost=4.30..10.55 rows=2 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (corporate_id = t2.corporate_id)
50. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on pk_corporate_with_parent (cost=0.00..4.29 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (corporate_id = t2.corporate_id)
51.          

CTE ctv4

52. 0.001 20.757 ↑ 2.0 1 1

Unique (cost=21.69..21.74 rows=2 width=186) (actual time=20.756..20.757 rows=1 loops=1)

53. 0.010 20.756 ↑ 2.0 1 1

Sort (cost=21.69..21.70 rows=2 width=186) (actual time=20.756..20.756 rows=1 loops=1)

  • Sort Key: t3.user_id, t3.role_id, t3.source_entit_code, t3.source_entity_id, t3.be_code, ("isnull"(t_14.corporate_id, t3.corporate_id)), (CASE (t_14.corporate_id IS NULL) WHEN CASE_TEST_EXPR THEN t3.unit_id ELSE NULL::uuid END), (CASE (t_14.corporate_id IS NULL) WHEN CASE_TEST_EXPR THEN t3.inspection_id ELSE NULL::uuid END)
  • Sort Method: quicksort Memory: 25kB
54. 0.064 20.746 ↑ 2.0 1 1

Nested Loop Left Join (cost=4.30..21.68 rows=2 width=186) (actual time=20.745..20.746 rows=1 loops=1)

  • Join Filter: (t3.unit_id IS NULL)
55. 20.670 20.670 ↑ 2.0 1 1

CTE Scan on ctv3 t3 (cost=0.00..0.04 rows=2 width=186) (actual time=20.669..20.670 rows=1 loops=1)

56. 0.004 0.012 ↓ 0.0 0 1

Bitmap Heap Scan on corporate_with_parent t_14 (cost=4.30..10.55 rows=2 width=32) (actual time=0.011..0.012 rows=0 loops=1)

  • Recheck Cond: (parent_corporate_id = t3.corporate_id)
57. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on uidx_corporate_with_parent_parent_corporate_id (cost=0.00..4.29 rows=2 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (parent_corporate_id = t3.corporate_id)
58. 0.002 20.765 ↑ 3.0 1 1

Nested Loop (cost=0.00..0.15 rows=3 width=170) (actual time=20.764..20.765 rows=1 loops=1)

  • SELECT DISTINCT ISNULL(t.corporate_id,m_fguid)
  • FROM tvs_security_service.fn_get_user_access_to_list_corporate(p_current_user_id) AS t
59. 20.758 20.758 ↑ 2.0 1 1

CTE Scan on ctv4 t (cost=0.00..0.04 rows=2 width=154) (actual time=20.758..20.758 rows=1 loops=1)

60. 0.005 0.005 ↑ 2.0 1 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=48) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((t.corporate_id = column2) OR (t.unit_id = column2))
  • Rows Removed by Filter: 1;