If Y was a constant, then it could be specified in the index definition. It cannot be read from a different table.

Have you looked into partitioning (is that what you mean by "child tables")? This would physically cluster the rows by category, and the vacuum would probably be more efficient because it would target only the tables that have changed. Multiple updates of different categories would be deconflicted also. I can't really comment further as I have no hands on experience of pg partitioning, but if it were Oracle then this would be almost a text book case.

Is there a pattern to the batches? For example, that each one is entirely for a particular "category" (which you mention in the question). If so, how many categories are there in total, and how many rows per category?

Maybe not a direct answer, but instead of storing AVG, consider storing both SUM(score) and COUNT(*), giving you total_score and score_count. Those are then aggregatable and an average can then be calculated from them with sum(total_score) / sum(score_count), so you have a more flexible solution that can provide averages at different granularities.

Are you deleting or truncating? And have you set a fillfactor of 100 on those indexes?

No Booleans in Oracle SQL, although PL/SQL supports it.

It's worth mentioning that both forms of cursor have inferior performance to a pure SQL solution -- particularly relevant to DML statements.

Implicit cursors fetch 100 rows at a time from 10g onwards.