explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sqkb

Settings
# exclusive inclusive rows x rows loops node
1. 1.085 170.450 ↑ 1.2 329 1

Sort (cost=82,015.80..82,016.80 rows=400 width=158) (actual time=170.442..170.450 rows=329 loops=1)

  • Sort Key: og.sortstring2
  • Sort Method: quicksort Memory: 131kB
2. 0.170 169.365 ↑ 1.2 329 1

Hash Join (cost=81,985.46..81,998.51 rows=400 width=158) (actual time=169.196..169.365 rows=329 loops=1)

  • Hash Cond: (o.id = og.id)
3. 0.260 165.078 ↑ 1.2 329 1

HashAggregate (cost=81,334.67..81,338.67 rows=400 width=5) (actual time=165.043..165.078 rows=329 loops=1)

  • Group Key: o.id, ((SubPlan 1))
4. 0.090 164.818 ↑ 190.3 741 1

Nested Loop (cost=77,805.09..80,629.67 rows=141,000 width=5) (actual time=151.773..164.818 rows=741 loops=1)

5. 0.135 151.330 ↓ 2.3 319 1

Unique (cost=77,804.84..77,808.01 rows=141 width=150) (actual time=151.138..151.330 rows=319 loops=1)

6. 0.331 151.195 ↓ 2.3 319 1

Sort (cost=77,804.84..77,805.19 rows=141 width=150) (actual time=151.137..151.195 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. 0.000 150.864 ↓ 2.3 319 1

Nested Loop (cost=0.53..77,799.81 rows=141 width=150) (actual time=4.524..150.864 rows=319 loops=1)

8. 4.858 4.858 ↓ 6.1 6,124 1

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

9. 12.024 97.984 ↓ 0.0 0 6,124

Index Scan using pk_organisation on organisation org (cost=0.28..68.47 rows=1 width=149) (actual time=0.016..0.016 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. 1.535 85.960 ↑ 1.0 1 1,535

Aggregate (cost=66.05..66.07 rows=1 width=1) (actual time=0.056..0.056 rows=1 loops=1,535)

12. 16.885 84.425 ↑ 1.0 1 1,535

Merge Join (cost=51.17..66.05 rows=1 width=0) (actual time=0.031..0.055 rows=1 loops=1,535)

  • Merge Cond: (rc_1.id = rco_1.roadworkcontractownerid)
13. 6.140 18.420 ↓ 1.5 22 1,535

Sort (cost=50.75..50.78 rows=15 width=4) (actual time=0.011..0.012 rows=22 loops=1,535)

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

Index Scan using ix_dks on roadworkcontract rc_1 (cost=0.29..50.45 rows=15 width=4) (actual time=0.001..0.008 rows=22 loops=1,535)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 50)
15. 49.120 49.120 ↑ 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.005..0.032 rows=244 loops=1,535)

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

SubPlan (forNested Loop)

17. 0.319 49.126 ↑ 1.0 1 319

Aggregate (cost=66.05..66.07 rows=1 width=1) (actual time=0.154..0.154 rows=1 loops=319)

18. 11.484 48.807 ↓ 4.0 4 319

Merge Join (cost=51.17..66.05 rows=1 width=0) (actual time=0.040..0.153 rows=4 loops=319)

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

Sort (cost=50.75..50.78 rows=15 width=4) (actual time=0.012..0.013 rows=22 loops=319)

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

Index Scan using ix_dks on roadworkcontract rc (cost=0.29..50.45 rows=15 width=4) (actual time=0.002..0.009 rows=22 loops=319)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 50)
21. 33.176 33.176 ↓ 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.006..0.104 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.041..0.042 rows=2 loops=319)

23. 1.702 4.117 ↑ 1.0 6,124 1

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

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

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