explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xruK

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,000.989 ↓ 0.0 0 1

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

2. 0.001 3,000.986 ↓ 0.0 0 1

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

3. 0.128 3,000.985 ↓ 0.0 0 1

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

  • Group Key: roadworkrequest_1.id
4. 0.000 3,000.857 ↓ 0.0 0 1

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

  • Merge Cond: (roadworkrequest_1.id = history.roadworkrequestid)
5. 0.016 3,000.857 ↓ 0.0 0 1

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

  • Sort Key: roadworkrequest_1.id
  • Sort Method: quicksort Memory: 25kB
6. 0.016 3,000.841 ↓ 0.0 0 1

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

  • Hash Cond: (roadworkrequest_1.roadworkrequestpsmorganisationid = o2.id)
7. 22.993 2,852.993 ↑ 21,509.2 4 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.009 17.757 ↑ 200.0 1 1

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

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

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

12. 0.012 147.832 ↑ 33.3 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.028 147.820 ↑ 33.3 6 1

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

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

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