Re: Caching for stable expressions with constant arguments v6

On Sat, Feb 4, 2012 at 09:49, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> i little review...

Thanks! By the way, you should update to the v7 patch.

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

> if you execute: select *, cached_random() from (select
> generate_series(1, 10) ) i;

Yeah, this is pretty much expected.

> 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

I think so, yeah, but separating it from the patch would be quite a bit of work.

>  shared_buffers             | 4096

Note that the "ts" table is 36MB so it doesn't fit in your
shared_buffers now. If you increase shared_buffers, you will see
better gains for tests #1 and #2

> 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

Yeah, the goal of this test script wasn't to demonstrate the best
cases of the patch, but to display how it behaves in different
scenarios. Here's what they do:

Test case #1 is a typical "real world" query that benefits from this
patch. With a higher shared_buffers you should see a 6-7x improvement
there, which I think is a compelling speedup for a whole class of
queries.

Test #2 doesn't benefit much from the patch since now() is already a
very fast function, but the point is that even saving the function
call overhead helps noticeably.

Tests #3 and #4 show the possible *worst* case of the patch -- it's
processing a complex expression, but there's just one row in the
table, so no opportunity for caching.

----
Besides stable functions, this patch also improves the performance of
expressions involving placeholders parameters, such as variable
references from PL/pgSQL, since these are not constant-folded. E.g:

DECLARE foo timestamptz = '2012-02-04';
BEGIN SELECT * FROM ts WHERE ts>(foo - interval '1 day');

Before this patch, the interval subtraction was executed once per row;
now it's once per execution.

> a benchmark with pgbench scale 20 (average of 3 runs, -T 300 clients
> =1, except on second run)
> -scale 20

I think the differences here are mostly noise because the queries
pgbench generates aren't affected by caching.

Regards,
Marti