Index conditional aggregate query on PostgreSQL How does one optimize a conditional aggregate query on PostgreSQL?

A example of such query would be:

SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL';


Indexing (PlayerId, status) would help the filter, any tricks or ideas to optimize the aggregation post filtration?

What would be ideal would be no external caching mechanism involved, and a save mechanism to keep a running SUM every time "games" table is updated – similar to how a cache works but a more reliable one.
You'll have to create a special table to contain the sum.

Then create triggers on the original table that add values that are inserted, subtract values that are deleted, do both for an update and set the value to zero on truncate.

You can see a small example of that for the count aggregate in my blog post. Different from that example you will have to have a summation table with one sum per "PlayerId" and status.