Hi Tom,
Thank you very much for your explanation.
On 13.08.2007, at 23:01, Tom Lane wrote:
Philipp Specht <phlybye@xxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
