explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pExf4

Settings
# exclusive inclusive rows x rows loops node
1. 1.571 2,768.739 ↓ 0.0 0 1

Insert on tmp_ure (cost=0.69..48,237.33 rows=435,346 width=128) (actual time=2,768.739..2,768.739 rows=0 loops=1)

2. 0.264 2,767.168 ↑ 869.0 501 1

Subquery Scan on *SELECT* (cost=0.69..48,237.33 rows=435,346 width=128) (actual time=12.648..2,767.168 rows=501 loops=1)

  • (row_num, user_id, entity_id, business_entity_id, entity_true_view_id, role_id, action_code)
  • SELECT ure.row_num AS row_num
  • FROM tvs_import.user_role_entity AS ure
  • JOIN tvs_external_data.user AS u
  • ON UPPER(u.email) = UPPER(ure.email)
  • AND u.client_id = p_client_id
  • JOIN tvs_dbo.role AS r
  • ON r.is_external = TRUE
  • AND (r.corporate_id = m_top_corporate_id OR r.corporate_id IS NULL)
  • AND r.name ILIKE ure.role_name
  • LEFT JOIN tvs_external_data.corporate AS b
  • ON b.true_view_id ILIKE ure.brand
  • LEFT JOIN tvs_external_data.unit AS un
  • ON un.true_view_id ILIKE ure.unit
  • WHERE 1=1
  • AND ure.session_id = p_import_session_id
  • ORDER BY ure.row_num
3. 1.341 2,766.904 ↑ 869.0 501 1

Nested Loop (cost=0.69..42,795.51 rows=435,346 width=102) (actual time=12.648..2,766.904 rows=501 loops=1)

  • ,u.user_id AS user_id
  • ,CASE
  • WHEN ure.client IS NOT NULL THEN m_corporate_id
  • WHEN ure.brand IS NOT NULL THEN b.corporate_id
  • WHEN ure.unit IS NOT NULL THEN un.unit_id
  • WHEN ure.inspection_id IS NOT NULL THEN ure.inspection_id
  • ELSE NULL
  • END AS entity_id
  • ,CASE
  • WHEN ure.client IS NOT NULL THEN m_corporate_be_id
  • WHEN ure.brand IS NOT NULL THEN m_corporate_be_id
  • WHEN ure.unit IS NOT NULL THEN m_unit_be_id
  • WHEN ure.inspection_id IS NOT NULL THEN m_ins_be_id
  • ELSE NULL
  • END AS business_entity_id
  • ,CASE
  • WHEN ure.client IS NOT NULL THEN ure.client
  • WHEN ure.brand IS NOT NULL THEN ure.brand
  • WHEN ure.unit IS NOT NULL THEN ure.unit
  • WHEN ure.inspection_id IS NOT NULL THEN CAST(ure.inspection_id AS VARCHAR(50))
  • ELSE NULL
  • END AS entity_true_view_id
  • ,r.role_id AS role_id
  • ,ure.action_code AS action_code
4. 2,545.485 2,759.551 ↑ 2.6 501 1

Nested Loop Left Join (cost=0.27..4,360.36 rows=1,283 width=220) (actual time=12.615..2,759.551 rows=501 loops=1)

  • Join Filter: ((un.true_view_id)::text ~~* (ure.unit)::text)
  • Rows Removed by Join Filter: 3673833
5. 4.274 14.668 ↓ 14.3 501 1

Nested Loop Left Join (cost=0.27..268.33 rows=35 width=204) (actual time=0.128..14.668 rows=501 loops=1)

  • Join Filter: ((b.true_view_id)::text ~~* (ure.brand)::text)
  • Rows Removed by Join Filter: 56112
6. 6.853 7.889 ↓ 14.3 501 1

Nested Loop (cost=0.27..206.14 rows=35 width=188) (actual time=0.051..7.889 rows=501 loops=1)

  • Join Filter: ((r.name)::text ~~* (ure.role_name)::text)
  • Rows Removed by Join Filter: 7014
7. 0.535 0.535 ↑ 1.0 501 1

Index Scan using pk_user_role_entity on user_role_entity ure (cost=0.27..98.04 rows=501 width=185) (actual time=0.024..0.535 rows=501 loops=1)

  • Index Cond: (session_id = '64c6d0f1-068e-49e1-a93f-be4db616256e'::uuid)
8. 0.474 0.501 ↓ 1.1 15 501

Materialize (cost=0.00..2.92 rows=14 width=30) (actual time=0.000..0.001 rows=15 loops=501)

9. 0.027 0.027 ↓ 1.1 15 1

Seq Scan on role r (cost=0.00..2.85 rows=14 width=30) (actual time=0.006..0.027 rows=15 loops=1)

  • Filter: (is_external AND ((corporate_id = 'd2b3707e-e6d9-4b47-9bd1-4b40f6a7e4b5'::uuid) OR (corporate_id IS NULL)))
  • Rows Removed by Filter: 53
10. 2.473 2.505 ↑ 1.0 112 501

Materialize (cost=0.00..3.68 rows=112 width=26) (actual time=0.000..0.005 rows=112 loops=501)

11. 0.032 0.032 ↑ 1.0 112 1

Seq Scan on corporate b (cost=0.00..3.12 rows=112 width=26) (actual time=0.003..0.032 rows=112 loops=1)

12. 197.090 199.398 ↑ 1.0 7,334 501

Materialize (cost=0.00..260.01 rows=7,334 width=26) (actual time=0.000..0.398 rows=7,334 loops=501)

13. 2.308 2.308 ↑ 1.0 7,334 1

Seq Scan on unit un (cost=0.00..223.34 rows=7,334 width=26) (actual time=0.005..2.308 rows=7,334 loops=1)

14. 6.012 6.012 ↑ 339.0 1 501

Index Scan using idx_user_email on "user" u (cost=0.42..24.02 rows=339 width=40) (actual time=0.011..0.012 rows=1 loops=501)

  • Index Cond: ((client_id = 'aa8e5674-00f6-41c2-bb98-152988e0b76f'::uuid) AND (upper((email)::text) = upper((ure.email)::text)));