explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EefL

Settings
# exclusive inclusive rows x rows loops node
1. 25.866 25.866 ↑ 1.0 1 1

Aggregate (cost=51.01..51.02 rows=1 width=8) (actual time=25.866..25.866 rows=1 loops=1)

  • -> CTE Scan on ctv_h th (cost=0.00..0.02 rows=1 width=0) (actual time=0.480..25.707 rows=501 loops=1);
  • ,ctv_h AS
  • SELECT COUNT(1)
2.          

CTE ctv_ins

3. 11.217 22.911 ↓ 501.0 501 1

Insert on user_group (cost=45.03..50.96 rows=1 width=82) (actual time=0.449..22.911 rows=501 loops=1)

4. 0.312 11.694 ↓ 501.0 501 1

Subquery Scan on *SELECT* (cost=45.03..50.96 rows=1 width=82) (actual time=0.382..11.694 rows=501 loops=1)

5. 5.934 11.382 ↓ 501.0 501 1

Nested Loop Anti Join (cost=45.03..50.94 rows=1 width=66) (actual time=0.380..11.382 rows=501 loops=1)

  • Join Filter: (ug.group_id = gre.group_id)
  • Rows Removed by Join Filter: 451
6. 0.461 0.939 ↓ 501.0 501 1

Merge Join (cost=44.75..50.41 rows=1 width=32) (actual time=0.348..0.939 rows=501 loops=1)

  • Merge Cond: ((t.entity_id = gre.entity_id) AND (t.business_entity_id = gre.business_entity_id) AND (t.role_id = gre.role_id))
7. 0.258 0.335 ↑ 1.0 501 1

Sort (cost=34.48..35.73 rows=501 width=64) (actual time=0.277..0.335 rows=501 loops=1)

  • Sort Key: t.entity_id, t.business_entity_id, t.role_id
  • Sort Method: quicksort Memory: 95kB
8. 0.077 0.077 ↑ 1.0 501 1

Seq Scan on tmp_ure t (cost=0.00..12.01 rows=501 width=64) (actual time=0.007..0.077 rows=501 loops=1)

9. 0.120 0.143 ↓ 4.3 553 1

Sort (cost=9.88..10.21 rows=130 width=64) (actual time=0.067..0.143 rows=553 loops=1)

  • Sort Key: gre.entity_id, gre.business_entity_id, gre.role_id
  • Sort Method: quicksort Memory: 40kB
10. 0.023 0.023 ↑ 1.1 116 1

Seq Scan on group_role_entity gre (cost=0.00..5.32 rows=130 width=64) (actual time=0.004..0.023 rows=116 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 1
11. 4.509 4.509 ↑ 1.0 1 501

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

  • Index Cond: (user_id = t.user_id)
12.          

CTE ctv_h

13. 2.018 25.451 ↓ 501.0 501 1

Insert on user_group user_group_1 (cost=0.00..0.03 rows=1 width=83) (actual time=0.479..25.451 rows=501 loops=1)

  • INSERT INTO tvs_dbo.user_group
  • (user_group_id, group_id, user_id, client_partition_id, modified_by)
  • SELECT NEWID()
  • FROM tmp_ure AS t
  • JOIN tvs_dbo.group_role_entity AS gre
  • ON gre.is_primary = true
  • AND gre.entity_id = t.entity_id
  • AND gre.business_entity_id = t.business_entity_id
  • AND gre.role_id = t.role_id
  • AND NOT EXISTS(SELECT 1 FROM tvs_dbo.user_group AS ug WHERE ug.group_id = gre.group_id AND ug.user_id = t.user_id)
  • RETURNING user_group_id, group_id, user_id, client_partition_id, modified_by
  • INSERT INTO tvs_history.user_group
  • (user_group_id, group_id, user_id, client_partition_id, modified_by)
  • SELECT t.user_group_id
  • FROM ctv_ins AS t
  • RETURNING user_group_id
14. 23.433 23.433 ↓ 501.0 501 1

CTE Scan on ctv_ins t_1 (cost=0.00..0.03 rows=1 width=83) (actual time=0.451..23.433 rows=501 loops=1)

  • ,gre.group_id
  • ,t.user_id
  • ,m_client_partition_id
  • ,p_current_user_id
  • ,t.group_id
  • ,t.user_id
  • ,t.client_partition_id
  • ,t.modified_by
  • FROM ctv_h AS th