On Mon, Sep 12, 2011 at 00:22, Tom Lane <tgl@sss.pgh.pa.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