Thread: STABLE vs. IMMUTABLE w.r.t. indexes
It seems Postgres 9.3 does not realize that it can collapse the result of a STABLE function when attempting to match against an index for a single query.
I am running into a problem with a full text index, where my filter conditions include a function that returns the user's language code. If the function result were substituted directly, the filter condition would match an index built for that particular language, but the only way I can get the function to collapse down is to call it IMMUTABLE.
The function pulls a GUC value and that's all it does. Is it safe to mark it IMMUTABLE? I noticed that if I updated the GUC variable and ran the query again, it worked as I would hope, with the new value of the function substituted. So it seems it would be safe, but I'd like to verify.
I'd also like to know why it wouldn't work if the function was STABLE: Since Postgres should know that it's not going to change over the course of the query, couldn't it substitute the value as well?
More details can be provided on request.
Thanks.
Moshe Jacobson
Moshe Jacobson
Principal Architect, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
"Quality is not an act, it is a habit." -- Aristotle
Moshe Jacobson <moshe@neadwerx.com> writes:
> The function pulls a GUC value and that's all it does. Is it safe to mark
> it IMMUTABLE?
No; such a function is by definition mutable.
> I noticed that if I updated the GUC variable and ran the
> query again, it worked as I would hope, with the new value of the function
> substituted. So it seems it would be safe, but I'd like to verify.
You might chance to get away with that as long as you never ever use the
function in a view or prepared query (including inside a plpgsql
function). But it seems likely to bite you eventually.
> I'd also like to know why it wouldn't work if the function was STABLE:
> Since Postgres should know that it's not going to change over the course of
> the query, couldn't it substitute the value as well?
You have not shown us the context, but I suspect you are wishing that the
planner would assume that the function's result can't change between
planning and execution. Unfortunately, it can.
regards, tom lane
On Mon, Nov 3, 2014 at 3:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'd also like to know why it wouldn't work if the function was STABLE:
> Since Postgres should know that it's not going to change over the course of
> the query, couldn't it substitute the value as well?
You have not shown us the context, but I suspect you are wishing that the
planner would assume that the function's result can't change between
planning and execution. Unfortunately, it can.
OK. So then I'd like to inform the planner that I don't care if the GUC value changes after the database session has begun. I want it to get the value and substitute it out in the planning phase so that the planner can find and use the correct index.
Is there any way to do this without marking something immutable when it is not?
Thanks.
Moshe Jacobson
Moshe Jacobson
Principal Architect, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
"Quality is not an act, it is a habit." -- Aristotle