explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SJi4

Settings
# exclusive inclusive rows x rows loops node
1. 26.705 6,196.661 ↑ 1.0 1 1

Aggregate (cost=3,217.00..3,217.01 rows=1 width=8) (actual time=6,196.661..6,196.661 rows=1 loops=1)

2. 20.884 6,169.956 ↓ 115.0 121,391 1

Subquery Scan on delegation_data (cost=45.54..3,214.36 rows=1,056 width=0) (actual time=0.691..6,169.956 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. 6,000.641 6,149.072 ↓ 57.8 121,443 1

Nested Loop Left Join (cost=45.54..3,193.34 rows=2,102 width=537) (actual time=0.690..6,149.072 rows=121,443 loops=1)

  • Join Filter: ((du.entity_id = ddu.entity_id) AND (du.role_group_id = ddu.role_group_id))
4. 15.771 148.431 ↓ 57.8 121,443 1

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

5. 0.118 0.318 ↓ 11.5 138 1

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

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

Bitmap Heap Scan on role_group rg (cost=9.53..30.24 rows=137 width=8) (actual time=0.027..0.142 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.022 0.022 ↓ 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.022..0.022 rows=165 loops=1)

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

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

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

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

  • Filter: is_workflow_entity
  • Rows Removed by Filter: 242
10. 109.572 132.342 ↓ 2.5 880 138

Bitmap Heap Scan on domain_user du (cost=29.07..211.31 rows=350 width=12) (actual time=0.225..0.959 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. 22.770 22.770 ↓ 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.165..0.165 rows=2,946 loops=138)

  • Index Cond: (role_group_id = rg.id)
12. 0.000 0.000 ↓ 0.0 0 121,443

Materialize (cost=0.00..15.56 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=121,443)

13. 0.079 0.079 ↓ 0.0 0 1

Seq Scan on delegated_domain_user ddu (cost=0.00..15.55 rows=1 width=12) (actual time=0.079..0.079 rows=0 loops=1)

  • Filter: ((NOT active) AND deleted AND is_expired)
  • Rows Removed by Filter: 655
Planning time : 1.144 ms
Execution time : 6,196.871 ms