#	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)

"Sort  (cost=82015.80..82016.80 rows=400 width=158) (actual time=179.017..179.026 rows=329 loops=1)"
"  Sort Key: og.sortstring2"
"  Sort Method: quicksort  Memory: 131kB"
"  ->  Hash Join  (cost=81985.46..81998.51 rows=400 width=158) (actual time=177.744..177.914 rows=329 loops=1)"
"        Hash Cond: (o.id = og.id)"
"        ->  HashAggregate  (cost=81334.67..81338.67 rows=400 width=5) (actual time=172.013..172.058 rows=329 loops=1)"
"              Group Key: o.id, ((SubPlan 1))"
"              ->  Nested Loop  (cost=77805.09..80629.67 rows=141000 width=5) (actual time=157.499..171.780 rows=741 loops=1)"
"                    ->  Unique  (cost=77804.84..77808.01 rows=141 width=150) (actual time=156.152..156.345 rows=319 loops=1)"
"                          ->  Sort  (cost=77804.84..77805.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"
"                                ->  Nested Loop  (cost=0.53..77799.81 rows=141 width=150) (actual time=8.702..155.846 rows=319 loops=1)"
"                                      ->  Function Scan on getorgtree orgtree  (cost=0.25..10.25 rows=1000 width=4) (actual time=7.139..7.986 rows=6124 loops=1)"
"                                      ->  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=6124)"
"                                            Index Cond: (id = orgtree.id)"
"                                            Filter: (isactive AND (organisationroleid = 3) AND (SubPlan 2))"
"                                            Rows Removed by Filter: 1"
"                                            SubPlan 2"
"                                              ->  Aggregate  (cost=66.05..66.07 rows=1 width=1) (actual time=0.058..0.058 rows=1 loops=1535)"
"                                                    ->  Merge Join  (cost=51.17..66.05 rows=1 width=0) (actual time=0.031..0.057 rows=1 loops=1535)"
"                                                          Merge Cond: (rc_1.id = rco_1.roadworkcontractownerid)"
"                                                          ->  Sort  (cost=50.75..50.78 rows=15 width=4) (actual time=0.010..0.011 rows=22 loops=1535)"
"                                                                Sort Key: rc_1.id"
"                                                                Sort Method: quicksort  Memory: 26kB"
"                                                                ->  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=1535)"
"                                                                      Index Cond: (roadworkcontractpublicspacemanagerid = 50)"
"                                                          ->  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=1535)"
"                                                                Index Cond: ((isactive = true) AND (organisationid = org.id))"
"                                                                Filter: isactive"
"                                                                Heap Fetches: 0"
"                                      SubPlan 1"
"                                        ->  Aggregate  (cost=66.05..66.07 rows=1 width=1) (actual time=0.151..0.151 rows=1 loops=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)"
"                                                    ->  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"
"                                                          ->  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)"
"                                                    ->  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"
"                    ->  Function Scan on getparentorgtree o  (cost=0.25..10.25 rows=1000 width=4) (actual time=0.048..0.048 rows=2 loops=319)"
"        ->  Hash  (cost=574.24..574.24 rows=6124 width=149) (actual time=5.700..5.700 rows=6124 loops=1)"
"              Buckets: 8192  Batches: 1  Memory Usage: 1174kB"
"              ->  Seq Scan on organisation og  (cost=0.00..574.24 rows=6124 width=149) (actual time=0.044..3.681 rows=6124 loops=1)"
"Planning time: 3.858 ms"
"Execution time: 184.378 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	2.173 ms	1.2 %
Function Scan	2	23.298 ms	13.0 %
Hash	1	2.019 ms	1.1 %
Hash Join	1	0.156 ms	0.1 %
HashAggregate	1	0.278 ms	0.2 %
Index Only Scan	2	86.263 ms	48.2 %
Index Scan	3	22.251 ms	12.4 %
Merge Join	2	28.050 ms	15.7 %
Nested Loop	2	1.830 ms	1.0 %
Seq Scan	1	3.681 ms	2.1 %
Sort	4	8.894 ms	5.0 %
Unique	1	0.133 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
organisation	2	12.635 ms	7.1 %
Index Scan	1	8.954 ms	70.9 %
Seq Scan	1	3.681 ms	29.1 %
roadworkcontract	2	13.297 ms	7.4 %
Index Scan	2	13.297 ms	100.0 %
roadworkcontractowners	2	86.263 ms	48.2 %
Index Only Scan	2	86.263 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ffzX

Color mode:

Visible columns:

Per node type stats

Per table stats