explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OBLp

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 5,491.385 ↑ 1.0 1 1

Aggregate (cost=446,197.05..446,197.06 rows=1 width=72) (actual time=5,491.385..5,491.385 rows=1 loops=1)

2. 0.000 5,491.361 ↓ 0.0 0 1

Nested Loop (cost=380,856.61..446,195.11 rows=155 width=132) (actual time=5,491.361..5,491.361 rows=0 loops=1)

3. 0.000 5,491.361 ↓ 0.0 0 1

Nested Loop (cost=380,848.16..444,879.93 rows=155 width=100) (actual time=5,491.361..5,491.361 rows=0 loops=1)

4. 0.000 5,491.361 ↓ 0.0 0 1

Nested Loop (cost=380,831.18..442,244.49 rows=155 width=68) (actual time=5,491.361..5,491.361 rows=0 loops=1)

5. 0.000 5,491.361 ↓ 0.0 0 1

Nested Loop (cost=380,822.64..440,915.16 rows=155 width=36) (actual time=5,491.361..5,491.361 rows=0 loops=1)

6. 0.011 5,491.361 ↓ 0.0 0 1

Hash Join (cost=380,814.02..439,575.22 rows=155 width=4) (actual time=5,491.361..5,491.361 rows=0 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestpsmorganisationid = o2.id)
7. 0.485 5,485.625 ↑ 77.5 4 1

Hash Join (cost=380,796.77..439,555.43 rows=310 width=8) (actual time=5,398.178..5,485.625 rows=4 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestownerorganisationid = o1.id)
8. 0.000 5,484.749 ↓ 8.2 5,118 1

Nested Loop (cost=380,779.52..439,533.10 rows=621 width=12) (actual time=4,815.738..5,484.749 rows=5,118 loops=1)

9. 1,833.689 5,434.840 ↓ 1.4 5,643 1

GroupAggregate (cost=380,779.10..412,339.78 rows=3,915 width=12) (actual time=4,803.630..5,434.840 rows=5,643 loops=1)

  • Group Key: rrh.roadworkrequestid
  • Filter: ((max(rrh."timestamp") >= (to_timestamp('20200601 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (max(rrh."timestamp") <= (to_timestamp('20200629 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
  • Rows Removed by Filter: 1,183,438
10. 514.999 3,601.151 ↑ 1.0 1,189,081 1

Sort (cost=380,779.10..383,775.07 rows=1,198,388 width=12) (actual time=3,489.485..3,601.151 rows=1,189,081 loops=1)

  • Sort Key: rrh.roadworkrequestid
  • Sort Method: external merge Disk: 30,280kB
11. 2,862.805 3,086.152 ↑ 1.0 1,189,081 1

Bitmap Heap Scan on roadworkrequesthistory rrh (cost=22,435.94..239,303.87 rows=1,198,388 width=12) (actual time=232.338..3,086.152 rows=1,189,081 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 18)
  • Rows Removed by Index Recheck: 4,086,160
  • Heap Blocks: exact=62,055 lossy=33,031
12. 223.347 223.347 ↑ 1.0 1,189,081 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..22,136.35 rows=1,198,388 width=0) (actual time=223.347..223.347 rows=1,189,081 loops=1)

  • Index Cond: (roadworkstatustypeid = 18)
13. 50.787 50.787 ↑ 1.0 1 5,643

Index Scan using pk_request on roadworkrequest (cost=0.43..6.94 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=5,643)

  • Index Cond: (id = rrh.roadworkrequestid)
  • Filter: (psmexportid IS NULL)
  • Rows Removed by Filter: 0
14. 0.004 0.391 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=0.391..0.391 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.003 0.387 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=0.387..0.387 rows=1 loops=1)

  • Group Key: o1.id
16. 0.384 0.384 ↑ 1,000.0 1 1

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

17. 0.004 5.725 ↑ 100.0 2 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=5.724..5.725 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.005 5.721 ↑ 100.0 2 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=5.720..5.721 rows=2 loops=1)

  • Group Key: o2.id
19. 5.716 5.716 ↑ 500.0 2 1

Function Scan on getorgtree o2 (cost=0.25..10.25 rows=1,000 width=4) (actual time=5.715..5.716 rows=2 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.61..8.62 rows=1 width=32) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial m (cost=0.43..8.61 rows=2 width=6) (never executed)

  • Index Cond: (roadworkrequest.id = roadworkrequestid)
  • Filter: ispsm
22. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.55..8.56 rows=1 width=32) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_roadworkrequestpenalty_roadworkrequestid on roadworkrequestpenalty p (cost=0.42..8.54 rows=1 width=5) (never executed)

  • Index Cond: (roadworkrequest.id = roadworkrequestid)
  • Filter: ispsm
24. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=16.97..16.98 rows=1 width=32) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..16.97 rows=1 width=5) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_roadworkrequestpenalty_roadworkrequestid on roadworkrequestpenalty rrp (cost=0.42..8.54 rows=1 width=9) (never executed)

  • Index Cond: (roadworkrequest.id = roadworkrequestid)
  • Filter: ispsm
27. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_penalty on penalty p_1 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = rrp.penaltytypeid)
  • Filter: islegalfee
28. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.45..8.46 rows=1 width=32) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_roadworkrequestadditional_roadworkrequestid_ispsm on roadworkrequestadditional a (cost=0.43..8.45 rows=1 width=3) (never executed)

  • Index Cond: ((roadworkrequest.id = roadworkrequestid) AND (ispsm = true))
  • Filter: ispsm