explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HqqD

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