Thread: Stable function optimisation

Hello!

Here's my test database:

# table
CREATE TABLE public.t
(
   id integer NOT NULL,
   a integer NOT NULL,
   CONSTRAINT pk_t PRIMARY KEY (id)
)
CREATE INDEX idx_t_a
   ON public.t
   USING btree
   (a);

# function
CREATE OR REPLACE FUNCTION public.f()
   RETURNS integer AS
$BODY$BEGIN
    RETURN 1;
END$BODY$
   LANGUAGE 'plpgsql' STABLE;

# view
CREATE OR REPLACE VIEW public.v AS
  SELECT t.id, t.a
    FROM public.t
   WHERE public.f() = t.a;

########

# f() is stable

test=# explain analyze select * from public.v;
                                                QUERY PLAN
------------------------------------------------------------------------
--------------------------------
  Seq Scan on t  (cost=0.00..1991.00 rows=51200 width=8) (actual
time=0.060..458.476 rows=50003 loops=1)
    Filter: (f() = a)
  Total runtime: 626.341 ms
(3 rows)

# changing f() to immutable

test=# explain analyze select * from public.v;
                                                QUERY PLAN
------------------------------------------------------------------------
--------------------------------
  Seq Scan on t  (cost=0.00..1741.00 rows=51200 width=8) (actual
time=0.165..199.215 rows=50003 loops=1)
    Filter: (1 = a)
  Total runtime: 360.819 ms
(3 rows)

# changing f() to volatile

test=# explain analyze select * from public.v;
                                                QUERY PLAN
------------------------------------------------------------------------
--------------------------------
  Seq Scan on t  (cost=0.00..1991.00 rows=50000 width=8) (actual
time=0.217..560.426 rows=50003 loops=1)
    Filter: (f() = a)
  Total runtime: 732.655 ms
(3 rows)

########

The biggest question here is: Why is the runtime of the query with
the stable function not near the runtime of the immutable function?
It's definitely one query and the manual states that a stable
function does not change in one statement and therefore can be
optimised.

Is this a pg problem or did I do something wrong?

Thank you for your help!

Philipp

Philipp Specht <phlybye@phlybye.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.

> 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.)

            regards, tom lane

Hi Tom,

Thank you very much for your explanation.

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

> Philipp Specht <phlybye@phlybye.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