Karsten Hilbert <Karsten.Hilbert@xxxxxxx> 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
