| From: | Philipp Specht <phlybye(at)phlybye(dot)de> | 
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org | 
| Subject: | Stable function optimisation | 
| Date: | 2007-08-13 20:12:33 | 
| Message-ID: | 4CA024CC-513C-46E7-8724-30087C54745E@phlybye.de | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2007-08-13 20:17:23 | Re: Help optimize view | 
| Previous Message | Kevin Grittner | 2007-08-13 20:02:15 | Re: Help optimize view |