Here is my code:
with
pairs as
(
select distinct team_info."number" as "team_number", aic_info."name" as aic_name,
aic_assets."IMEI" as parent_imei, team_assets ."IMEI" as child_imei, 0 as count, ucs.name as "uc_name", tehsils.name as "tehsil_name", districts.name as "district_name", provinces.name as "province_name"
from campaign_staff team
left join campaign_staff_info team_info on team."ID" = team_info."campaignStaffID" and team_info.date = (select max(date) from campaign_staff_info team_info2 where team_info2."campaignStaffID" = team_info."campaignStaffID" and team_info2.date<='2024-05-04')
left join campaign_staff aic on team."parentID" = aic."ID"
left join campaign_staff_info aic_info on aic."ID" = aic_info."campaignStaffID" and aic_info.date = (select max(date) from campaign_staff_info aic_info2 where aic_info2."campaignStaffID" = aic_info."campaignStaffID" and aic_info2.date<='2024-05-04')
left join campaign_staff_assets team_staff_assets on team_staff_assets."campaignStaffID" = team."ID" and team_staff_assets."date" = (select max(date) from campaign_staff_assets cs2 where cs2."campaignStaffID"= team_staff_assets."campaignStaffID" and cs2.date<='2024-05-04')
left join campaign_staff_assets aic_staff_assets on aic_staff_assets."campaignStaffID" = aic."ID" and aic_staff_assets."date" = (select max(date) from campaign_staff_assets cs2 where cs2."campaignStaffID"= aic_staff_assets."campaignStaffID" and cs2.date<='2024-05-04')
left join assets team_assets on team_assets."ID" = team_staff_assets."assetID"
left join assets aic_assets on aic_assets."ID" = aic_staff_assets."assetID"
left join campaign_asset_status aic_cas on aic_cas."campaignStaffID" = aic."ID" and aic_cas.date = (select max(date) from campaign_asset_status aic_cas_tmp where aic_cas_tmp.date <= '2024-05-04' and aic_cas_tmp."campaignStaffID" = aic_cas."campaignStaffID")
left join campaign_asset_status team_cas on team_cas."campaignStaffID" = team."ID" and team_cas.date = (select max(date) from campaign_asset_status team_cas_tmp where team_cas_tmp.date <= '2024-05-04' and team_cas_tmp."campaignStaffID" = team_cas."campaignStaffID")
left join area ucs on team."campaignScopeID" = ucs."ID"
left join area tehsils on ucs."parentID" = tehsils."ID"
left join area districts on tehsils."parentID" = districts."ID"
left join area provinces on districts."parentID" = provinces."ID"
where
team."staffTypeID" = 'acf0cc70-7c92-474d-82af-f37494f94c09' -- Team ID
and aic."staffTypeID" = '93313048-4e75-4e91-9bcd-44897a6df7c7' -- AIC ID
and team."parentID" = aic."ID"
and (aic_cas.date is null or (aic_cas.status not in ('Inactive', 'DeployedWoPhone') and aic_cas.date = (select max(date) from campaign_asset_status aic_cas_tmp where aic_cas_tmp.date <= '2024-05-04' and aic_cas_tmp."campaignStaffID" = aic_cas."campaignStaffID")))
and (team_cas.date is null or (team_cas.status not in ('Inactive', 'DeployedWoPhone') and team_cas.date = (select max(date) from campaign_asset_status team_cas_tmp where team_cas_tmp.date <= '2024-05-04' and team_cas_tmp."campaignStaffID" = team_cas."campaignStaffID")))
and ((ucs."ID" = '00b78578-1e82-492a-b5de-1d50071fe4cb') or (tehsils."ID" = '00b78578-1e82-492a-b5de-1d50071fe4cb') or (districts."ID" = '00b78578-1e82-492a-b5de-1d50071fe4cb') or (provinces."ID" = '00b78578-1e82-492a-b5de-1d50071fe4cb'))
and (aic."removedDate" is null or aic."removedDate"> '2024-05-04'::date)
and (team."removedDate" is null or team."removedDate"> '2024-05-04'::date)
order by aic_assets."IMEI"
)
--
,
ping as ( -- ================= SELECTING JOINED data with join conditions -- ================= -- ================= -- ================= -- ================= -- =================
select
u."imei" as u_imei,
a."imei" as a_imei,
u.t as u_ping_time,
a.t as a_ping_time,
u_ping_id,
a_ping_id
from
(-- ================= SELECTING Parent INFO [imei, geom, generatedAt, pingID] -- ================= -- ================= -- ================= -- ================= -- =================
select
p.imei,
p."geoJson" as geom,
--st_transform(p."geoJson", 32643) as geom,
"generatedAt" as t,
p."ID" as u_ping_id
from
campaign_pings p
where --"createdAt"::date = '2024-05-04'--'2024-05-04'
"generatedAt"+ interval '5 hours' between '2024-05-04 06:00:00 ' and '2024-05-04 06:30:00'
and
p.imei in (
select
assets."IMEI"
from
campaign_staff staff
left join campaign_staff_info csi on csi."campaignStaffID"=staff."ID" and csi."date" = (select max(date) from campaign_staff_info csi2 where csi2."campaignStaffID" = csi."campaignStaffID" and csi2.date <= '2024-05-04')
left join campaign_staff_assets csa on csa."campaignStaffID" = staff."ID" and csa."date" = (select max(date) from campaign_staff_assets cs2 where cs2."campaignStaffID"= csa."campaignStaffID" and cs2.date<='2024-05-04')
left join campaign_area_assets caa on caa."assetID" = csa."assetID"
and caa."campaignScopeAreaID" = csa."campaignScopeID"
left join assets assets on assets."ID" = caa."assetID"
left join staff_type staff_type ON staff_type."ID" = staff."staffTypeID"
left join campaign_asset_status cas on cas."campaignStaffID" = staff."ID" and cas."date" = (select max(date) from campaign_asset_status cas2 where cas2."campaignStaffID"= cas."campaignStaffID" and cas2.date<='2024-05-04')
left join campaign_scope cs on cs."areaID" = caa."campaignScopeAreaID"
where
caa."campaignScopeAreaID" = '00b78578-1e82-492a-b5de-1d50071fe4cb' --'00b78578-1e82-492a-b5de-1d50071fe4cb'
and assets."IMEI" is not null
and staff_type."ID" = '93313048-4e75-4e91-9bcd-44897a6df7c7' -- AIC ID
and (cas.status is null or cas.status <> 'DeployedWoPhone')
and ((csi.number is null) or (csi.number <> '0'))
and '2024-05-04' between cs."campaignStartDate" and cs."catchupEndDate"
)
) u
inner join ( -- ================= SELECTING child INFO [imei, geom, generatedAt, pingID] -- ================= -- ================= -- ================= -- ================= -- =================
SELECT
p.imei,
p."ID" as a_ping_id,
p."geoJson" as geom,
--st_transform(p."geoJson", 32643) as geom,
"generatedAt" as t
FROM
campaign_pings p
where --"createdAt"::date = '2024-05-04' --'2024-05-04'
"generatedAt"+ interval '5 hours' between '2024-05-04 06:00:00 ' and '2024-05-04 06:30:00'
and
p.imei in (
select
assets."IMEI"
from
campaign_staff staff
left join campaign_staff_assets csa on csa."campaignStaffID" = staff."ID" and csa."date" = (select max(date) from campaign_staff_assets cs2 where cs2."campaignStaffID"= csa."campaignStaffID" and cs2.date<='2024-05-04')
left join campaign_area_assets caa on caa."assetID" = csa."assetID"
and caa."campaignScopeAreaID" = csa."campaignScopeID"
left join assets assets on assets."ID" = caa."assetID"
left join staff_type staff_type ON staff_type."ID" = staff."staffTypeID"
left join campaign_asset_status cas on cas."campaignStaffID" = staff."ID" and cas."date" = (select max(date) from campaign_asset_status cas2 where cas2."campaignStaffID"= cas."campaignStaffID" and cas2.date<='2024-05-04')
left join campaign_scope cs on cs."areaID" = caa."campaignScopeAreaID"
where
caa."campaignScopeAreaID" = '00b78578-1e82-492a-b5de-1d50071fe4cb'
and assets."IMEI" is not null
and staff_type."ID" = 'acf0cc70-7c92-474d-82af-f37494f94c09' -- Team ID
and (cas.status is null or cas.status <> 'DeployedWoPhone')
and '2024-05-04' between cs."campaignStartDate" and cs."catchupEndDate"
)
) a on ST_DWithin(u."geom"::geography, a."geom"::geography, 30)
and a.t BETWEEN u.t - INTERVAL '30 seconds'
and u.t + INTERVAL '30 seconds'
where
a.imei is not null
)
,
results AS (
SELECT *
FROM ping
JOIN pairs ON ping.u_imei = pairs.parent_imei AND ping.a_imei = pairs.child_imei
)
SELECT COUNT(*)
FROM results;
My ping CTE only has 187 rows. My pairs CTE has only 72 rows. If I remove the results CTE, the query executes in a couple of seconds.
When I re-insert the results CTE, it keeps on going on for minutes without stopping, then I have to forcefully stop the query. I have tried various different strategies such as replacing the join with subquery IN etc. but it always gets stuck. I can not include the query plan here because of this website’s posting limitation when it comes to total characters in a post.