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
Principal Architect, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 courseof
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
Principal Architect, Nead Werx Inc. <http://www.neadwerx.com>
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle