Quartz UI (currently) needs ~4 days of data x all gsps

[peterdudfield]

Currently using loop over ForecastAtTimestamps it takes about 15 seconds, which is a bit too slow.

Would be good to

1. speed this up
2. change the UI to load the data more effectively

[peterdudfield]

One SQL query, did not speed it up. This took ~30 seconds to get all data

WITH relevant_forecasts AS (
    SELECT 
        f.forecast_uuid,
        f.geometry_uuid,
        f.source_type_id,
        f.init_time_utc, 
		f.target_period
    FROM pred.forecasts AS f
    WHERE
        f.geometry_uuid in (select geometry_uuid from loc.geometries where geometry_type_id = 2 limit 400)
        AND f.source_type_id = 1
        AND f.forecaster_id = 53
        AND f.target_period @> '2026-01-07 00:00'::TIMESTAMP
    ORDER BY f.geometry_uuid, f.init_time_utc desc
),
ranked_predictions AS (
    SELECT
        rf.forecast_uuid,
        rf.geometry_uuid,
        rf.source_type_id,
        pg.horizon_mins,
        pg.p50_sip,
        pg.target_time_utc,
        pg.metadata,
        pg.other_stats_fractions,  rf.target_period,
        ROW_NUMBER() OVER (
            PARTITION BY rf.geometry_uuid
            ORDER BY pg.horizon_mins ASC
        ) AS rn
    FROM relevant_forecasts AS rf
        INNER JOIN pred.predicted_generation_values AS pg USING (forecast_uuid)
    WHERE
        pg.target_time_utc >= '2026-01-06'::TIMESTAMP
		and pg.target_time_utc <= '2026-01-07'::TIMESTAMP
        -- AND pg.horizon_mins >= sqlc.arg(horizon_mins)::INTEGER
)
SELECT distinct on (rp.target_time_utc, rp.geometry_uuid)
    rp.forecast_uuid,
    rp.geometry_uuid,
    rp.source_type_id,
    rp.horizon_mins,
    rp.p50_sip,
    rp.target_time_utc,
    rp.metadata,
    rp.other_stats_fractions,
	rp.target_period,
    sv.capacity_watts,
    sv.latitude,
    sv.longitude,
    sv.geometry_name,
	*
FROM ranked_predictions AS rp
    INNER JOIN loc.sources_mv AS sv USING (geometry_uuid, source_type_id)
WHERE rp.rn > 0
and sv.sys_period @> rp.target_time_utc
order by rp.target_time_utc, rp.geometry_uuid, rp.horizon_mins

[AnujNayak108]

Thanks for sharing this — really interesting problem.

Reading through this, it feels like the bottleneck might be less about a single SQL query and more about how much data the UI is trying to load at once (4 days × all GSPs).

A couple of thoughts / questions that came to mind:

• Does the UI actually need the full 4 days for all GSPs upfront, or could this be loaded incrementally (e.g. per day or per visible time range)?

• Is there scope to reduce the initial payload (fewer columns or a coarser time resolution), and then fetch full-resolution data on demand?

• On the query side, the combination of ROW_NUMBER(), DISTINCT ON, and joins across pred and loc tables looks like it could get expensive at this scale — is the main intent here to select the latest init per geometry per target time?

Happy to dig into either the query plan or the UI data-loading side if that would be helpful.