Clarification on Expression indexes
Greetings all.
The following page:
https://www.postgresql.org/docs/11/indexes-expressional.html
States the following:
Index expressions are relatively expensive to maintain, because the derived
expression(s) must be computed for each row upon insertion and whenever it
is updated
I'd like to get an idea on "relatively expensive". For instance, compared
to a partial index, which is split on one or more boolean values. As
opposed to making a function for this that serves the same identical
calculation.
Let's say that over the lifetime of a row, it rarely gets updated more than
2000 times, and the majority of this is right after creation?
Is this a concern?
Regards,
Koen De Groote
Koen De Groote <kdg.dev@gmail.com> writes:
Index expressions are relatively expensive to maintain, because the derived
expression(s) must be computed for each row upon insertion and whenever it
is updated
I'd like to get an idea on "relatively expensive".
It's basically whatever the cost of evaluating that expression is,
plus the normal costs of index insertion. If the expression is
something built-in like sin(x), probably the evaluation cost is
negligible ... but with a user-defined function in SQL or some PL,
maybe not so much.
In any case, the real question is "how many expression evaluations am I
going to save over the life of the row, versus how many I pay up-front?"
You didn't address how many queries would benefit from having the index,
so the question is unanswerable with just these facts.
regards, tom lane