Lengthy PostGIS processing time of buffered polygon and line string intersection/difference

Question

I have created a ring buffer of lines (streams). I then use ST_Intersect and ST_Difference/ST_Intersects to intersect a linestring (linestring_feature) with the ring buffer (MultiPolygon) and also find the linestring segments that don't intersect with the ring buffers. The size of the analysis is relatively small, with the linestring being just 492 segments and the polygon having 4 multipolygon features. I suspect the lengthy time of 4 min 45 sec is because of the ring buffer being a large multipolygon agglomeration, but not sure how one goes about separating the unconnected ring buffer features.

Buffering SQL Code:

-- << Stream Buffer >>
CREATE TABLE stream_buffer AS
WITH bounding_box AS (              -- create a buffered bounding box (envelope) around linstring of interst
SELECT ST_Buffer(ST_Transform(ST_Union(ST_Envelope(p.geom)), 3005), 250) as geom 
FROM public.linestring_feature p
), 
streams AS (                        -- intersect streams with envelope
SELECT ST_Intersection(b.geom, ST_Transform(s.geom, 3005)) as geom
FROM bounding_box b, public.streams_test s
),                                  -- begin creating buffer rings
buffer50 AS(                        
    SELECT 1.0::float8 vul,        -- vulnerability value of 1.0
    ST_Buffer(ST_Transform(ST_Union(geom),3005),50) geom 
    FROM streams),
buffer100 AS
  (SELECT 0.75::float8 vul,         -- vulnerability value of 0.75
      ST_Difference(ST_Buffer(ST_Transform(ST_Union(geom),3005),100),ST_Buffer(ST_Transform(ST_Union(geom),3005),50)) geom 
  FROM streams),
buffer200 AS 
  (SELECT 0.50::float8 vul,         -- vulnerability value of 0.50
      ST_Difference(ST_Buffer(ST_Transform(ST_Union(geom),3005),200),
      ST_Buffer(ST_Transform(ST_Union(geom),3005),100)) geom 
  FROM streams),
buffer300 AS
  (SELECT 0.25::float8 vul,         -- vulnerability value of 0.25
      ST_Difference(ST_Buffer(ST_Transform(ST_Union(geom),3005),300),
      ST_Buffer(ST_Transform(ST_Union(geom),3005),200)) geom 
  FROM streams)
SELECT * FROM buffer50              -- union all buffers
UNION
SELECT * FROM buffer100
UNION
SELECT * FROM buffer200
UNION
SELECT * FROM buffer300;
ALTER TABLE public.stream_buffer ADD COLUMN id SERIAL PRIMARY KEY;              -- add id column
CREATE INDEX stream_buffer_geom_idx ON public.stream_buffer USING GIST (geom);  -- create spatial index

Line Intersection code:

CREATE TEMP TABLE intersected AS
     SELECT s.vul, ST_Intersection(p.geom, s.geom) AS geom      
      FROM
        public.linestring_feature p,
        public.stream_buffer s;

-- << Intersected linestring_feature segments outside of buffer >>
CREATE TEMP TABLE inter_nonbuffer AS 
      SELECT ST_Difference(l.geom, ST_Union(p.geom)) AS geom 
      FROM 
      public.linestring_feature AS l
      JOIN public.stream_buffer AS p ON
      ST_Intersects (l.geom, p.geom)
      GROUP BY l.geom;

ALTER TABLE inter_nonbuffer             -- add values of linestring_feature
ADD COLUMN vul float8;                      
UPDATE inter_nonbuffer
SET vul = 0.0; 

SELECT * FROM inter_nonbuffer;

-- << Non intersected linestring_feature sections >>
CREATE TEMP TABLE non_intersect AS
SELECT 
  p.id, p.geom, s.vul  
FROM 
  public.linestring_feature AS p 
LEFT JOIN
  public.stream_buffer AS s ON
  ST_Intersects(p.geom,s.geom)
WHERE s.id IS NULL;

UPDATE non_intersect                            -- insert values of linestring_feature
SET vul=0.0
WHERE vul IS NULL;

CREATE TABLE public.strm_vul AS                     -- create final linestring_feature table
SELECT i.geom, i.vul FROM intersected i
UNION
SELECT ni.geom, ni.vul FROM inter_nonbuffer ni  
UNION
SELECT nb.geom, nb.vul FROM non_intersect nb;

ALTER TABLE public.strm_vul ADD COLUMN id SERIAL PRIMARY KEY;               -- add id column

Final intersected and nonintersected linestring features:

Answer 1

Improved my spatial analysis from 4 min 45 sec to 29 secs 306 msec by using ST_Dump.

SELECT (ST_Dump(sb.geom)).geom AS geom
FROM stream_buffer sb;