Suppose I have defined a function that is expensive to compute. Is there a way to define an index in PostgreSQL to cache the return value for a given set of parameters?

For example, let's say that I have a function stats(year, month) to calculate some statistics for the given year and month. Is there a way to precompute the result for the years 2019, 2020, 2021 using an index or any other technique?

  • 1
    Why not just store the precomputed values in a table? You can index the table if you want, but you can't have an index without a table.
    – jjanes
    Mar 30, 2022 at 1:02
  • If both parameters are take from a table, and the function returns a single value and is (truly) immutable you can create an index on that: create index on the_table ((stats(year, month)))
    – user1822
    Mar 30, 2022 at 15:24


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.