Stable function optimisation
От | Philipp Specht |
---|---|
Тема | Stable function optimisation |
Дата | |
Msg-id | 2E281675-B282-41F6-B7A2-86853250850A@phlybye.de обсуждение исходный текст |
Ответы |
Re: Stable function optimisation
|
Список | 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 по дате отправления: