From: | Demitri Muna <postgresql(at)demitri(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Getting "could not read block" error when creating an index on a function. |
Date: | 2021-01-03 17:48:54 |
Message-ID: | ECDA9578-E43D-448D-96E6-F638EA5E3ECD@demitri.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2021-01-03 17:59:57 | Re: Possible trigger bug? function call argument literalised |
Previous Message | Thiemo Kellner | 2021-01-03 17:45:29 | Re: Possible trigger bug? function call argument literalised |