Re: BUG #5816: index not used in function

Frank, thanks for educating me.

-- Korry

> -----Original Message-----
> From: Korry Douglas [mailto:korry(dot)douglas(at)enterprisedb(dot)com]
> Sent: Sunday, January 09, 2011 2:34 PM
> To: frank
> Cc: 'Kevin Grittner'; pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] BUG #5816: index not used in function
>
>
>
> > We may have different perceptions of something being a 'bug'. I always
>
> > have several simple ways of determining it. One of them is when a
>
> > work-around is in the proposal. Yours is one.
>
>
>
> It seems to me that the important question in this case is whether or not the query produced the correct result.
>
> The important question by nature is not whether correct result is produce eventually. In this case, the important thing is the inability to use the index when in fact, if designed and implemented correctly, it could.
>
>
>
> You are complaining about a performance issue, not a correctness issue, right?
>
> No. I am pointing out a low-level-design/implementation defect. Poor performance is the symptom. Poor performance due to sub-optimal execution of the spec is a correctness issue. (See my comment on the nature of a function below)
>
>
>
> Kevin's work-around is meant to help you *gain better performance*, not to obtain correct results when you are getting incorrect results.
>
> If it is not a bug, why do we need a work-around?
>
>
>
> > There can be quite a number of ways of looking at the issue. First, it
>
> > is truly an implementation matter (making it in the true sense a bug). I
>
> > do not believe that the spec would in formal way say that 'well, there
>
> > are caveats where you have to do this and that to work around'.
>
>
>
> The "spec" (by which I assume you mean the SQL standard) says nothing about which execution plan will be selected (by) the optimizer.
>
> No. Whatever the spec, it will never say that a function will not work as a function, or something that works outside one will not work once moved inside.
>
>
>
> > <snip>
>
> > If by 'kept from one execution to another' means that (the concept of) a
>
> > plan is implemented static, this can be a low level design issue, which
>
> > in general will still be regarded as implementation, thus a bug.
>
>
>
> The execution plan is not quite static - it is computed the first time you run the function (within a session) and is discarded when your session ends (or when the compiled function/execution plan becomes obsolete because of a change to a dependency).
>
>
>
> That is by design.
>
> Then the design is poor.
>
>
>
> If you want a dynamic plan that is re-computed each time you execute the query, you can get that behavior by using dynamic SQL, as Kevin suggested.
>
>
>
> This seems far fetched and irrelevant. Whatever is truly static should be implemented static; whatever is dynamic should be implemented dynamic; whatever is partially static, the static part should be static and the dynamic part should be dynamic. It is natural and correct treatment.
>
>
>
> Purely dynamic situation in which the final query can not be determined in any fashion, will have to be constructed either outside of the function or within, so EXECUTE is the only way to handle. What do you think of requiring the caller to construct such a static statement as “select count(*) from sometable” and use EXECUTE?
>
>
>
> By the same token, “select thiscolumn from thistable where upper(thiscolumn) like $1” has to be treated statically for the static part. The only unknown is the parameter, which can be, by the right design and implementation, delayed till execution (runtime). The code to deal with this is what I pointed out (via a conditional). If the plan is a piece of code, then the conditional will be in it. If the plan is a piece of text to be further interpreted for actual execution (why would one want to do it that way?), the conditional could contain a text reference to two pieces of code (w/o the use of the index). If it is not properly designed/implemented and such situation results in the loss of the said ability, it is a defect to be addressed. Whether one wants to address it is one issue. A defect is a defect.
>
>
>
> You seem to suggest that the plan was only built at (the first) execution. That is poor design/implementation.
>
>
>
> Lastly, what is a function? One of the fundamental features of a function is encapsulation. One is guaranteed some well-defined output based on well-defined input. No implementation detail is necessary or is obliged to be available. The user does not have to know what table or anything for that matter is involved. When you push for what is suggested as a work-around, it defeats one of the basic purposes for a function.
>
>
>
> More can be said, but why one wants to defend a defect is quite beyond me.
>
>
>
> -- Korry
>