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