explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v22u

Settings
# exclusive inclusive rows x rows loops node
1. 25.047 6,177.732 ↑ 1.0 1 1

Aggregate (cost=2,945.12..2,945.13 rows=1 width=8) (actual time=6,177.732..6,177.732 rows=1 loops=1)

2. 19.713 6,152.685 ↓ 229.9 121,391 1

Subquery Scan on delegation_data (cost=69.02..2,943.80 rows=528 width=0) (actual time=0.989..6,152.685 rows=121,391 loops=1)

  • Filter: ((NOT delegation_data.is_workflow_closed) OR (delegation_data.is_workflow_closed IS NULL))
  • Rows Removed by Filter: 52
3. 5,981.672 6,132.972 ↓ 115.5 121,443 1

Hash Left Join (cost=69.02..2,933.29 rows=1,051 width=538) (actual time=0.989..6,132.972 rows=121,443 loops=1)

  • Hash Cond: (du.id = ddu.domain_user_id)
  • Filter: (NOT CASE WHEN (ddu.id IS NOT NULL) THEN (ddu.active AND (NOT ddu.deleted) AND (NOT ddu.is_expired)) ELSE false END)
4. 17.632 150.999 ↓ 57.8 121,443 1

Nested Loop (cost=45.54..2,615.50 rows=2,102 width=12) (actual time=0.406..150.999 rows=121,443 loops=1)

5. 0.119 0.335 ↓ 11.5 138 1

Hash Join (cost=16.47..37.81 rows=12 width=8) (actual time=0.103..0.335 rows=138 loops=1)

  • Hash Cond: (rg.entity_type_id = et.id)
6. 0.131 0.155 ↓ 1.0 138 1

Bitmap Heap Scan on role_group rg (cost=9.53..30.24 rows=137 width=8) (actual time=0.029..0.155 rows=138 loops=1)

  • Recheck Cond: (client_id = 1005)
  • Filter: (entity_type_id = ANY ('{1,61,12,13,14,15,16,17,28,18,63,67,80,165,176,181,64,160}'::integer[]))
  • Rows Removed by Filter: 25
  • Heap Blocks: exact=8
7. 0.024 0.024 ↓ 1.0 165 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..9.50 rows=163 width=0) (actual time=0.023..0.024 rows=165 loops=1)

  • Index Cond: (client_id = 1005)
8. 0.009 0.061 ↑ 1.0 23 1

Hash (cost=6.65..6.65 rows=23 width=4) (actual time=0.061..0.061 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.052 0.052 ↑ 1.0 23 1

Seq Scan on entity_type et (cost=0.00..6.65 rows=23 width=4) (actual time=0.017..0.052 rows=23 loops=1)

  • Filter: is_workflow_entity
  • Rows Removed by Filter: 242
10. 108.882 133.032 ↓ 2.5 880 138

Bitmap Heap Scan on domain_user du (cost=29.07..211.31 rows=350 width=16) (actual time=0.237..0.964 rows=880 loops=138)

  • Recheck Cond: (role_group_id = rg.id)
  • Filter: ((NOT entity_deleted) AND (user_id = 1245))
  • Rows Removed by Filter: 2066
  • Heap Blocks: exact=23948
11. 24.150 24.150 ↓ 1.8 2,946 138

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..28.98 rows=1,675 width=0) (actual time=0.175..0.175 rows=2,946 loops=138)

  • Index Cond: (role_group_id = rg.id)
12. 0.143 0.301 ↓ 1.0 635 1

Hash (cost=15.55..15.55 rows=634 width=15) (actual time=0.301..0.301 rows=635 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
13. 0.158 0.158 ↓ 1.0 635 1

Seq Scan on delegated_domain_user ddu (cost=0.00..15.55 rows=634 width=15) (actual time=0.005..0.158 rows=635 loops=1)

  • Filter: active
  • Rows Removed by Filter: 20
Planning time : 0.947 ms
Execution time : 6,177.867 ms