explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SKHg

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 89.952 1,161.306 ↓ 1.0 2,229 1

GroupAggregate (cost=55,300.81..58,185.52 rows=2,228 width=64) (actual time=1,004.907..1,161.306 rows=2,229 loops=1)

  • Group Key: r.tenant_id, r.asset_id
2.          

CTE aa

3. 1.019 1.293 ↑ 1.0 2,763 1

Bitmap Heap Scan on access_application (cost=316.73..590.18 rows=2,763 width=16) (actual time=0.320..1.293 rows=2,763 loops=1)

  • Recheck Cond: ((tenant_id = '7b26e0d9-d493-30c1-ac75-38d68d70538d'::uuid) AND (application_id = '8a8bb7cd-343a-32ad-99b7-d762030857a2'::uuid))
  • Heap Blocks: exact=148
4. 0.274 0.274 ↑ 1.0 2,763 1

Bitmap Index Scan on idx_access_application_application (cost=0.00..316.04 rows=2,763 width=0) (actual time=0.274..0.274 rows=2,763 loops=1)

  • Index Cond: ((tenant_id = '7b26e0d9-d493-30c1-ac75-38d68d70538d'::uuid) AND (application_id = '8a8bb7cd-343a-32ad-99b7-d762030857a2'::uuid))
5. 403.926 1,071.354 ↓ 1.8 516,318 1

Sort (cost=54,710.63..55,424.85 rows=285,686 width=48) (actual time=1,004.802..1,071.354 rows=516,318 loops=1)

  • Sort Key: r.asset_id
  • Sort Method: external merge Disk: 29360kB
6. 65.959 667.428 ↓ 1.8 516,318 1

Nested Loop (cost=62.59..20,033.17 rows=285,686 width=48) (actual time=2.692..667.428 rows=516,318 loops=1)

7. 2.748 4.661 ↓ 13.8 2,763 1

HashAggregate (cost=62.17..64.17 rows=200 width=16) (actual time=2.609..4.661 rows=2,763 loops=1)

  • Group Key: aa.id
8. 1.913 1.913 ↑ 1.0 2,763 1

CTE Scan on aa (cost=0.00..55.26 rows=2,763 width=16) (actual time=0.323..1.913 rows=2,763 loops=1)

9. 596.808 596.808 ↑ 1.1 187 2,763

Index Scan using idx_security_policy_risk_access_id on security_policy_risk r (cost=0.42..97.84 rows=200 width=48) (actual time=0.008..0.216 rows=187 loops=2,763)

  • Index Cond: ((tenant_id = '7b26e0d9-d493-30c1-ac75-38d68d70538d'::uuid) AND (access_id = aa.id))
Planning time : 0.380 ms
Execution time : 1,170.550 ms