#	exclusive	inclusive	rows x	rows	loops	node
1.	0.003	151.085	↓ 0.0	0	1	→ Update on roadworkrequest (cost=261,601.55..295,762.35 rows=24,377 width=1,289) (actual time=151.085..151.085 rows=0 loops=1)
2.	0.001	151.082	↓ 0.0	0	1	→ Nested Loop (cost=261,601.55..295,762.35 rows=24,377 width=1,289) (actual time=151.082..151.082 rows=0 loops=1)
3.	0.083	151.081	↓ 0.0	0	1	→ HashAggregate (cost=261,601.13..261,844.90 rows=24,377 width=98) (actual time=151.081..151.081 rows=0 loops=1) Group Key: roadworkrequest_1.id
4.	0.001	150.998	↓ 0.0	0	1	→ Merge Join (cost=235,563.02..261,540.18 rows=24,377 width=98) (actual time=150.998..150.998 rows=0 loops=1) Merge Cond: (roadworkrequest_1.id = history.roadworkrequestid)
5.	0.013	150.997	↓ 0.0	0	1	→ Sort (cost=19,799.94..19,907.48 rows=43,018 width=66) (actual time=150.997..150.997 rows=0 loops=1) Sort Key: roadworkrequest_1.id Sort Method: quicksort Memory: 25kB
6.	0.013	150.984	↓ 0.0	0	1	→ Hash Join (cost=34.93..16,489.13 rows=43,018 width=66) (actual time=150.984..150.984 rows=0 loops=1) Hash Cond: (roadworkrequest_1.roadworkrequestpsmorganisationid = o2.id)
7.	9.116	137.228	↑ 21,509.2	4	1	→ Hash Join (cost=17.68..15,767.46 rows=86,037 width=42) (actual time=14.568..137.228 rows=4 loops=1) Hash Cond: (roadworkrequest_1.roadworkrequestownerorganisationid = o1.id)
8.	127.864	127.864	↑ 1.0	169,210	1	→ Index Scan using ix_roadworkrequest_psmexportid on roadworkrequest roadworkrequest_1 (cost=0.43..14,341.35 rows=172,074 width=18) (actual time=0.056..127.864 rows=169,210 loops=1) Index Cond: (psmexportid IS NULL)
9.	0.002	0.248	↑ 200.0	1	1	→ Hash (cost=14.75..14.75 rows=200 width=32) (actual time=0.248..0.248 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
10.	0.004	0.246	↑ 200.0	1	1	→ HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=0.245..0.246 rows=1 loops=1) Group Key: o1.id
11.	0.242	0.242	↑ 1,000.0	1	1	→ Function Scan on getorgtree o1 (cost=0.25..10.25 rows=1,000 width=32) (actual time=0.242..0.242 rows=1 loops=1)
12.	0.005	13.743	↑ 33.3	6	1	→ Hash (cost=14.75..14.75 rows=200 width=32) (actual time=13.743..13.743 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB
13.	0.008	13.738	↑ 33.3	6	1	→ HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=13.737..13.738 rows=6 loops=1) Group Key: o2.id
14.	13.730	13.730	↑ 166.7	6	1	→ Function Scan on getorgtree o2 (cost=0.25..10.25 rows=1,000 width=32) (actual time=13.728..13.730 rows=6 loops=1)
15.	0.000	0.000	↓ 0.0		0	→ Subquery Scan on history (cost=215,763.09..239,729.04 rows=620,940 width=32) (never executed)
16.	0.000	0.000	↓ 0.0		0	→ GroupAggregate (cost=215,763.09..233,519.64 rows=620,940 width=12) (never executed) Group Key: rrh.roadworkrequestid Filter: ((max(rrh."timestamp") >= (to_timestamp('20190205 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (max(rrh."timestamp") <= (to_timestamp('20190305 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
17.	0.000	0.000	↓ 0.0		0	→ Sort (cost=215,763.09..218,072.52 rows=923,772 width=12) (never executed) Sort Key: rrh.roadworkrequestid
18.	0.000	0.000	↓ 0.0		0	→ Bitmap Heap Scan on roadworkrequesthistory rrh (cost=17,295.67..108,441.82 rows=923,772 width=12) (never executed) Recheck Cond: (roadworkstatustypeid = 19)
19.	0.000	0.000	↓ 0.0		0	→ Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..17,064.73 rows=923,772 width=0) (never executed) Index Cond: (roadworkstatustypeid = 19)
20.	0.000	0.000	↓ 0.0		0	→ Index Scan using pk_request on roadworkrequest (cost=0.43..1.41 rows=1 width=1,195) (never executed) Index Cond: (id = roadworkrequest_1.id)

Planning time	:	110.973 ms

"Update on roadworkrequest  (cost=261601.55..295762.35 rows=24377 width=1289) (actual time=151.085..151.085 rows=0 loops=1)"
"  ->  Nested Loop  (cost=261601.55..295762.35 rows=24377 width=1289) (actual time=151.082..151.082 rows=0 loops=1)"
"        ->  HashAggregate  (cost=261601.13..261844.90 rows=24377 width=98) (actual time=151.081..151.081 rows=0 loops=1)"
"              Group Key: roadworkrequest_1.id"
"              ->  Merge Join  (cost=235563.02..261540.18 rows=24377 width=98) (actual time=150.998..150.998 rows=0 loops=1)"
"                    Merge Cond: (roadworkrequest_1.id = history.roadworkrequestid)"
"                    ->  Sort  (cost=19799.94..19907.48 rows=43018 width=66) (actual time=150.997..150.997 rows=0 loops=1)"
"                          Sort Key: roadworkrequest_1.id"
"                          Sort Method: quicksort  Memory: 25kB"
"                          ->  Hash Join  (cost=34.93..16489.13 rows=43018 width=66) (actual time=150.984..150.984 rows=0 loops=1)"
"                                Hash Cond: (roadworkrequest_1.roadworkrequestpsmorganisationid = o2.id)"
"                                ->  Hash Join  (cost=17.68..15767.46 rows=86037 width=42) (actual time=14.568..137.228 rows=4 loops=1)"
"                                      Hash Cond: (roadworkrequest_1.roadworkrequestownerorganisationid = o1.id)"
"                                      ->  Index Scan using ix_roadworkrequest_psmexportid on roadworkrequest roadworkrequest_1  (cost=0.43..14341.35 rows=172074 width=18) (actual time=0.056..127.864 rows=169210 loops=1)"
"                                            Index Cond: (psmexportid IS NULL)"
"                                      ->  Hash  (cost=14.75..14.75 rows=200 width=32) (actual time=0.248..0.248 rows=1 loops=1)"
"                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                            ->  HashAggregate  (cost=12.75..14.75 rows=200 width=32) (actual time=0.245..0.246 rows=1 loops=1)"
"                                                  Group Key: o1.id"
"                                                  ->  Function Scan on getorgtree o1  (cost=0.25..10.25 rows=1000 width=32) (actual time=0.242..0.242 rows=1 loops=1)"
"                                ->  Hash  (cost=14.75..14.75 rows=200 width=32) (actual time=13.743..13.743 rows=6 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 10kB"
"                                      ->  HashAggregate  (cost=12.75..14.75 rows=200 width=32) (actual time=13.737..13.738 rows=6 loops=1)"
"                                            Group Key: o2.id"
"                                            ->  Function Scan on getorgtree o2  (cost=0.25..10.25 rows=1000 width=32) (actual time=13.728..13.730 rows=6 loops=1)"
"                    ->  Subquery Scan on history  (cost=215763.09..239729.04 rows=620940 width=32) (never executed)"
"                          ->  GroupAggregate  (cost=215763.09..233519.64 rows=620940 width=12) (never executed)"
"                                Group Key: rrh.roadworkrequestid"
"                                Filter: ((max(rrh."timestamp") >= (to_timestamp('20190205 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (max(rrh."timestamp") <= (to_timestamp('20190305 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))"
"                                ->  Sort  (cost=215763.09..218072.52 rows=923772 width=12) (never executed)"
"                                      Sort Key: rrh.roadworkrequestid"
"                                      ->  Bitmap Heap Scan on roadworkrequesthistory rrh  (cost=17295.67..108441.82 rows=923772 width=12) (never executed)"
"                                            Recheck Cond: (roadworkstatustypeid = 19)"
"                                            ->  Bitmap Index Scan on ix_roadworkrequesttypeid  (cost=0.00..17064.73 rows=923772 width=0) (never executed)"
"                                                  Index Cond: (roadworkstatustypeid = 19)"
"        ->  Index Scan using pk_request on roadworkrequest  (cost=0.43..1.41 rows=1 width=1195) (never executed)"
"              Index Cond: (id = roadworkrequest_1.id)"
"Planning time: 110.973 ms"
"Execution time: 154.885 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.000 ms	0.0 %
Bitmap Index Scan	1	0.000 ms	0.0 %
Function Scan	2	13.972 ms	9.2 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	2	0.007 ms	0.0 %
Hash Join	2	9.129 ms	6.0 %
HashAggregate	3	0.095 ms	0.1 %
Index Scan	2	127.864 ms	84.6 %
Merge Join	1	0.001 ms	0.0 %
Nested Loop	1	0.001 ms	0.0 %
Sort	2	0.013 ms	0.0 %
Subquery Scan	1	0.000 ms	0.0 %
Update	1	0.003 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
roadworkrequest	3	127.867 ms	84.6 %
Index Scan	2	127.864 ms	100.0 %
Update	1	0.003 ms	0.0 %
roadworkrequesthistory	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HqqD

Color mode:

Visible columns:

Per node type stats

Per table stats