explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PlN3

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=203,469.31..203,562.59 rows=4,146 width=319) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=203,469.31..203,479.67 rows=4,146 width=319) (actual rows= loops=)

  • Sort Key: (concat('M', (r.id)::text)), r.address, psmorg.name, ownerorg.name, owncontractor.name, ((rd.startdate)::text), ((rd.completeddate)::text), (st_asgeojson(g.geom, 15, 0))
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=78,814.90..203,220.18 rows=4,146 width=319) (actual rows= loops=)

  • Hash Cond: (r.roadworkrequestownercontractororganisationid = owncontractor.id)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=78,077.59..192,034.43 rows=4,146 width=174) (actual rows= loops=)

  • Hash Cond: (r.roadworkrequestpsmorganisationid = psmorg.id)
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=77,340.28..191,286.22 rows=4,146 width=149) (actual rows= loops=)

  • Hash Cond: (r.roadworkrequestownerorganisationid = ownerorg.id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=76,602.98..190,538.02 rows=4,146 width=124) (actual rows= loops=)

  • Hash Cond: (g.entityid = r.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..112,143.00 rows=466,934 width=56) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Function Scan on regexp_split_to_table (cost=0.00..10.00 rows=1,000 width=32) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using idx_roadwork_geom on geometryroadwork g (cost=0.41..111.66 rows=47 width=56) (actual rows= loops=)

  • Index Cond: (st_geomfromtext(regexp_split_to_table.regexp_split_to_table, 4326) && geom)
  • Filter: _st_intersects(st_geomfromtext(regexp_split_to_table.regexp_split_to_table, 4326), geom)
10. 0.000 0.000 ↓ 0.0

Hash (cost=76,455.78..76,455.78 rows=11,742 width=72) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Gather (cost=1,538.01..76,455.78 rows=11,742 width=72) (actual rows= loops=)

  • Workers Planned: 2
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=538.01..74,281.58 rows=4,892 width=72) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on roadworkrequestdates rd (cost=537.58..22,834.87 rows=11,415 width=20) (actual rows= loops=)

  • Recheck Cond: (((startdate >= '2020-06-11 22:00:00'::timestamp without time zone) AND (startdate <= '2020-06-12 05:00:00'::timestamp without time zone)) OR ((completeddate >= '2020-06-11 22:00:00'::timestamp without time zone) AND (completeddate <= '2020-06-12 05:00:00'::timestamp without time zone)) OR (completeddate >= '2020-06-12 05:00:00'::timestamp without time zone))
  • Filter: (((startdate >= '2020-06-11 22:00:00'::timestamp without time zone) AND (startdate <= '2020-06-12 05:00:00'::timestamp without time zone)) OR ((completeddate >= '2020-06-11 22:00:00'::timestamp without time zone) AND (completeddate <= '2020-06-12 05:00:00'::timestamp without time zone)) OR ((startdate <= '2020-06-11 22:00:00'::timestamp without time zone) AND (completeddate >= '2020-06-12 05:00:00'::timestamp without time zone)))
14. 0.000 0.000 ↓ 0.0

BitmapOr (cost=537.58..537.58 rows=27,572 width=0) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_roadworkrequestdates_startdate (cost=0.00..6.59 rows=216 width=0) (actual rows= loops=)

  • Index Cond: ((startdate >= '2020-06-11 22:00:00'::timestamp without time zone) AND (startdate <= '2020-06-12 05:00:00'::timestamp without time zone))
16. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_roadworkrequestdates_completeddate (cost=0.00..6.11 rows=168 width=0) (actual rows= loops=)

  • Index Cond: ((completeddate >= '2020-06-11 22:00:00'::timestamp without time zone) AND (completeddate <= '2020-06-12 05:00:00'::timestamp without time zone))
17. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_roadworkrequestdates_completeddate (cost=0.00..504.33 rows=27,187 width=0) (actual rows= loops=)

  • Index Cond: (completeddate >= '2020-06-12 05:00:00'::timestamp without time zone)
18. 0.000 0.000 ↓ 0.0

Index Scan using ix_actualdates on roadworkrequest r (cost=0.43..4.50 rows=1 width=60) (actual rows= loops=)

  • Index Cond: (actualroadworkrequestdatesid = rd.id)
  • Filter: (roadworkstatustypeid = ANY ('{10,12,13,15,16,17,18,19,20,21,22,24}'::integer[]))
19. 0.000 0.000 ↓ 0.0

Hash (cost=650.47..650.47 rows=6,947 width=33) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on organisation ownerorg (cost=0.00..650.47 rows=6,947 width=33) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=650.47..650.47 rows=6,947 width=33) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on organisation psmorg (cost=0.00..650.47 rows=6,947 width=33) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=650.47..650.47 rows=6947 width=33)" -> Seq Scan on organisation owncontractor (cost=0.00..650.47 rows=6,947 width=33) (actual rows= loops=)