explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ynf7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 46,266.128 ↓ 0.0 0 1

Unique (cost=203,469.31..203,562.59 rows=4,146 width=319) (actual time=46,266.128..46,266.128 rows=0 loops=1)

2. 0.008 46,266.128 ↓ 0.0 0 1

Sort (cost=203,469.31..203,479.67 rows=4,146 width=319) (actual time=46,266.128..46,266.128 rows=0 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 25kB
3. 0.009 46,266.120 ↓ 0.0 0 1

Hash Join (cost=78,814.90..203,220.18 rows=4,146 width=319) (actual time=46,266.120..46,266.120 rows=0 loops=1)

  • Hash Cond: (r.roadworkrequestownercontractororganisationid = owncontractor.id)
4. 0.010 46,263.503 ↓ 0.0 0 1

Hash Join (cost=78,077.59..192,034.43 rows=4,146 width=174) (actual time=46,263.503..46,263.503 rows=0 loops=1)

  • Hash Cond: (r.roadworkrequestpsmorganisationid = psmorg.id)
5. 0.011 46,261.513 ↓ 0.0 0 1

Hash Join (cost=77,340.28..191,286.22 rows=4,146 width=149) (actual time=46,261.513..46,261.513 rows=0 loops=1)

  • Hash Cond: (r.roadworkrequestownerorganisationid = ownerorg.id)
6. 0.106 46,259.539 ↓ 0.0 0 1

Hash Join (cost=76,602.98..190,538.02 rows=4,146 width=124) (actual time=46,259.539..46,259.539 rows=0 loops=1)

  • Hash Cond: (g.entityid = r.id)
7. 0.960 46,241.357 ↑ 8,810.1 53 1

Nested Loop (cost=0.42..112,143.00 rows=466,934 width=56) (actual time=753.920..46,241.357 rows=53 loops=1)

8. 0.509 0.509 ↑ 1,000.0 1 1

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

9. 46,239.888 46,239.888 ↓ 1.1 53 1

Index Scan using idx_roadwork_geom on geometryroadwork g (cost=0.41..111.66 rows=47 width=56) (actual time=752.513..46,239.888 rows=53 loops=1)

  • 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)
  • Rows Removed by Filter: 45216
10. 0.222 18.076 ↑ 16.9 694 1

Hash (cost=76,455.78..76,455.78 rows=11,742 width=72) (actual time=18.075..18.076 rows=694 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 202kB
11. 13.117 17.854 ↑ 16.9 694 1

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

  • Workers Planned: 2
  • Workers Launched: 2
12. 0.654 4.737 ↑ 21.2 231 3 / 3

Nested Loop (cost=538.01..74,281.58 rows=4,892 width=72) (actual time=0.348..4.737 rows=231 loops=3)

13. 1.137 1.329 ↑ 12.4 918 3 / 3

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

  • 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)))
  • Rows Removed by Filter: 25
  • Heap Blocks: exact=1153
14. 0.000 0.192 ↓ 0.0 0 1 / 3

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

15. 0.003 0.003 ↓ 0.0 0 1 / 3

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

  • 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.002 0.002 ↓ 0.0 0 1 / 3

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

  • 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.187 0.187 ↑ 9.6 2,828 1 / 3

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

  • Index Cond: (completeddate >= '2020-06-12 05:00:00'::timestamp without time zone)
18. 2.754 2.754 ↓ 0.0 0 2,754 / 3

Index Scan using ix_actualdates on roadworkrequest r (cost=0.43..4.50 rows=1 width=60) (actual time=0.003..0.003 rows=0 loops=2,754)

  • Index Cond: (actualroadworkrequestdatesid = rd.id)
  • Filter: (roadworkstatustypeid = ANY ('{10,12,13,15,16,17,18,19,20,21,22,24}'::integer[]))
  • Rows Removed by Filter: 0
19. 0.963 1.963 ↑ 1.0 6,947 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 521kB
20. 1.000 1.000 ↑ 1.0 6,947 1

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

21. 0.980 1.980 ↑ 1.0 6,947 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 521kB
22. 1.000 1.000 ↑ 1.0 6,947 1

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

23. 0.954 2.608 ↑ 1.0 6,947 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 521kB
24. 1.654 1.654 ↑ 1.0 6,947 1

Seq Scan on organisation owncontractor (cost=0.00..650.47 rows=6,947 width=33) (actual time=0.006..1.654 rows=6,947 loops=1)