Slow API calls with India dev

[katyalmohit]

While testing the new API http://india-development-quartz-api.ap-south-1.elasticbeanstalk.com, the route /forecast is taking too long to load the forecasts ~50seconds. But the earlier API takes about ~2 seconds.

Routes tested:

• /solar/ruvnl/forecast

• /wind/ruvnl/forecast

• For the UK one, it's quick ~ less than 1 sec

[katyalmohit]

This are the aurora SQL insights sql.txt

[PavanRaghavendraKulkarni]

It's also happening for the Sites endpoint. The new API is using the DP, which is significantly slower.

GET /sites/{uuid}/forecast (Forecast data)

Old API: 2.28 s
New API: 20.42 s

[peterdudfield]

This are the aurora SQL insights sql.txt

I tried this out on the database with

• 1='019d7732-dfcf-7580-92c0-467386100469'
• 2 =
• 3 = 38
• 4 '2026-07-24'
• 5 '2026-07-28'
• 6 0
• 7 '2026-07-28'
  and it took about 20 seconds

[peterdudfield]

The code is roughly divided into 3,

• 1 took about a second
• 1+2 took about 20 seconds
• 1+2+3 took about 20 seconds

[peterdudfield]

It looks like there is a slightly bug with the partitions as they are no longer being added to

Although I think that just slows down 1, but not 2

[peterdudfield]

Each partition table for prediced_generation_values has about ~1.5 million roles (< UK with ~56 million roles)

[peterdudfield]

I did some Vaccuum full on the predicted_generation_values and the query nows works in ~14 seconds

[peterdudfield]

The query runs every quick is you just run it on the one partition table needed

Also there seems to be a difference between the constraines (left is india, right is uk)

[peterdudfield]

This didnt seemed to help

ALTER TABLE pred.predicted_generation_values
VALIDATE CONSTRAINT predicted_generation_values_forecast_uuid_fkey;

[devsjc]

I have resolved the speed issue. The problem was as follows:

• Postgres uses table statistics to determine the right kind of joins. These statistics are generated when ANALYZE is run. The table grows quickly, so ANALYZE is run by pg_cron every hour. Or at least it should...
• pg_cron hadn't been running since April, so the statistics were 3 months old. This led to postgres trying to use the wrong kind of join, and causing the slow query times as it was no longer querying sympathetically to the indexes.
• This is because the database name, which was set manually in a parameter group, was returned to default on an RDS update (maybe?)
• Running ANALYZE on the predicted_generation_values and forecasts tables manually, sped the query back up to expected speeds

Here is a PR in ocf-infrastructure to resolve the parameter group issue: https://github.com/openclimatefix/ocf-infrastructure/pull/1049