explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UINF

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 257.963 ↓ 0.0 0 1

Update on roadworkrequest (cost=261,601.55..295,762.35 rows=24,377 width=1,289) (actual time=257.963..257.963 rows=0 loops=1)

2. 0.001 257.957 ↓ 0.0 0 1

Nested Loop (cost=261,601.55..295,762.35 rows=24,377 width=1,289) (actual time=257.957..257.957 rows=0 loops=1)

3. 0.090 257.956 ↓ 0.0 0 1

HashAggregate (cost=261,601.13..261,844.90 rows=24,377 width=98) (actual time=257.956..257.956 rows=0 loops=1)

  • Group Key: roadworkrequest_1.id
4. 0.001 257.866 ↓ 0.0 0 1

Merge Join (cost=235,563.02..261,540.18 rows=24,377 width=98) (actual time=257.866..257.866 rows=0 loops=1)

  • Merge Cond: (roadworkrequest_1.id = history.roadworkrequestid)
5. 0.026 257.865 ↓ 0.0 0 1

Sort (cost=19,799.94..19,907.48 rows=43,018 width=66) (actual time=257.865..257.865 rows=0 loops=1)

  • Sort Key: roadworkrequest_1.id
  • Sort Method: quicksort Memory: 25kB
6. 0.020 257.839 ↓ 0.0 0 1

Hash Join (cost=34.93..16,489.13 rows=43,018 width=66) (actual time=257.839..257.839 rows=0 loops=1)

  • Hash Cond: (roadworkrequest_1.roadworkrequestpsmorganisationid = o2.id)
7. 12.001 211.656 ↑ 21,509.2 4 1

Hash Join (cost=17.68..15,767.46 rows=86,037 width=42) (actual time=59.236..211.656 rows=4 loops=1)

  • Hash Cond: (roadworkrequest_1.roadworkrequestownerorganisationid = o1.id)
8. 184.398 184.398 ↑ 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=15.923..184.398 rows=169,210 loops=1)

  • Index Cond: (psmexportid IS NULL)
9. 0.004 15.257 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=32) (actual time=15.257..15.257 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.010 15.253 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=15.251..15.253 rows=1 loops=1)

  • Group Key: o1.id
11. 15.243 15.243 ↑ 1,000.0 1 1

Function Scan on getorgtree o1 (cost=0.25..10.25 rows=1,000 width=32) (actual time=15.243..15.243 rows=1 loops=1)

12. 0.007 46.163 ↑ 33.3 6 1

Hash (cost=14.75..14.75 rows=200 width=32) (actual time=46.163..46.163 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.019 46.156 ↑ 33.3 6 1

HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=46.153..46.156 rows=6 loops=1)

  • Group Key: o2.id
14. 46.137 46.137 ↑ 166.7 6 1

Function Scan on getorgtree o2 (cost=0.25..10.25 rows=1,000 width=32) (actual time=46.131..46.137 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 : 459.139 ms