Thank you for the responses! I was going to go with a materialized view, but then realized that since the dataset is static it’s really no different from just creating a new table and indexing that. The suggestions provide useful advice for the future though.
Cheers, Demitri > On Dec 30, 2020, at 3:14 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Karsten Hilbert <karsten.hilb...@gmx.net> writes: >> Am Wed, Dec 30, 2020 at 02:37:59PM -0500 schrieb Demitri Muna: >>> I want to index the results of these repeated, unchanging calculations to >>> speed up other queries. Which mechanism would be best to do this? Create >>> additional columns? Create another table? > >> A materialized view ? > > Yeah, or you might be able to do something with a before-insert-or-update > trigger that computes whatever desired value you want and fills it into a > derived column. Indexing that column then gives the same results as > indexing the derived expression; but it sidesteps the semantic problems > because the time of computation of the expression is well-defined, even > if it's not immutable. > > You might try to avoid a handwritten trigger by defining a generated > column instead, but we insist that generation expressions be immutable > so it won't really work. (Of course, you could still lie about the > mutability of the expression, but I can't recommend that. Per Henry > Spencer's well-known dictum, "If you lie to the compiler, it will get its > revenge". He was speaking of C compilers, I suppose, but the principle > applies to database query optimizers too.) > > regards, tom lane
