Обсуждение: 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