explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LVCA

Settings
# exclusive inclusive rows x rows loops node
1. 0.206 7,027.396 ↑ 105.4 1,422 1

Unique (cost=10,804,822.20..10,862,149.64 rows=149,820 width=4) (actual time=6,994.905..7,027.396 rows=1,422 loops=1)

2. 0.109 7,027.190 ↑ 104.4 1,435 1

Result (cost=10,804,822.20..10,861,775.09 rows=149,820 width=4) (actual time=6,994.904..7,027.190 rows=1,435 loops=1)

  • One-Time Filter: ((to_timestamp('20180103 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)
3. 0.961 7,027.081 ↑ 104.4 1,435 1

Nested Loop (cost=10,804,822.20..10,861,775.09 rows=149,820 width=4) (actual time=6,994.880..7,027.081 rows=1,435 loops=1)

4. 38.324 7,018.945 ↑ 104.4 1,435 1

Merge Join (cost=10,804,822.05..10,837,217.51 rows=149,820 width=8) (actual time=6,987.638..7,018.945 rows=1,435 loops=1)

  • Merge Cond: (gis.entityid = rw.id)
5. 68.103 68.103 ↑ 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.038..68.103 rows=1,045,497 loops=1)

  • Heap Fetches: 0
6. 0.113 6,912.518 ↑ 103.9 1,435 1

Materialize (cost=10,804,821.63..10,805,567.28 rows=149,130 width=8) (actual time=6,912.353..6,912.518 rows=1,435 loops=1)

7. 0.818 6,912.405 ↑ 104.9 1,422 1

Sort (cost=10,804,821.63..10,805,194.45 rows=149,130 width=8) (actual time=6,912.342..6,912.405 rows=1,422 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 115kB
8. 0.495 6,911.587 ↑ 104.9 1,422 1

Nested Loop Left Join (cost=1.70..10,789,966.23 rows=149,130 width=8) (actual time=2,572.605..6,911.587 rows=1,422 loops=1)

9. 0.868 6,909.670 ↑ 104.9 1,422 1

Nested Loop (cost=1.42..10,745,213.13 rows=149,130 width=12) (actual time=2,572.598..6,909.670 rows=1,422 loops=1)

10. 0.741 6,907.380 ↑ 104.9 1,422 1

Nested Loop (cost=1.14..10,700,460.03 rows=149,130 width=16) (actual time=2,572.593..6,907.380 rows=1,422 loops=1)

11. 29.006 6,903.795 ↑ 104.9 1,422 1

Nested Loop (cost=0.85..10,655,706.94 rows=149,130 width=20) (actual time=2,572.576..6,903.795 rows=1,422 loops=1)

12. 4,175.527 4,175.527 ↓ 1.0 449,877 1

Index Scan using pk_roadworkrequestdates on roadworkrequestdates ad (cost=0.43..113,069.38 rows=448,292 width=4) (actual time=659.349..4,175.527 rows=449,877 loops=1)

  • Filter: ((completeddate >= (to_timestamp('20180103 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: 1896445
13. 1,859.294 2,699.262 ↓ 0.0 0 449,877

Index Scan using ix_actualdates on roadworkrequest rw (cost=0.42..23.51 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=449,877)

  • Index Cond: (actualroadworkrequestdatesid = ad.id)
  • Filter: ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
  • Rows Removed by Filter: 0
14.          

SubPlan (forIndex Scan)

15. 419.984 839.968 ↓ 0.0 0 209,992

Hash Join (cost=4.46..49.55 rows=3 width=0) (actual time=0.004..0.004 rows=0 loops=209,992)

  • Hash Cond: (uo.id = c_1.organisationid)
16. 0.000 0.000 ↑ 2,550.0 1 209,967

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

17. 209.992 419.984 ↑ 1.0 1 209,992

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 209.992 209.992 ↑ 1.0 1 209,992

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=209,992)

  • Index Cond: (roadworkrequestid = rw.id)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0 208,570

Seq Scan on userorgids uo_1 (cost=0.00..41.88 rows=13 width=0) (actual time=0.000..0.000 rows=0 loops=208,570)

  • Filter: (id = rw.roadworkrequestownerorganisationid)
  • Rows Removed by Filter: 1
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on userorgids uo_2 (cost=0.00..35.50 rows=2,550 width=4) (never executed)

21. 2.844 2.844 ↑ 1.0 1 1,422

Index Only Scan using pk_organisation on organisation psm (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,422)

  • Index Cond: (id = rw.roadworkrequestpsmorganisationid)
  • Heap Fetches: 0
22. 1.422 1.422 ↑ 1.0 1 1,422

Index Only Scan using pk_organisation on organisation ownr (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,422)

  • Index Cond: (id = rw.roadworkrequestownerorganisationid)
  • Heap Fetches: 0
23. 1.422 1.422 ↑ 1.0 1 1,422

Index Only Scan using pk_organisation on organisation c (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,422)

  • Index Cond: (id = rw.roadworkrequestownercontractororganisationid)
  • Heap Fetches: 0
24. 7.175 7.175 ↑ 1.0 1 1,435

Index Only Scan using pk_roadworkstatustype on roadworkstatustype rs (cost=0.14..0.16 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,435)

  • Index Cond: (id = rw.roadworkstatustypeid)
  • Heap Fetches: 0