Stable function optimisation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

               http://www.postgresql.org/about/donate

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux