postgresql - Optimize SQL query with 3 FOR loops -

i have working sql query. however, very slow. looking way optimize it.

create table trajectory_geom (   id                        serial primary key,   trajectory_id             bigint,   user_id               bigint,   geom                  geometry(linestring, 4326) );  insert trajectory_geom (trajectory_id, user_id, geom)     select         p.trajectory_id,         p.user_id,         st_transform(st_makeline(p.geom), 4326)     point p     group p.trajectory_id ;  $$ declare   urow record;   vrow record;   wrow record; begin   wrow in   select distinct(p.user_id) point p   loop     raise notice 'user id: %', wrow.user_id;     vrow in     select distinct(p.trajectory_id) point p p.user_id = wrow.user_id     loop       urow in       select         analyzed_tr.*       trajectory_start_end_geom analyzed_tr             analyzed_tr.user_id = wrow.user_id       ,       st_intersects (         (          analyzed_tr.start_geom         )         ,         (           select g.geom           trajectory_geom g           g.trajectory_id = vrow.trajectory_id         )       ) = true       loop         insert trajectories_intercepting_with_starting_point (initial_trajectory_id, mathced_trajectory_id, user_id)           select             vrow.trajectory_id,             urow.trajectory_id,             wrow.user_id           urow.trajectory_id <> vrow.trajectory_id         ;       end loop;     end loop;   end loop; end; $$; 

it has 3 loops...how can avoid them?

basically, looping user ids, each user looping trajectories , checking trajectory interact other trajectory of user.

schema:

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) ) (   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) ) (   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) ) (   oids=false ); 

try sql query. hope helps.

insert trajectories_intercepting_with_starting_point  (initial_trajectory_id, mathced_trajectory_id, user_id) select          tg.trajectory_id first_trajectory_id,         tg2.trajectory_id last_trajectory_id,         tg.user_id trajectory_geom tg     join trajectory_geom tg2 on tg.user_id = tg2.user_id                                        , tg.trajectory_id < tg2.trajectory_id     join trajectory_start_end_geom tse on tse.trajectory_id = tg.trajectory_id st_intersects(tse.start_geom, tg2.geom) = true