Optimize SQL query with 3 FOR loops
<p>I have a fully working SQL query. However, it is very very slow. I am looking for a way to optimize it.</p>

<pre><code>CREATE TABLE trajectory_geom (
  id                        SERIAL PRIMARY KEY,
  trajectory_id             BIGINT,
  user_id               BIGINT,
  geom                  GEOMETRY(Linestring, 4326)
);

INSERT INTO trajectory_geom (trajectory_id, user_id, geom)
    SELECT
        p.trajectory_id,
        p.user_id,
        ST_Transform(ST_MakeLine(p.geom), 4326)
    FROM point p
    GROUP BY p.trajectory_id
;

DO $$
DECLARE
  urow record;
  vrow record;
  wrow record;
BEGIN
  FOR wrow IN
  SELECT DISTINCT(p.user_id) FROM point p
  LOOP
    raise notice 'User id: %', wrow.user_id;
    FOR vrow IN
    SELECT DISTINCT(p.trajectory_id) FROM point p WHERE p.user_id = wrow.user_id
    LOOP
      FOR urow IN
      SELECT
        analyzed_tr.*
      FROM trajectory_start_end_geom analyzed_tr
      WHERE
      analyzed_tr.user_id = wrow.user_id
      AND
      ST_Intersects (
        (
         analyzed_tr.start_geom
        )
        ,
        (
          SELECT g.geom
          FROM trajectory_geom g
          WHERE g.trajectory_id = vrow.trajectory_id
        )
      ) = TRUE
      LOOP
        INSERT INTO trajectories_intercepting_with_starting_point (initial_trajectory_id, mathced_trajectory_id, user_id)
          SELECT
            vrow.trajectory_id,
            urow.trajectory_id,
            wrow.user_id
          WHERE urow.trajectory_id &lt;&gt; vrow.trajectory_id
        ;
      END LOOP;
    END LOOP;
  END LOOP;
END;
$$;
</code></pre>

<p>It has 3 loops...how can I avoid them? </p>

<p>Basically, I am looping all user IDs, for each user looping all trajectories and checking is trajectory interact with any other trajectory of this user.</p>

<p>Schema:</p>

<pre><code>CREATE TABLE public.trajectory_start_end_geom
(
  id integer NOT NULL DEFAULT nextval('trajectory_start_end_geom_id_seq'::regclass),
  trajectory_id bigint,
  user_id bigint,
  start_geom geometry(Polygon,4326),
  end_geom geometry(Polygon,4326),
  CONSTRAINT trajectory_start_end_geom_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE public.trajectory_geom
(
  id integer NOT NULL DEFAULT nextval('trajectory_geom_id_seq'::regclass),
  trajectory_id bigint,
  user_id bigint,
  geom geometry(LineString,4326),
  CONSTRAINT trajectory_geom_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE public.point
(
  id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass),
  user_id bigint,
  date date,
  "time" time without time zone,
  lat double precision,
  lon double precision,
  trajectory_id integer,
  geom geometry(Geometry,4326),
  CONSTRAINT point_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
</code></pre>