Re: [HACKERS] indexes and floats - Mailing list pgsql-hackers

David Gould wrote:
>
> >                ^^^^^^^^^
> > I don't like the idea to evaluate random() in WHERE x = random()
> > for _each_ tuple. The same for WHERE insert_date = now() - 5
> > /* 5 days */. Imho, these functions should be evaluated ONCE
> > by executor, but EACH time when executor starts. There is big
> > difference between evaluation in parser/planner and in executor
> > due to ability (currently, very limitted) to have prepared plans
> > (parsed/planned). And nothing more. Imho, queries must return
> > consistent set of data: if I want to get rows inserted 5 days
> > ago and run query with WHERE insert_date = now() - 5 near 12PM
> > then I risk to get inconsistent set of rows if now() will be
> > evaluated for EACH tuple.
>
> Perhaps random was a bad example, the point I was trying to make was that
> it is a function that is not wholely determined by its arguments.
>
> However, I disagree with your contention about random() and now() also.
> Think about the statement
>
> insert into samples
>   select random(), xval, yval from points;
>
> The intent being to associate a random value with each of the x,y pairs...

Imho, we have to treat random() etc differently in target list and
qual! WHERE defines set of rows to be returned by query, functions
in target list define representation of this set.

>
> > > Also, perhaps instead of doing constant folding in the parser, consider makeing
> > > it part of rewrite. This pass would just traverse the tree looking for
> > > functions with constant arguements and would pre-evaluate them and and
> > > save the result in the wherever the cacheable results would be stored. No
> > > special case required except that the optimizer would have to notice that
> > > a pre-computed result was available to use as a key value.
> >
> > This is bad for performance.
>
> What_ is the "This" that is bad for performance? It is not clear what you
> mean here.
>
> Do you mean memoizing? If so, I strongly disagree, and there is a ton of
> work on this that supports me.
>
> Or do you mean adding the cacheable function optimization to rewrite? If so,
                                                            ^^^^^^^^^^
This. Why should we traverse the tree once more time?
What the problem to let parser handle these functions?

> why is this a problem? I am assuming that this can be piggybacked on one
> of the existing expression tree traversals, so I just don't see how
> this creates more work than doing it in the parser.

Vadim