explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ak5B

Settings
# exclusive inclusive rows x rows loops node
1. 0.938 3,456.934 ↑ 1.2 329 1

Sort (cost=474,073.46..474,074.46 rows=400 width=158) (actual time=3,456.925..3,456.934 rows=329 loops=1)

  • Sort Key: og.sortstring2
  • Sort Method: quicksort Memory: 131kB
2. 0.155 3,455.996 ↑ 1.2 329 1

Hash Join (cost=474,043.12..474,056.17 rows=400 width=158) (actual time=3,455.837..3,455.996 rows=329 loops=1)

  • Hash Cond: (o.id = og.id)
3. 0.269 3,451.212 ↑ 1.2 329 1

HashAggregate (cost=473,392.33..473,396.33 rows=400 width=5) (actual time=3,451.176..3,451.212 rows=329 loops=1)

  • Group Key: o.id, ((SubPlan 1))
4. 0.122 3,450.943 ↑ 190.3 741 1

Nested Loop (cost=469,862.75..472,687.33 rows=141,000 width=5) (actual time=3,437.384..3,450.943 rows=741 loops=1)

5. 0.125 3,437.423 ↓ 2.3 319 1

Unique (cost=469,862.50..469,865.67 rows=141 width=150) (actual time=3,437.244..3,437.423 rows=319 loops=1)

6. 0.535 3,437.298 ↓ 2.3 319 1

Sort (cost=469,862.50..469,862.85 rows=141 width=150) (actual time=3,437.244..3,437.298 rows=319 loops=1)

  • Sort Key: org.id, org.name, org.parentid, org.iteration, org.sortstring, org.sortstring2, org.isactive, ((SubPlan 1))
  • Sort Method: quicksort Memory: 124kB
7. 2.100 3,436.763 ↓ 2.3 319 1

Nested Loop (cost=0.53..469,857.46 rows=141 width=150) (actual time=19.907..3,436.763 rows=319 loops=1)

8. 5.399 5.399 ↓ 6.1 6,124 1

Function Scan on getorgtree orgtree (cost=0.25..10.25 rows=1,000 width=4) (actual time=4.061..5.399 rows=6,124 loops=1)

9. 18.719 2,823.164 ↓ 0.0 0 6,124

Index Scan using pk_organisation on organisation org (cost=0.28..412.08 rows=1 width=149) (actual time=0.461..0.461 rows=0 loops=6,124)

  • Index Cond: (id = orgtree.id)
  • Filter: (isactive AND (organisationroleid = 3) AND (SubPlan 2))
  • Rows Removed by Filter: 1
10.          

SubPlan (forIndex Scan)

11. 16.885 2,804.445 ↑ 1.0 1 1,535

Aggregate (cost=409.66..409.68 rows=1 width=1) (actual time=1.827..1.827 rows=1 loops=1,535)

12. 19.955 2,787.560 ↑ 1.0 1 1,535

Merge Join (cost=394.78..409.66 rows=1 width=0) (actual time=1.792..1.816 rows=1 loops=1,535)

  • Merge Cond: (rc_1.id = rco_1.roadworkcontractownerid)
13. 12.280 2,713.880 ↓ 1.5 22 1,535

Sort (cost=394.36..394.39 rows=15 width=4) (actual time=1.768..1.768 rows=22 loops=1,535)

  • Sort Key: rc_1.id
  • Sort Method: quicksort Memory: 26kB
14. 2,701.600 2,701.600 ↓ 1.5 22 1,535

Seq Scan on roadworkcontract rc_1 (cost=0.00..394.06 rows=15 width=4) (actual time=0.001..1.760 rows=22 loops=1,535)

  • Filter: (roadworkcontractpublicspacemanagerid = 50)
  • Rows Removed by Filter: 12143
15. 53.725 53.725 ↑ 1.2 244 1,535

Index Only Scan using ix_roadworkcontractowners_isactive_organisationid_roadworkcontr on roadworkcontractowners rco_1 (cost=0.42..14.47 rows=302 width=4) (actual time=0.008..0.035 rows=244 loops=1,535)

  • Index Cond: ((isactive = true) AND (organisationid = org.id))
  • Filter: isactive
  • Heap Fetches: 0
16.          

SubPlan (forNested Loop)

17. 3.509 606.100 ↑ 1.0 1 319

Aggregate (cost=409.66..409.68 rows=1 width=1) (actual time=1.900..1.900 rows=1 loops=319)

18. 12.122 602.591 ↓ 4.0 4 319

Merge Join (cost=394.78..409.66 rows=1 width=0) (actual time=1.777..1.889 rows=4 loops=319)

  • Merge Cond: (rc.id = rco.roadworkcontractownerid)
19. 2.871 556.974 ↓ 1.5 22 319

Sort (cost=394.36..394.39 rows=15 width=4) (actual time=1.745..1.746 rows=22 loops=319)

  • Sort Key: rc.id
  • Sort Method: quicksort Memory: 26kB
20. 554.103 554.103 ↓ 1.5 22 319

Seq Scan on roadworkcontract rc (cost=0.00..394.06 rows=15 width=4) (actual time=0.002..1.737 rows=22 loops=319)

  • Filter: (roadworkcontractpublicspacemanagerid = 50)
  • Rows Removed by Filter: 12143
21. 33.495 33.495 ↓ 3.0 891 319

Index Only Scan using ix_roadworkcontractowners_isactive_organisationid_roadworkcontr on roadworkcontractowners rco (cost=0.42..14.47 rows=302 width=4) (actual time=0.009..0.105 rows=891 loops=319)

  • Index Cond: ((isactive = true) AND (organisationid = org.id))
  • Filter: isactive
  • Heap Fetches: 0
22. 13.398 13.398 ↑ 500.0 2 319

Function Scan on getparentorgtree o (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.042..0.042 rows=2 loops=319)

23. 2.031 4.629 ↑ 1.0 6,124 1

Hash (cost=574.24..574.24 rows=6,124 width=149) (actual time=4.629..4.629 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1174kB
24. 2.598 2.598 ↑ 1.0 6,124 1

Seq Scan on organisation og (cost=0.00..574.24 rows=6,124 width=149) (actual time=0.027..2.598 rows=6,124 loops=1)