explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fhg7

Settings
# exclusive inclusive rows x rows loops node
1. 1.231 146.152 ↑ 1.1 364 1

Sort (cost=198,246.70..198,247.71 rows=401 width=1,332) (actual time=146.142..146.152 rows=364 loops=1)

  • Sort Key: org.sortstring2
  • Sort Method: quicksort Memory: 150kB
2. 0.309 144.921 ↑ 1.1 364 1

HashAggregate (cost=198,225.36..198,229.37 rows=401 width=1,332) (actual time=144.823..144.921 rows=364 loops=1)

  • Group Key: org.id, org.name, org.parentid, org.organisationroleid, org.iteration, org.sortstring, org.sortstring2, org.isactive, (true), (0), (0)
3. 0.014 144.612 ↑ 1.1 364 1

Append (cost=0.28..198,214.33 rows=401 width=1,332) (actual time=0.092..144.612 rows=364 loops=1)

4. 0.091 0.091 ↑ 1.0 1 1

Index Scan using ix_parent on organisation org (cost=0.28..8.30 rows=1 width=159) (actual time=0.090..0.091 rows=1 loops=1)

  • Index Cond: (parentid IS NULL)
5. 0.177 144.507 ↑ 1.1 363 1

Hash Join (cost=198,192.97..198,202.02 rows=400 width=159) (actual time=144.323..144.507 rows=363 loops=1)

  • Hash Cond: (o.id = og.id)
6. 0.335 134.223 ↑ 1.1 363 1

HashAggregate (cost=197,505.60..197,509.60 rows=400 width=5) (actual time=134.182..134.223 rows=363 loops=1)

  • Group Key: o.id, ((SubPlan 1))
7. 0.264 133.888 ↑ 173.8 823 1

Nested Loop (cost=193,925.95..196,790.60 rows=143,000 width=5) (actual time=118.723..133.888 rows=823 loops=1)

8. 0.137 117.964 ↓ 2.4 348 1

Unique (cost=193,925.70..193,928.92 rows=143 width=151) (actual time=117.777..117.964 rows=348 loops=1)

9. 0.370 117.827 ↓ 2.4 348 1

Sort (cost=193,925.70..193,926.06 rows=143 width=151) (actual time=117.776..117.827 rows=348 loops=1)

  • Sort Key: org_1.id, org_1.name, org_1.parentid, org_1.iteration, org_1.sortstring, org_1.sortstring2, org_1.isactive, ((SubPlan 1))
  • Sort Method: quicksort Memory: 142kB
10. 0.164 117.457 ↓ 2.4 348 1

Nested Loop (cost=0.53..193,920.58 rows=143 width=151) (actual time=8.395..117.457 rows=348 loops=1)

11. 8.141 8.141 ↓ 6.4 6,372 1

Function Scan on getorgtree orgtree (cost=0.25..10.25 rows=1,000 width=4) (actual time=7.201..8.141 rows=6,372 loops=1)

12. 12.144 95.580 ↓ 0.0 0 6,372

Index Scan using pk_organisation on organisation org_1 (cost=0.28..169.97 rows=1 width=150) (actual time=0.015..0.015 rows=0 loops=6,372)

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

SubPlan (forIndex Scan)

14. 1.636 83.436 ↑ 1.0 1 1,636

Aggregate (cost=167.39..167.40 rows=1 width=1) (actual time=0.051..0.051 rows=1 loops=1,636)

15. 0.000 81.800 ↑ 1.0 1 1,636

Nested Loop (cost=0.71..167.39 rows=1 width=0) (actual time=0.043..0.050 rows=1 loops=1,636)

16. 11.452 11.452 ↓ 1.6 22 1,636

Index Scan using ix_roadworkcontract_roadworkcontractpublicspacemanagerid on roadworkcontract rc_1 (cost=0.29..49.02 rows=14 width=4) (actual time=0.001..0.007 rows=22 loops=1,636)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 50)
17. 71.984 71.984 ↓ 0.0 0 35,992

Index Scan using ix_roadworkcontractowners_organisationid_roadworkcontowner on roadworkcontractowners rco_1 (cost=0.42..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=35,992)

  • Index Cond: ((organisationid = org_1.id) AND (roadworkcontractownerid = rc_1.id))
  • Filter: isactive
  • Rows Removed by Filter: 0
18.          

SubPlan (forNested Loop)

19. 0.348 13.572 ↑ 1.0 1 348

Aggregate (cost=167.39..167.40 rows=1 width=1) (actual time=0.039..0.039 rows=1 loops=348)

20. 3.132 13.224 ↓ 4.0 4 348

Nested Loop (cost=0.71..167.39 rows=1 width=0) (actual time=0.025..0.038 rows=4 loops=348)

21. 2.436 2.436 ↓ 1.6 22 348

Index Scan using ix_roadworkcontract_roadworkcontractpublicspacemanagerid on roadworkcontract rc (cost=0.29..49.02 rows=14 width=4) (actual time=0.001..0.007 rows=22 loops=348)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 50)
22. 7.656 7.656 ↓ 0.0 0 7,656

Index Scan using ix_roadworkcontractowners_organisationid_roadworkcontowner on roadworkcontractowners rco (cost=0.42..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=7,656)

  • Index Cond: ((organisationid = org_1.id) AND (roadworkcontractownerid = rc.id))
  • Filter: isactive
  • Rows Removed by Filter: 0
23. 15.660 15.660 ↑ 500.0 2 348

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

24. 2.350 10.107 ↑ 1.0 6,372 1

Hash (cost=607.72..607.72 rows=6,372 width=150) (actual time=10.107..10.107 rows=6,372 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1228kB
25. 7.757 7.757 ↑ 1.0 6,372 1

Seq Scan on organisation og (cost=0.00..607.72 rows=6,372 width=150) (actual time=0.029..7.757 rows=6,372 loops=1)