explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zpn7

Settings
# exclusive inclusive rows x rows loops node
1. 18.579 40.811 ↑ 8,252.8 1,507 1

Gather (cost=3,750.36..14,473,445.94 rows=12,437,000 width=319) (actual time=14.823..40.811 rows=1,507 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 5.804 22.232 ↑ 10,322.9 502 3 / 3

Nested Loop (cost=2,750.36..13,228,745.94 rows=5,182,083 width=319) (actual time=8.614..22.232 rows=502 loops=3)

3. 0.201 15.926 ↑ 10.3 502 3 / 3

Nested Loop (cost=2,750.36..79,211.99 rows=5,182 width=199) (actual time=7.944..15.926 rows=502 loops=3)

4. 0.196 14.568 ↑ 21.2 231 3 / 3

Hash Join (cost=2,749.93..76,532.06 rows=4,892 width=147) (actual time=7.921..14.568 rows=231 loops=3)

  • Hash Cond: (r.roadworkrequestownercontractororganisationid = owncontractor.id)
5. 0.137 11.292 ↑ 21.2 231 3 / 3

Hash Join (cost=2,012.62..75,781.90 rows=4,892 width=122) (actual time=4.747..11.292 rows=231 loops=3)

  • Hash Cond: (r.roadworkrequestpsmorganisationid = psmorg.id)
6. 0.164 8.947 ↑ 21.2 231 3 / 3

Hash Join (cost=1,275.31..75,031.74 rows=4,892 width=97) (actual time=2.500..8.947 rows=231 loops=3)

  • Hash Cond: (r.roadworkrequestownerorganisationid = ownerorg.id)
7. 0.000 6.621 ↑ 21.2 231 3 / 3

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

8. 1.953 2.091 ↑ 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.215..2.091 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=672
9. 0.000 0.138 ↓ 0.0 0 1 / 3

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

10. 0.002 0.002 ↓ 0.0 0 1 / 3

Bitmap Index Scan on ix_roadworkrequestdates_startdate (cost=0.00..6.59 rows=216 width=0) (actual time=0.005..0.005 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))
11. 0.004 0.004 ↓ 0.0 0 1 / 3

Bitmap Index Scan on ix_roadworkrequestdates_completeddate (cost=0.00..6.11 rows=168 width=0) (actual time=0.013..0.013 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))
12. 0.132 0.132 ↑ 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.396..0.396 rows=2,828 loops=1)

  • Index Cond: (completeddate >= '2020-06-12 05:00:00'::timestamp without time zone)
13. 4.590 4.590 ↓ 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.005..0.005 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
14. 1.221 2.162 ↑ 1.0 6,947 3 / 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 521kB
15. 0.941 0.941 ↑ 1.0 6,947 3 / 3

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

16. 1.229 2.208 ↑ 1.0 6,947 3 / 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 521kB
17. 0.979 0.979 ↑ 1.0 6,947 3 / 3

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

18. 1.205 3.080 ↑ 1.0 6,947 3 / 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 521kB
19. 1.875 1.875 ↑ 1.0 6,947 3 / 3

Seq Scan on organisation owncontractor (cost=0.00..650.47 rows=6,947 width=33) (actual time=0.014..1.875 rows=6,947 loops=3)

20. 1.157 1.157 ↑ 1.0 2 694 / 3

Index Scan using ix_geometryroadworkid on geometryroadwork g (cost=0.43..0.53 rows=2 width=56) (actual time=0.005..0.005 rows=2 loops=694)

  • Index Cond: (entityid = r.id)
21. 0.502 0.502 ↑ 1,000.0 1 1,507 / 3

Function Scan on regexp_split_to_table (cost=0.00..10.00 rows=1,000 width=0) (actual time=0.001..0.001 rows=1 loops=1,507)