explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PZmb

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 251.180 ↓ 0.0 0 1

Update on roadworkrequest (cost=296,822.98..330,983.78 rows=24,377 width=1,289) (actual time=251.180..251.180 rows=0 loops=1)

2. 0.000 251.177 ↓ 0.0 0 1

Nested Loop (cost=296,822.98..330,983.78 rows=24,377 width=1,289) (actual time=251.177..251.177 rows=0 loops=1)

3. 0.116 251.177 ↓ 0.0 0 1

HashAggregate (cost=296,822.55..297,066.32 rows=24,377 width=98) (actual time=251.177..251.177 rows=0 loops=1)

  • Group Key: roadworkrequest_1.id
4. 0.004 251.061 ↓ 0.0 0 1

Merge Join (cost=270,784.45..296,761.61 rows=24,377 width=98) (actual time=251.061..251.061 rows=0 loops=1)

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

Sort (cost=55,021.36..55,128.91 rows=43,018 width=66) (actual time=251.057..251.057 rows=0 loops=1)

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

Hash Join (cost=34.50..51,710.56 rows=43,018 width=66) (actual time=251.044..251.044 rows=0 loops=1)

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

Hash Join (cost=17.25..50,988.89 rows=86,037 width=42) (actual time=32.697..246.529 rows=4 loops=1)

  • Hash Cond: (roadworkrequest_1.roadworkrequestownerorganisationid = o1.id)
8. 236.807 236.807 ↑ 1.0 169,210 1

Seq Scan on roadworkrequest roadworkrequest_1 (cost=0.00..49,562.78 rows=172,074 width=18) (actual time=0.019..236.807 rows=169,210 loops=1)

  • Filter: (psmexportid IS NULL)
  • Rows Removed by Filter: 926568
9. 0.001 0.258 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.004 0.257 ↑ 200.0 1 1

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

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

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

12. 0.003 4.502 ↑ 33.3 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.011 4.499 ↑ 33.3 6 1

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

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

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