On Mon, Jan 16, 2012 at 12:06 PM, Marti Raudsepp <ma...@juffo.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
