Stable function optimisation - Mailing list pgsql-performance

From Philipp Specht
Subject Stable function optimisation
Date
Msg-id 2E281675-B282-41F6-B7A2-86853250850A@phlybye.de
Whole thread Raw
Responses Re: Stable function optimisation  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Stefan Kaltenbrunner
Date:
Subject: Re: [HACKERS] Proposal: Pluggable Optimizer Interface
Next
From: "Relyea, Mike"
Date:
Subject: Re: Help optimize view