explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HG61 : Optimization for: plan #iiqq

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 289.018 31,871.160 ↑ 1.0 1 1

Aggregate (cost=13,694.02..13,694.03 rows=1 width=8) (actual time=31,871.159..31,871.160 rows=1 loops=1)

2. 458.072 31,582.142 ↓ 353.9 288,064 1

Subquery Scan on delegation_data (cost=324.57..13,691.99 rows=814 width=0) (actual time=2.211..31,582.142 rows=288,064 loops=1)

  • Filter: ((NOT delegation_data.is_workflow_closed) OR (delegation_data.is_workflow_closed IS NULL))
  • Rows Removed by Filter: 5270
3. 28,331.374 31,124.070 ↓ 181.2 293,334 1

Hash Left Join (cost=324.57..13,675.80 rows=1,619 width=1,178) (actual time=2.208..31,124.070 rows=293,334 loops=1)

  • Hash Cond: (du.id = ddu.domain_user_id)
4. 465.382 2,790.918 ↓ 181.2 293,334 1

Nested Loop (cost=0.86..12,933.16 rows=1,619 width=12) (actual time=0.208..2,790.918 rows=293,334 loops=1)

5. 0.172 1.040 ↓ 11.3 79 1

Merge Join (cost=0.43..78.81 rows=7 width=8) (actual time=0.062..1.040 rows=79 loops=1)

  • Merge Cond: (rg.entity_type_id = et.id)
6. 0.762 0.762 ↑ 1.0 79 1

Index Scan using role_group_entity_type_id_idx on role_group rg (cost=0.28..72.00 rows=80 width=8) (actual time=0.046..0.762 rows=79 loops=1)

  • Index Cond: (entity_type_id = ANY ('{1,61,12,13,14,15,16,17,28,18,63,67,80,165,176,181,64,160}'::integer[]))
  • Filter: (client_id = 1012)
  • Rows Removed by Filter: 1128
7. 0.106 0.106 ↑ 1.0 23 1

Index Scan using pk_entity_type on entity_type et (cost=0.15..10.59 rows=23 width=4) (actual time=0.011..0.106 rows=23 loops=1)

  • Filter: is_workflow_entity
  • Rows Removed by Filter: 132
8. 2,324.496 2,324.496 ↓ 4.9 3,713 79

Index Scan using idx_domain_user_3 on domain_user du (cost=0.43..1,828.78 rows=756 width=16) (actual time=20.958..29.424 rows=3,713 loops=79)

  • Index Cond: (role_group_id = rg.id)
  • Filter: ((NOT entity_deleted) AND (user_id = 27984))
  • Rows Removed by Filter: 84210
9. 0.311 1.778 ↑ 1.0 420 1

Hash (cost=318.46..318.46 rows=420 width=8) (actual time=1.778..1.778 rows=420 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
10. 1.467 1.467 ↑ 1.0 420 1

Seq Scan on delegated_domain_user ddu (cost=0.00..318.46 rows=420 width=8) (actual time=0.005..1.467 rows=420 loops=1)

  • Filter: active
  • Rows Removed by Filter: 13726
Planning time : 0.969 ms
Execution time : 31,871.281 ms