delete forecasts doesnt work (takes too long)

[peterdudfield]

make doctor output

I tried to delete a forecast but i couldnt

Verification

• I have searched the existing issues to ensure this bug has not already been reported.
• I have run make init locally.
• I have been able to reproduce this bug locally.

What were you trying to do (and why)?

Tidy up the list forecast route, but first need to delete the forecasts

What happened (including relevant commands, logs etc)?

postman doesnt give any logs

What did you expect to happen?

when i re run getLatestForeast, it not to be there

Step-by-step reproduction instructions (optional).

No response

[peterdudfield]

I think the sql code here should filter on init_time_utc, here
And do we not need to delete from predicted_generation_values too

[peterdudfield]

But even so, once deleted the values from

WITH forecasts_to_delete AS (
    SELECT forecast_uuid FROM pred.forecasts AS f
    WHERE f.init_time_utc >= '2022-12-31T18:30:00'
        AND f.init_time_utc < '2022-12-31T18:30:01'
        AND f.geometry_uuid = '019a7231-3561-76bc-9da6-dbabaa2e3734'
        AND f.source_type_id = 1
        AND f.forecaster_id = 176
)
DELETE FROM pred.forecasts
WHERE forecast_uuid IN (SELECT forecast_uuid FROM forecasts_to_delete);

takes a long time to run (did not wait for it to complete) - note the select is very quick

[peterdudfield]

WIP

WITH forecasts_to_delete AS (
    SELECT forecast_uuid FROM pred.forecasts AS f
    WHERE f.init_time_utc >= sqlc.arg(init_timestamp)::TIMESTAMP
        AND f.init_time_utc < sqlc.arg(init_timestamp)::TIMESTAMP + INTERVAL '1 millisecond'
        AND f.geometry_uuid = $1
        AND f.source_type_id = $2
        AND f.forecaster_id = $3
)
DELETE FROM pred.predicted_generation_values
WHERE forecast_uuid IN (SELECT forecast_uuid FROM forecasts_to_delete);
DELETE FROM pred.forecasts
WHERE forecast_uuid IN (SELECT forecast_uuid FROM forecasts_to_delete);

[peterdudfield]

Even

delete FROM pred.forecasts
WHERE forecast_uuid ='019c6663-6386-7c2f-a7db-3337b5947866'

takes a very long time to run

[danishdynamic]

Hi @peterdudfield,

I have implemented a fix for this performance bottleneck in my fork. I was able to reproduce the slow DELETE latency locally and verified the improvement.

Changes:

• Performance Fix: Added a B Tree index on pred.predicted_generation_values(forecast_uuid) to optimize foreign key lookups during deletes.

• Migration: Created 00006_predictions_index_forecast_uuid.sql following the project's migration pattern.

• Verification: Confirmed the DELETE execution time dropped from ~170ms to ~30ms using EXPLAIN ANALYZE (shifted from a Sequential Scan to an Index Scan).

Branch Link: improve/delete-forecast-index-performance

I noticed I don't have permissions to open a PR directly here. Please let me know if you'd like me to open one (if access can be granted) or if you will prefer to pull these changes from my fork!

[ocf-bot]

@danishdynamic Thank you for reaching out! Unfortunately, this issue is not suitable for open source contribution and so hasn't been marked with "good first issue" or "contributions welcome". Please consult our contribution guidelines for more information on how you can contribute, and hope to see you around!

[devsjc]

After doing the reindexing the other day and the partition alignment, I'm a little suprised to see this. Is it still true?

[peterdudfield]

I think there were two issues,

1. I had to manually delete forecast_Values
2. It took a very long time, I could have another go, but it might be worth doing it with you, incase im doing something wrong

[devsjc]

I'll have a look!