explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kt1o

Settings
# exclusive inclusive rows x rows loops node
1. 366.544 3,237.141 ↑ 255.8 7,044 1

Hash Join (cost=578,595.75..715,652.97 rows=1,801,690 width=4) (actual time=2,632.423..3,237.141 rows=7,044 loops=1)

  • Hash Cond: (rwreq.id = roadworkrequest.id)
2.          

CTE ownertreetmp

3. 0.288 0.288 ↑ 1,000.0 1 1

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

4.          

CTE roadworkrequesttemp

5. 361.886 361.886 ↑ 1.0 156,320 1

Seq Scan on roadworkrequest roadworkrequest_4 (cost=0.00..71,213.81 rows=160,746 width=28) (actual time=0.026..361.886 rows=156,320 loops=1)

  • Filter: (roadworkstatustypeid <> ALL ('{15,3,14}'::integer[]))
  • Rows Removed by Filter: 1,220,299
6. 408.192 1,837.771 ↑ 1.0 1,376,619 1

Hash Left Join (cost=2,655.84..118,285.79 rows=1,380,287 width=4) (actual time=28.167..1,837.771 rows=1,376,619 loops=1)

  • Hash Cond: ((rwreq.roadworkcontractid = w.roadworkcontractid) AND (rwreq.worktypeid = w.worktypeid))
7. 182.758 1,417.956 ↑ 1.0 1,376,619 1

Hash Join (cost=1,106.99..82,284.46 rows=1,376,641 width=12) (actual time=16.335..1,417.956 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkstatustypeid = rwstatus.id)
8. 229.256 1,235.167 ↑ 1.0 1,376,619 1

Hash Join (cost=1,105.48..78,003.38 rows=1,376,641 width=16) (actual time=16.300..1,235.167 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkrequestpsmorganisationid = orgpsm.id)
9. 244.745 993.188 ↑ 1.0 1,376,619 1

Hash Join (cost=822.69..74,104.74 rows=1,376,641 width=20) (actual time=3.548..993.188 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkrequestownerorganisationid = orgowner.id)
10. 405.455 747.331 ↑ 1.0 1,376,619 1

Hash Join (cost=539.89..70,206.10 rows=1,376,641 width=24) (actual time=2.400..747.331 rows=1,376,619 loops=1)

  • Hash Cond: (rwreq.roadworkcontractid = rwcont.id)
11. 339.552 339.552 ↑ 1.0 1,376,619 1

Seq Scan on roadworkrequest rwreq (cost=0.00..66,051.41 rows=1,376,641 width=32) (actual time=0.025..339.552 rows=1,376,619 loops=1)

12. 1.312 2.324 ↑ 1.0 13,804 1

Hash (cost=367.34..367.34 rows=13,804 width=4) (actual time=2.324..2.324 rows=13,804 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 614kB
13. 1.012 1.012 ↑ 1.0 13,804 1

Index Only Scan using pk_contract on roadworkcontract rwcont (cost=0.29..367.34 rows=13,804 width=4) (actual time=0.011..1.012 rows=13,804 loops=1)

  • Heap Fetches: 0
14. 0.612 1.112 ↑ 1.0 7,073 1

Hash (cost=194.38..194.38 rows=7,073 width=4) (actual time=1.112..1.112 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
15. 0.500 0.500 ↑ 1.0 7,073 1

Index Only Scan using ix_organisation_id_emailenabled on organisation orgowner (cost=0.28..194.38 rows=7,073 width=4) (actual time=0.014..0.500 rows=7,073 loops=1)

  • Heap Fetches: 0
16. 0.668 12.723 ↑ 1.0 7,073 1

Hash (cost=194.38..194.38 rows=7,073 width=4) (actual time=12.723..12.723 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
17. 12.055 12.055 ↑ 1.0 7,073 1

Index Only Scan using ix_organisation_id_emailenabled on organisation orgpsm (cost=0.28..194.38 rows=7,073 width=4) (actual time=0.011..12.055 rows=7,073 loops=1)

  • Heap Fetches: 0
18. 0.004 0.031 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.031..0.031 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.027 0.027 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rwstatus (cost=0.00..1.23 rows=23 width=4) (actual time=0.025..0.027 rows=23 loops=1)

20. 6.569 11.623 ↑ 1.0 49,354 1

Hash (cost=808.54..808.54 rows=49,354 width=8) (actual time=11.623..11.623 rows=49,354 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,440kB
21. 5.054 5.054 ↑ 1.0 49,354 1

Seq Scan on worktypeminimalhours w (cost=0.00..808.54 rows=49,354 width=8) (actual time=0.025..5.054 rows=49,354 loops=1)

22. 25.218 1,032.826 ↑ 255.1 7,044 1

Hash (cost=475,234.21..475,234.21 rows=1,796,931 width=4) (actual time=1,032.826..1,032.826 rows=7,044 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 1,032kB
23. 1.260 1,007.608 ↑ 255.1 7,044 1

Unique (cost=448,280.24..457,264.90 rows=1,796,931 width=4) (actual time=1,005.566..1,007.608 rows=7,044 loops=1)

24. 5.006 1,006.348 ↑ 85.0 21,132 1

Sort (cost=448,280.24..452,772.57 rows=1,796,931 width=4) (actual time=1,005.564..1,006.348 rows=21,132 loops=1)

  • Sort Key: roadworkrequest.id
  • Sort Method: quicksort Memory: 1,759kB
25. 0.902 1,001.342 ↑ 85.0 21,132 1

Append (cost=32.50..237,035.14 rows=1,796,931 width=4) (actual time=0.439..1,001.342 rows=21,132 loops=1)

26. 15.387 428.290 ↑ 114.1 7,044 1

Hash Join (cost=32.50..31,779.83 rows=803,730 width=4) (actual time=0.438..428.290 rows=7,044 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestownerorganisationid = t.id)
27. 412.611 412.611 ↑ 1.0 156,320 1

CTE Scan on roadworkrequesttemp roadworkrequest (cost=0.00..3,214.92 rows=160,746 width=8) (actual time=0.028..412.611 rows=156,320 loops=1)

28. 0.002 0.292 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.292..0.292 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.290 0.290 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.290..0.290 rows=1 loops=1)

30. 9.553 21.096 ↓ 0.0 0 1

Hash Join (cost=32.50..31,779.83 rows=803,730 width=4) (actual time=21.096..21.096 rows=0 loops=1)

  • Hash Cond: (roadworkrequest_1.roadworkrequestownercontractororganisationid = t_1.id)
31. 11.538 11.538 ↑ 1.0 156,320 1

CTE Scan on roadworkrequesttemp roadworkrequest_1 (cost=0.00..3,214.92 rows=160,746 width=8) (actual time=0.018..11.538 rows=156,320 loops=1)

32. 0.004 0.005 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.001 0.001 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.000..0.001 rows=1 loops=1)

34. 7.570 272.282 ↓ 7.5 7,044 1

Hash Join (cost=19,345.89..66,295.85 rows=943 width=4) (actual time=95.781..272.282 rows=7,044 loops=1)

  • Hash Cond: (roadworkrequest_2.roadworkrequestownerorganisationid = t_2.id)
35. 83.338 264.708 ↓ 720.2 136,119 1

Merge Join (cost=19,313.39..66,229.80 rows=189 width=12) (actual time=67.482..264.708 rows=136,119 loops=1)

  • Merge Cond: ((c.roadworkrequestid = roadworkrequest_2.id) AND (c.organisationid = roadworkrequest_2.roadworkrequestownerorganisationid))
36. 90.422 90.422 ↑ 1.0 1,462,553 1

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c (cost=0.43..37,994.72 rows=1,462,553 width=8) (actual time=0.021..90.422 rows=1,462,553 loops=1)

  • Heap Fetches: 0
37. 11.465 90.948 ↑ 1.0 156,322 1

Materialize (cost=19,312.97..20,116.70 rows=160,746 width=8) (actual time=67.441..90.948 rows=156,322 loops=1)

38. 62.836 79.483 ↑ 1.0 156,320 1

Sort (cost=19,312.97..19,714.83 rows=160,746 width=8) (actual time=67.439..79.483 rows=156,320 loops=1)

  • Sort Key: roadworkrequest_2.id, roadworkrequest_2.roadworkrequestownerorganisationid
  • Sort Method: external merge Disk: 2,768kB
39. 16.647 16.647 ↑ 1.0 156,320 1

CTE Scan on roadworkrequesttemp roadworkrequest_2 (cost=0.00..3,214.92 rows=160,746 width=8) (actual time=0.009..16.647 rows=156,320 loops=1)

40. 0.004 0.004 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.000 0.000 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t_2 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.000..0.000 rows=1 loops=1)

42. 7.559 278.772 ↑ 26.8 7,044 1

Hash Join (cost=67,228.62..80,225.65 rows=188,528 width=4) (actual time=276.607..278.772 rows=7,044 loops=1)

  • Hash Cond: (t_3.id = c_1.organisationid)
43. 0.002 0.002 ↑ 1,000.0 1 1

CTE Scan on ownertreetmp t_3 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.001..0.002 rows=1 loops=1)

44. 24.572 271.211 ↑ 1.1 164,997 1

Hash (cost=64,165.58..64,165.58 rows=186,643 width=8) (actual time=271.211..271.211 rows=164,997 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,436kB
45. 75.767 246.639 ↑ 1.1 164,997 1

Merge Join (cost=19,313.39..64,165.58 rows=186,643 width=8) (actual time=55.154..246.639 rows=164,997 loops=1)

  • Merge Cond: (c_1.roadworkrequestid = roadworkrequest_3.id)
46. 91.088 91.088 ↑ 1.0 1,462,553 1

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c_1 (cost=0.43..37,994.72 rows=1,462,553 width=8) (actual time=0.020..91.088 rows=1,462,553 loops=1)

  • Heap Fetches: 0
47. 12.589 79.784 ↓ 1.0 165,236 1

Materialize (cost=19,312.97..20,116.70 rows=160,746 width=4) (actual time=55.113..79.784 rows=165,236 loops=1)

48. 53.242 67.195 ↑ 1.0 156,320 1

Sort (cost=19,312.97..19,714.83 rows=160,746 width=4) (actual time=55.108..67.195 rows=156,320 loops=1)

  • Sort Key: roadworkrequest_3.id
  • Sort Method: external merge Disk: 2,144kB
49. 13.953 13.953 ↑ 1.0 156,320 1

CTE Scan on roadworkrequesttemp roadworkrequest_3 (cost=0.00..3,214.92 rows=160,746 width=4) (actual time=0.023..13.953 rows=156,320 loops=1)

Planning time : 3.085 ms