I have a Postgres table named shipData(total rows 700 millions). Which has ship location with reporting time and location in geometry format(combination of lat & lon). I have a shipID, I want to retrieve the nearby ship’s location with in 1 nautical mile. I have 3 index on shipID, shipLocation, reportingTime column. I think the main time time is required for PostGIS ST_DWithin calculation.

I tried with the following query. At first I retrieve the selected ship position with my shipID and time range. Then I tried to get the nearby ship’s location withe the help of PostGIS ST_DWithin method.

WITH selected_ship_positions AS (
    SELECT shipID, shipLocation, reportingTime
    FROM shipData
    WHERE shipID = 123456789 AND reportingTime BETWEEN '07/12/2024 02:00 UTC' AND '07/12/2024 02:10 UTC'
)
SELECT sd.shipID, sd.shipname, sd.shipLocation, sd.reportingTime
FROM shipData sd
JOIN selected_ship_positions ssp
    ON sd.reportingTime BETWEEN ssp.reportingTime - INTERVAL '1 minutes' AND ssp.reportingTime + INTERVAL '1 minutes'
    AND ST_DWithin(sd.shipLocation::geography, ssp.shipLocation::geography, 1852) -- 1 nautical miles
WHERE sd.shipID != ssp.shipID 

The query gives result in 2 min. I want to get the response with in 15-20sec. How to improve the performance?