Re: Stable function optimisation

Hi Tom,

Thank you very much for your explanation.

On 13.08.2007, at 23:01, Tom Lane wrote:

> Philipp Specht <phlybye(at)phlybye(dot)de> writes:
>> The biggest question here is: Why is the runtime of the query with
>> the stable function not near the runtime of the immutable function?
>
> Stable functions don't get folded to constants.

I tried to force this by using the following construct:

SELECT t.id, t.a FROM public.t WHERE t.a=(VALUES(public.f()));

Is this a bad practice and will destroy some other thing I can't
think of at the moment? What it means for me at the moment is about
half the query time of a high usage query directly linked to a gui.
That's a big gain for a user interface and takes the query under the
magical 500ms response time...

>> It's definitely one query and the manual states that a stable
>> function does not change in one statement and therefore can be
>> optimised.
>
> That's not the type of optimization that gets done with it. What
> "STABLE" is for is marking functions that are safe to use in index
> conditions. If you'd been using an indexable condition you'd have
> seen three different behaviors here.
>
> (I see that you do have an index on t.a, but apparently there are
> too many matching rows for the planner to think the index is worth
> using.)

Yes, that's not the real problem here. It's only a test database and
the real data behaves a bit differently.

Have a nice day,
Philipp