Re: [WIP] Caching constant stable expressions per execution

On Mon, Sep 12, 2011 at 00:22, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The other thing that is going to be an issue is that I'm fairly sure
> this breaks plpgsql's handling of simple expressions.

> The quick and dirty solution to this would be for plpgsql to pass some
> kind of planner flag that disables insertion of CacheExpr nodes, or
> alternatively have it not believe that CacheExpr nodes are safe to have
> in simple expressions.  But that gives up all the advantage of the
> concept for this use-case, which seems a bit disappointing.  Maybe we
> can think of a better answer.

I got around to this and I think there's a better way.

PL/pgSQL "simple expressions" are queries that return a single row, a
single column, don't reference any tables, have no WHERE clauses etc.

All expressions in such queries would be evaluated only once anyway,
so don't benefit from cache -- and indeed could potentially be hurt by
the added overheads.

It seems that we could pre-empt this in the planner by recognizing
potentially-simple queries right from the start and forbidding
CacheExpr. Maybe add a new boolean to PlannerInfo?

However, I got stuck because set-returning functions aren't
immediately obvious -- I'd have to walk the whole expression tree to
find out. A query like "SELECT now(), generate_series(1,10)" isn't a
simple expression and could still benefit from cache for the now()
call.

Or we could just let it slip and not cache anything if there's no
FROM/WHERE clause.

Thoughts?

Regards,
Marti