Speed up ListPredictionsAtTimeForLocations

[peterdudfield]

Current this SQL functions take a long time
Around 7 seconds for each go

It looks like this is the slow bit

SELECT DISTINCT ON (f.geometry_uuid)
        f.forecast_uuid,
        f.geometry_uuid,
        f.source_type_id,
        f.init_time_utc, *
    FROM pred.forecasts AS f
    WHERE
        f.geometry_uuid in (select geometry_uuid from loc.geometries where geometry_type_id = 2)
        AND f.source_type_id = 1
        AND f.forecaster_id = 53
        AND f.target_period @> '2026-01-21 18:00:00'::TIMESTAMP
    ORDER BY f.geometry_uuid ASC, f.init_time_utc DESC

[peterdudfield]

This can be speed up big time with

AND f.init_time_utc > '2026-01-19 18:00:00'::TIMESTAMP - interval '2 days'

I guess this is because init_time_utc is on the index and target_period is not

[peterdudfield]

This moves it down from about 7 days to 0.2 seconds

[peterdudfield]

Of course this has the negative of hardcoding this to 2 days

[peterdudfield]

There might be a way to do this with indexes on target_period to speed things up

[peterdudfield]

Idea 1

1. Add an extra column for when the forecast finished
2. add to index
3. filter

[devsjc]

Provided we aren't expecting to make forecasts with a >2 day lead time, then I think this is a good idea! But we should maybe make a note of that.