Re: Caching for stable expressions with constant arguments v6

On Mon, Jan 16, 2012 at 12:06 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> Here's v6 of my expression caching patch.

i little review...

first, i notice a change of behaviour... i'm not sure if i can say
this is good or not.

with this function:
"""
create or replace function cached_random() returns numeric as $$
begin
raise notice 'cached';
return random();
end;
$$ language plpgsql stable;
"""
if you execute: select *, cached_random() from (select
generate_series(1, 10) ) i;

on head you get 10 random numbers, with your patch you get 10 times
the same random number... wich means your patch make stable promise a
hard one.
personally i think that's good but i know there are people using,
mistakenly, volatile functions inside stable ones

---

seems you are moving code in simplify_function(), do you think is
useful to do that independently? at least if it provides some clarity
to the code

---

benchmark. i run a few tests in my laptop (which is not very performant but...)
from what i see there is no too much gain for the amount of complexity
added... i can see there should be cases which a lot more gain (for
example if you use a function to hide a select and you use such a
function several times in the select... but i guess it should work the
same with a CTE)

configuration:

name | setting
----------------------------------+--------------------
shared_buffers | 4096
synchronous_commit | off

filesystem: xfs

-- This is from the bench_cache.sh but with -T 150
select * from ts where ts between to_timestamp('2005-01-01',
'YYYY-MM-DD') and to_timestamp('2005-01-01', 'YYYY-MM-DD');
head 0.423855
cache 1.949242
select * from ts where ts>now();
head 2.420200
cache 2.580885

/*uncachable*/ select * from one where ts >=
to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') and ts <
(to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') + interval '1
year')
head 955.007129
cache 846.917163

/*cachable*/ select * from one where ts >= to_date(now()::date::text,
'YYYY-MM-DD') and ts < (to_date(now()::date::text, 'YYYY-MM-DD') +
interval '1 year')
head 827.484067
cache 801.743863

a benchmark with pgbench scale 1 (average of 3 runs, -T 300 clients
=1, except on second run)
-scale 1
== simple ==
head 261.833794
cache 250.22167
== simple (10 clients) ==
head 244.075592
cache 233.815389
== extended ==
head 194.676093
cache 202.778580
== prepared ==
head 300.460328
cache 302.061739
== select only ==
head 886.207252
cache 909.832986

a benchmark with pgbench scale 20 (average of 3 runs, -T 300 clients
=1, except on second run)
-scale 20
== simple ==
head 19.890278
cache 19.536342
== simple (10 clients) ==
head 40.864455
cache 44.457357
== extended ==
head 21.372751
cache 19.992955
== prepared ==
head 19.543434
cache 20.226981
== select only ==
head 31.780529
cache 36.410658

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación