explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ffzX

Settings
# exclusive inclusive rows x rows loops node
1. 1.112 179.026 ↑ 1.2 329 1

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

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

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

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

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

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

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

5. 0.133 156.345 ↓ 2.3 319 1

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

6. 0.366 156.212 ↓ 2.3 319 1

Sort (cost=77,804.84..77,805.19 rows=141 width=150) (actual time=156.151..156.212 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. 1.707 155.846 ↓ 2.3 319 1

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

8. 7.986 7.986 ↓ 6.1 6,124 1

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

9. 8.954 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 89.030 ↑ 1.0 1 1,535

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

12. 16.885 87.495 ↑ 1.0 1 1,535

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

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

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

  • Sort Key: rc_1.id
  • Sort Method: quicksort Memory: 26kB
14. 10.745 10.745 ↓ 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.007 rows=22 loops=1,535)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 50)
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.006..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. 0.638 48.169 ↑ 1.0 1 319

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

18. 11.165 47.531 ↓ 4.0 4 319

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

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

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

  • Sort Key: rc.id
  • Sort Method: quicksort Memory: 26kB
20. 2.552 2.552 ↓ 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.008 rows=22 loops=319)

  • Index Cond: (roadworkcontractpublicspacemanagerid = 50)
21. 32.538 32.538 ↓ 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.102 rows=891 loops=319)

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

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

23. 2.019 5.700 ↑ 1.0 6,124 1

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

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

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