explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mK7t

Settings
# exclusive inclusive rows x rows loops node
1. 0.305 13,163.808 ↑ 273.0 2,760 1

Unique (cost=24,133,836.99..24,137,604.00 rows=753,402 width=4) (actual time=13,163.399..13,163.808 rows=2,760 loops=1)

2. 0.827 13,163.503 ↑ 271.7 2,773 1

Sort (cost=24,133,836.99..24,135,720.49 rows=753,402 width=4) (actual time=13,163.398..13,163.503 rows=2,773 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 226kB
3. 0.191 13,162.676 ↑ 271.7 2,773 1

Result (cost=117,624.33..24,049,992.92 rows=753,402 width=4) (actual time=6,150.171..13,162.676 rows=2,773 loops=1)

  • One-Time Filter: ((to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone)
4. 0.542 13,162.485 ↑ 271.7 2,773 1

Hash Left Join (cost=117,624.33..24,049,992.92 rows=753,402 width=4) (actual time=6,150.159..13,162.485 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestownercontractororganisationid = c.id)
5. 0.994 13,160.937 ↑ 271.7 2,773 1

Hash Join (cost=117,379.63..24,040,237.61 rows=753,402 width=8) (actual time=6,149.123..13,160.937 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestpsmorganisationid = psm.id)
6. 242.758 13,158.893 ↑ 271.7 2,773 1

Hash Join (cost=117,134.94..24,030,482.31 rows=753,402 width=12) (actual time=6,148.045..13,158.893 rows=2,773 loops=1)

  • Hash Cond: (rw.actualroadworkrequestdatesid = ad.id)
7. 13.883 7,151.821 ↑ 279.9 2,802 1

Hash Join (cost=4,655.65..23,891,638.21 rows=784,143 width=16) (actual time=75.603..7,151.821 rows=2,802 loops=1)

  • Hash Cond: ((rw.roadworkstatustypeid = rs.id) AND (rw.roadworkrequestownerorganisationid = ownr.id))
8. 39.837 7,098.198 ↑ 279.9 2,802 1

Merge Join (cost=61.79..23,820,452.99 rows=784,143 width=24) (actual time=35.508..7,098.198 rows=2,802 loops=1)

  • Merge Cond: (rw.id = gis.entityid)
9. 3,867.100 6,989.854 ↑ 279.9 2,789 1

Index Scan using pk_request on roadworkrequest rw (cost=0.42..23,780,884.45 rows=780,535 width=24) (actual time=34.994..6,989.854 rows=2,789 loops=1)

  • Filter: ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
  • Rows Removed by Filter: 1038128
10.          

SubPlan (forIndex Scan)

11. 1,040.917 3,122.751 ↓ 0.0 0 1,040,917

Hash Join (cost=4.46..49.55 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1,040,917)

  • Hash Cond: (uo.id = c_1.organisationid)
12. 0.000 0.000 ↑ 2,550.0 1 1,039,350

Seq Scan on userorgids uo (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.000..0.000 rows=1 loops=1,039,350)

13. 1,040.917 2,081.834 ↑ 1.0 1 1,040,917

Hash (cost=4.45..4.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,040,917)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 1,040.917 1,040.917 ↑ 1.0 1 1,040,917

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,040,917)

  • Index Cond: (roadworkrequestid = rw.id)
  • Heap Fetches: 0
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on userorgids uo_1 (cost=0.00..41.88 rows=13 width=0) (never executed)

  • Filter: (id = rw.roadworkrequestownerorganisationid)
16. 0.003 0.003 ↑ 2,550.0 1 1

Seq Scan on userorgids uo_2 (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.003..0.003 rows=1 loops=1)

17. 68.507 68.507 ↑ 1.0 1,045,497 1

Index Only Scan using ix_geometryroadworkid on geometryroadwork gis (cost=0.42..27,163.27 rows=1,045,523 width=4) (actual time=0.012..68.507 rows=1,045,497 loops=1)

  • Heap Fetches: 0
18. 24.285 39.740 ↑ 1.0 140,852 1

Hash (cost=1,930.08..1,930.08 rows=140,852 width=8) (actual time=39.740..39.740 rows=140,852 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3773kB
19. 8.782 15.455 ↑ 1.0 140,852 1

Nested Loop (cost=0.28..1,930.08 rows=140,852 width=8) (actual time=0.025..15.455 rows=140,852 loops=1)

20. 0.549 0.549 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation ownr (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.013..0.549 rows=6,124 loops=1)

  • Heap Fetches: 0
21. 6.112 6.124 ↑ 1.0 23 6,124

Materialize (cost=0.00..1.35 rows=23 width=4) (actual time=0.000..0.001 rows=23 loops=6,124)

22. 0.012 0.012 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rs (cost=0.00..1.23 rows=23 width=4) (actual time=0.008..0.012 rows=23 loops=1)

23. 337.220 5,764.314 ↓ 1.0 2,259,470 1

Hash (cost=75,493.05..75,493.05 rows=2,254,339 width=4) (actual time=5,764.314..5,764.314 rows=2,259,470 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3506kB
24. 5,427.094 5,427.094 ↓ 1.0 2,259,470 1

Seq Scan on roadworkrequestdates ad (cost=0.00..75,493.05 rows=2,254,339 width=4) (actual time=6.813..5,427.094 rows=2,259,470 loops=1)

  • Filter: ((completeddate >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (startdate <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
  • Rows Removed by Filter: 86852
25. 0.593 1.050 ↑ 1.0 6,124 1

Hash (cost=168.14..168.14 rows=6,124 width=4) (actual time=1.050..1.050 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
26. 0.457 0.457 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation psm (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.020..0.457 rows=6,124 loops=1)

  • Heap Fetches: 0
27. 0.583 1.006 ↑ 1.0 6,124 1

Hash (cost=168.14..168.14 rows=6,124 width=4) (actual time=1.006..1.006 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
28. 0.423 0.423 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation c (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.014..0.423 rows=6,124 loops=1)

  • Heap Fetches: 0