Tom Lane wrote:
>
> Bernard Frankpitt <frankpit@pop.dn.net> writes:
> > The solution that I propose is to include code in the optimizer that
> > picks functions with constant arguments out of a qualification
> > clause, and evaluates them.
>
> This is something I had on my own to-do list, and I'm glad to see
> someone beat me to it. But you've only done half the job: you
> should also be folding operators with constant arguments.
>
> Also, you need to be wary of functions like now() and random().
> There probably isn't any other way to handle these than to add a
> column to pg_proc flagging functions that can't be constant-folded.
>
I actually do the operators as well, and also boolean operators (which
are handled by special Expr nodes).
I puzzled over case of now() for a while but I don't think that it
raises a problem.
For queries like
SELECT * FROM t WHERE t.a < now();
Early evaluation seems quite reasonable. Now means a fixed time close to
the time the backend received the query. It seems to me that all the
now() calls in a query should return values pretty close to each other.
A query like
SELECT * FROM t1 t2 WHERE t1.a < now() AND t2.a < now();
will have two values of now that are very close, since the evaluations
both happen in the planner. People who expect the two now() calls to
give exactly the same value in this case are expecting too much, queries
like this should be rewritten
SELECT * FROM t1 t2 WHERE t1.a < now() AND t1.a = t2.a;
In fact istm that the correct way to handle now() would be to have a
value that is constant over a transation, and comensurate with the
numbering of tids.
I don't think that random() is a problem at all. It gets called once
each time it is written in the query string. That is certainly a
reasonable interpretation of its meaning.
> > is, I chose to put it in
> > plan/initsplan.c:add_restrict_and_join_to_rels().
>
> I believe it would be best to do it considerably earlier, specifically,
> before cnfify(). It might even be worth running the code twice,
> once before and once after cnfify.
>
> Also, probably we should apply it to the targetlist as well as the qual.
>
Yes, close to cnfify might be a better place. I only did it for the
quals because I don't think I understand the other parts of the plan
trees well enough. The function is quite easy to use though, it acts as
a filter on connected subtrees that consist of List nodes and all Expr
nodes other than
SUBPLAN_EXPR nodes. Because of the recursive way that qual plans are
built, subplans are still optimized.
Another factor about positioning of the filter that I was uncertain
about was time expense. Is the time taken by multiple tree walks in the
planner
very significant in the overall scheme of things?
Bernie
