explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UbcX : Optimization for: plan #FxuK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 1,304.217 ↑ 29.6 189 1

Finalize GroupAggregate (cost=874,761.06..875,471.96 rows=5,596 width=81) (actual time=1,297.175..1,304.217 rows=189 loops=1)

  • Group Key: issues.customer_hash, issues.resource_group_id, issues.policy, issues.beam_id
2. 5.427 1,306.975 ↑ 8.6 542 1

Gather Merge (cost=874,761.06..875,357.70 rows=4,664 width=81) (actual time=1,297.170..1,306.975 rows=542 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 4.224 1,301.548 ↑ 12.9 181 3 / 3

Partial GroupAggregate (cost=873,761.04..873,819.34 rows=2,332 width=81) (actual time=1,294.738..1,301.548 rows=181 loops=3)

  • Group Key: issues.customer_hash, issues.resource_group_id, issues.policy, issues.beam_id
4. 77.650 1,297.324 ↓ 12.1 28,314 3 / 3

Sort (cost=873,761.04..873,766.87 rows=2,332 width=73) (actual time=1,294.723..1,297.324 rows=28,314 loops=3)

  • Sort Key: issues.customer_hash, issues.resource_group_id, issues.policy, issues.beam_id
  • Sort Method: external sort Disk: 2,488kB
5. 9.851 1,219.674 ↓ 12.1 28,314 3 / 3

Hash Join (cost=2,161.75..873,630.59 rows=2,332 width=73) (actual time=442.949..1,219.674 rows=28,314 loops=3)

  • Hash Cond: ((issues.policy = policy.id) AND (issues.audit = audit.id))
6. 1,186.329 1,186.329 ↓ 2.2 33,518 3 / 3

Parallel Seq Scan on issues (cost=0.00..870,111.50 rows=15,246 width=89) (actual time=419.324..1,186.329 rows=33,518 loops=3)

  • Filter: ((issue_result = 'FAIL'::issue_result) AND (issue_state = 'Open'::issue_state) AND (resource_group_id = ANY ('{33cd1835-c3b4-45a1-949a-c725aa88367a,dc416371-09fb-4df1-853e-c183c3c80d6d,c0eb7ae8-3292-40de-8ef5-422ef26170f3,01307356-855d-44ff-9cef-d952c9efa2d0,1bb2684e-328d-43c4-886b-aa4c39de8c04,67e2212e-bd30-4d8f-86bd-c669931ee1d0,1315ef41-3c9a-4711-aa16-6410742649d4,af2e4e33-5589-4ad0-8473-7f0d94865eb7,6310f5ca-8ab0-4b47-bd9c-77b53c77e11e,9577827d-c696-45a0-afba-47624bca27ff,6416152f-4021-4c03-85c2-cbcf76a6a1c1,0963889c-850a-46e5-a9dc-1ffb4bc4f961,c95b6161-060c-4ff1-b732-b4a6404fefae,cdf4a5d5-9826-48fb-8499-45c5486d809f,4e2521c7-2f1e-4ac0-bac8-37e37ddcc959,69915a8a-f67f-4462-83f1-0c8d5af14a98,15bfd6f3-63c1-47be-aafe-6cb1cb0ae022,9e6ea195-2af4-407d-8fce-5391c8865c1e,c7648e71-e1f7-411f-8957-872e0e230fbc,93c658b6-c971-48d9-8c6e-4d54eb86c95b}'::uuid[])))
  • Rows Removed by Filter: 2,094,526
7. 6.263 23.494 ↓ 1.0 28,199 3 / 3

Hash (cost=1,743.76..1,743.76 rows=27,866 width=64) (actual time=23.494..23.494 rows=28,199 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,900kB
8. 4.923 17.231 ↓ 1.0 28,199 3 / 3

Hash Join (cost=160.99..1,743.76 rows=27,866 width=64) (actual time=0.645..17.231 rows=28,199 loops=3)

  • Hash Cond: (audit_policies.policy = policy.id)
9. 5.308 12.220 ↓ 1.0 28,199 3 / 3

Hash Join (cost=152.31..1,660.44 rows=27,866 width=48) (actual time=0.549..12.220 rows=28,199 loops=3)

  • Hash Cond: (audit_policies.audit = audit.id)
10. 6.378 6.378 ↑ 1.0 28,276 3 / 3

Seq Scan on audit_policies (cost=0.00..1,431.84 rows=28,923 width=32) (actual time=0.006..6.378 rows=28,276 loops=3)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 4,644
11. 0.145 0.534 ↓ 1.0 852 3 / 3

Hash (cost=141.76..141.76 rows=844 width=16) (actual time=0.534..0.534 rows=852 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
12. 0.389 0.389 ↓ 1.0 852 3 / 3

Seq Scan on audit (cost=0.00..141.76 rows=844 width=16) (actual time=0.005..0.389 rows=852 loops=3)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 33
13. 0.038 0.088 ↑ 1.0 208 3 / 3

Hash (cost=6.08..6.08 rows=208 width=16) (actual time=0.088..0.088 rows=208 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
14. 0.050 0.050 ↑ 1.0 208 3 / 3

Seq Scan on policy (cost=0.00..6.08 rows=208 width=16) (actual time=0.019..0.050 rows=208 loops=3)