Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire table at once could be quite costly.

And just as importantly, can I trigger future INSERT|UPDATE|DELETEs to update the sum without re-adding every term? (For example, an INSERT should add the relevant column's value from the new row to the existing sum.)

Will materialized views help with this goal? This SO question seems to suggest that they will, but I'm not sure how.

  • You cannot create an index like that. However, you can keep a separate table for the aggregate which is then updated on data changes. One can implement such a system using triggers, for example. You can use a materialized view, too, but you have to check the performance hit the refresh takes. – dezso Feb 2 '15 at 10:52
  • The link is to dba.SE, not SO, and the linked question is for SQL Server, not Postgres. Here is a related answer that's actually for Postgres (and actually on SO): stackoverflow.com/questions/27629134/… – Erwin Brandstetter Feb 4 '15 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.